How to make a button show the result of a query in a subform?

How to make a query be displayed in a subform (in datasheet mode) with the criteria inserted in a text box (on the main form), after pressing a command button to run it?

Post your answer or comment

comments powered by Disqus

How to create a Report from the result of a Filter?

Oh it has length limitation for the title, what I actually want to ask is,

How to make a form show a partiular record when open it? But I can still navigate to other records?
Just like a book, I can open it to Page 3 but I can still go to Page 4 later.

Here is my code:

Private Sub EditOrder_Click()
On Error GoTo Err_EditOrder_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Edit Order"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
DoCmd.Close acForm, "Orders"
Exit Sub
MsgBox Err.Description
Resume Exit_EditOrder_Click
End Sub It's a button on "Orders" form, when I click on it ,it will open "Edit Order" with the same record shown on "Orders" form.

but "Edit Order" form now has a (Filtered) on the bottom near Navigation Buttons, which makes it will only shows that single record and I cannot navigate to others.

Does anyone would like to tell me how to make navigation button works without impact on any other funtions? (eg. I don't want to refresh the form if it makes the form jumps back to the 1st record, I just want to navigate it from X's record to X+1 or X-1's record)

Sorry for my poor English, and thanks very much.

Hi all

Do you know how to make a Slide Show for each form in Access??


P.S. any function is like VB can set the timer?

Please E-mail to me...THKS

how to make a command button in a form visible automatically when the form is opened if he is in admin mode ?

Is there any way to to create a variable with the name of another variables content in VBA?

PHP has something like what I now need in VBA, I believe it is something like this:

// Put text string in a variable

// Now create a variable named as the content of the above variable and put content in the new variable

// Print the content of $Apple which should now contan 5
echo $Apple;

Of course to be of real value the newly created variable should be available globally, another thing I don't know if MS Accces can do, unless defined in a module.

Alternatively I would need something like being able to define an array of names and then assign a number to each name, like this:

Dim Fruit
Fruit = Array("Apples", "Bananas", "Oranges")

and then later do this:



I have build a simple 3 table database at my work for us to replace our ancient logbooks. We are using it and it works pretty good, that is my colleagues and I are happy with the end result.

but I am by no means an expert in this field and we have a small problem: when the form for logging is initially started it shows the first record in the table and my colleague overwrote that accidentally.

my question:

How to make a form default to new record rather then showing the first record from the table each time the form is initially opened?

in my search for the answer here I found a new term (for me) but its name already makes me think of a modification for my purpose: "switchboard"

I just made another form with buttons to start a form to fill in the appropriate table, that I would almost want call a switchboard

so perhaps my second question would be:

what is a switchboard?

Thanks in advance

Dear All,
Good Day

Can anybody tell me “How to make a sub-form based on disconnected recordset and update/modify data?”

I have written the following code to get data on main form with the disconnected recordset:

Option Compare Database
Option Explicit
Dim rsContracts As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err

'call the function to open the database
Call OpenDB

'create new instance of a recordset
Set rsContracts = New ADODB.Recordset

'set various properties of the recordset
With rsContracts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblContacts table using the existing connection
.Open "SELECT * FROM tblContracts", con
'disconnect the recordset
.ActiveConnection = Nothing
End With

'if recordset is empty
If rsContracts.BOF And rsContracts.EOF Then
Exit Sub
'move to the first record
'populate the controls on form
Call PopulateControlsOnForm

If Not rsContracts.BOF And Not rsContracts.EOF Then
ElseIf rsContracts.BOF Then
'past begining of recordset so move to next record
ElseIf rsContracts.BOF Then
'past end of recordset so move to next record
End If

End If

'close the connection and release it from memory
Set con = Nothing

Exit Sub
MsgBox Err.Description
Resume Err_Exit
End Sub

This works fine and I have all the functionality for delete/update/movenext/moveprevious etc. which I have coded for each button on the form and call the procedures

My Problem is:

I have another subform on the main form which has related disconnected records. I have Set Me.Recordset = rsItems (disconnected recordset) and link child fields and link master fields properties set to my primary and foreign keys on it, but it don’t give me the desire results.

How can I filter my disconnected records on my sub-form and update back them to the database with adLockBatchOptimistic cursor type property opening the connection.

Best regards


I have a table "Purchases" In this table field name "purattach" which stores the scanned images.

I am trying to make a querry which can show me only the transaction which has the images stored in Purchases table.

Can someone guide me how to make such a querry.

Dear all,

How to make a notifications when going to the last record or back to the first record

Previous Button is ok

Code: Private Sub gotoPrevious_Click() On Error GoTo Err_gotoPrevious_Click With Me.lstItems .SetFocus If .ListIndex < .ListCount Then .ListIndex = .ListIndex - 1 MsgBox "First Contact", , "First Contact" End If End With Exit_gotoPrevious_Click: Exit Sub Err_gotoPrevious_Click: 'MsgBox Err.Description 'MsgBox "End the Record" Resume Exit_gotoPrevious_Click End Sub Next button not ok
Code: Private Sub gotoNext_Click() On Error GoTo Err_gotoNext_Click With Me.lstItems .SetFocus If .ListIndex < .ListCount Then .ListIndex = .ListIndex + 1 'MsgBox "End the Contact", , "End the Contact" End If End With Exit_gotoNext_Click: Exit Sub Err_gotoNext_Click: MsgBox Err.Description Resume Exit_gotoNext_Click End Sub


I have a data entry form with several fields that have information typed in. There is also one text field that would normally have information pasted in from the clipboard. However, my users cannot manage to highlight the correct field when pasting, so I get all sorts of rubbish appearing in the records.

I want to put a button on the form that will paste the clipboard to the correct field, regardless of where the current focus is. I can then disable the paste button on the toolbar.

I know how to set the focus with the button, but how do I get it to Paste?

Thanks in advance


I think I must be brain dead today. I have, I think, a simple problem. I have a form with two text boxes txtNumber and txtAnswer. I want to show the result of a calculation in txtAnswer using txtNumber and fldResult. The problem is fld Result is in another table. I can of course use a query to add the two together, using a calculated control, but how can I show the result of the query in the text box txtAnswer?

Hi, I want to know how to create a new table from 4 fields entered in a form. The reason I'm doing this, because I'm creating a report that goes through numerous macros to come up with the results. And in the beginning I'm creating a table that is use in all my queries. But not knowing how to do any Visual Basic or not knowing a lot about Access, need some help completing this.

My 4 fields are description, route #, blk length and waste factor.

In the form I have the end user enter the description, which is unique, then the last 3 fields populate on the form. I want to have a command button that creates a table for these 4 fields. Then all my queries will run in the macro I've already created, giving me my final report.


Combo boxes A and B are looking up texts from a table which has three fields: field 1: Personnel Names (PN), Field 2: Personnel Dimension (PD) such as Height and weight, and field 3: Related Data (RD). So we have a unique triplet combination. For example: (John, weight, 88), or (John, height, 180). I know how to make the comboboxes A and B to select texts based on PN and PD. But could you let me know how to make a textbox to show automatically the unique third data? I hope I could convey my meaning clearly.

Thank you

While there are lots of detailed issues discussed and resolved here, my problem is at a much higher level. Here's what I want to do.

I want to generate records in a file based on the user entering some 9 different values. The first two values will be used to get records from file A (they are a partial key). The values from the records in file A, plus the other values entered by the user will be used to generate records in file B. I hope this is clear enough.

I have created a query which prompts the user for each field and does the job just fine, but I want to replace the 9 individual prompts with a single form which allows the use of combo boxes for the partial key and a date picker for the single date field.

I've fluffed through creating a form with unbound controls to suit and even worked out a form of cascading combo box for the partial key, but I am struggling to find the best way to make a button at the bottom generate those records.

A nice optional would be to then display the added records (i.e. another query I guess).

Can somebody give me some pointers as to how to approach this? I've done very little in the way of Access programming, being mostly an SQL guy.


I have several questions that all resolve around the above issue. I have a database that queries a SQL database, an IBM AS400 database, and an Access database. I have a form with four drop-down boxes, all pointing to the same table (tbl_AllSchools) and the same field (SchoolName). The table contains the names of all the schools in our district. Techs will submit repair tickets through this database, and I'd like for them to be able to select the schools they take care of (each tech has 1-4 schools) to limit the tickets they see to the ones at their schools.

PROBLEM 1: I want to make a button under the first drop-down that will make a table (tbl_MySchools) with one field (SchoolName).

PROBLEM 2: I want to make a button under the subsequent drop-downs that will append the SchoolName to tbl_MySchools.

PROBLEM 3: I want to be able to reset the tbl_MySchools to all schools by re-making the table called tbl_MySchools that populates with the info from tbl_All Schools in it's entirety.

The queries that populate the repair ticket forms and reports do so with queries containing the tbl_MySchools table. I'm thinking I probably need to use a module, but I'm uncertain how to do that, and to call it in the vba code window for that button. Thanks.

Hello experts.

I have two forms: Form1: tabular form showing the latest order date for every customer. Form2: A form showing the history of this customer's interactions with my CSreps.

I want to create a button on form 1 that opens form 2 AND shows the same customer´s details. This button should show on every row so it opens form2 on the record that shows the customer name from this row.

How do I do this?

I have tried to create a button on design view that opens form2 and then tried linking the button to form1 via "Customer name". It doesnt work. All it does is open form2 on the same record every time, no matter which button from which row do I press on the tabular form (form1)


Hi - I'm new here, and very new to Access. I'm pretty experienced with Excel and VBA, but have come across a problem at work that I know really needs to be solved using Access - so time for me to learn! I'm Googling for as much as I can, but I'm going to get stuck quite a few times with probably simple things! Thanks for your patience ...

(Using Windows 7, Access 2010 btw)


Say I have a table called Locations that lists Countries and Cities:

USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans

I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).


Q2 to follow later

Hi All;

How to make a query that returns different criteria.

My table “tblRelease”
Account - text data
OpeningBalance- Currency
Type - Combo box – Value list = credit / debit
Status - Combo box – Value list = Pg / Pg no

I would get the momentary balance, ie credits and debits paid and unpaid balance added to the initial and the actual balance, ie credits and debits paid added to the initial balance.

Hi all,

I must limit the possibility of viewing the records by day and by person. Every record has a person that entered the record and date. I want to make my button (buttons for record navigation) limit by person and by date so you can only view that current date and if you entered it today. (by date).



I've got a database with a report attached to it with a form that updates the database. Is there a way that when I create/delete a new entry using the form, it will automatically/on a button update the report?


How to append more lines at the end of a memo field?
I have table field called NOTES which I use to write various notes, sometimes the same note is stretched across several records & I would like to be able to append it to the records using an update query. However, if any of the affected records already contain pre-existing notes then I’d like to be sure that the new note I append to the field adds an ENTER character before appending the new note, how do I do this though? I tried writing something like this in the UPDATE TO row of the query, but it doesn’t work.

VBCRLF & “This is the new note appended to the end of the note field”

Please advise.

I need to make a form into a user interface and I have no clue how to do that. I dont really know very much about coding or anything like that does anyone know of any place with examples on how to make a from into a user interface that will link into reports and forms in my database. If anyone has any suggestions on how to do this I would greatly appreciate it.

How to Open a Webpage automatically without using WebBrowser Control in Access? I want to open a webpage in the original browser automatically. The problem i have is the automation- if i didn't need it, i would just create a command button or such to manually open the webpage in Internet Explorer. Using the WebBrowser control does allow automation, but i require the full functional browser (and recreating IE using the the WebBrowser control would be a feat!) What is the How-to?

Thank you!


I'm offering this is as a tip as I've recently found it useful in reducing the number of queries in an application - apologies if it's old hat. I have three tables with nothing in common but a date field. I want to collect a number and date from each table and then do things with those numbers (group by month, divide, multiply, total, cross tab etc.). Suppose table1 has fields Date, Quantity, table2 has fields Date, Price and table3 has fields Date, NumberOfWheels. I can make a union as follows:

SELECT Date, Quantity, 0 AS Price, 0 AS NumberOfWheels
FROM table1
SELECT Date, 0 AS Quantity, Price, 0 AS NumberOfWheels
FROM table2
SELECT Date, 0 AS Quantity, 0 AS Price, NumberOfWheels
FROM table3;

If you have a WHERE clause it must be appended to all three FROM statements, like this:

SELECT Date, Quantity, 0 AS Price, 0 AS NumberOfWheels
FROM table1 WHERE Year(Date)=2012
SELECT Date, 0 AS Quantity, Price, 0 AS NumberOfWheels
FROM table2 WHERE Year(Date)=2012
SELECT Date, 0 AS Quantity, 0 AS Price, NumberOfWheels
FROM table3 WHERE Year(Date)=2012;

Not finding an answer? Try a Google search.