If you’ve been working with Microsoft Access for a little while, you’ve probably used parameter queries in some way. Below is a basic example of a query based on a Members table in which the query’s criteria uses two parameters shown in brackets to specify the start and end dates to be applied to the date_added field.

Whenever the user runs the query, they will be prompted with an input box for each parameter specified in the query. In this case, they will enter a start date and an end date and the query will use those dates to filter the results.

This is fine and it works great so long as your users are manually running queries. There are times, however, when you might want to call this query from VBA without having the input boxes popping up. In that case, you need to find another way to supply the information needed by the parameters. You can do this by defining formal parameters in the query settings.

By right-clicking in that empty space at the top of the query window, you can select the Parameters option from the context menu that appears.

In this case, I’ve added two parameters matching the ones shown in the query’s criteria and assigned them both a data type of Date with Time. This provides a formal definition that outside code can use to specify the correct values for the two parameters. This is also sometimes done to resolve errors that pop up when running reports or charts with unusual filter specifications.

The next step is to call this query from VBA and use it to display results on a form. I’ve created a basic form that enables the user to specify the start and end dates and shows the results in a subform. Initially, the form pulls all records directly from the Members table so that the query doesn’t show the input prompts but I want the form to replace the table’s results with those from the query.

The click event for the Open button shown on the form calls the following code:

Private Sub cmdOpen_Click()

'Declare objects
Dim dbs As DAO.Database
Dim qdfMembers As DAO.QueryDef
Dim rsMembers As DAO.Recordset

'Use current DB and MembersByDate query.
Set dbs = CurrentDb
Set qdfMembers = dbs.QueryDefs("MembersByDate")

'Set parameters
qdfMembers.Parameters("Start Date").Value = Me.txtStart
qdfMembers.Parameters("End Date").Value = Me.txtEnd

'Open recordset from query and assign to form.
Set rsMembers = qdfMembers.OpenRecordset
Set Me.Member_List.Form.Recordset = rsMembers

'Cleanup
Set qdfMembers = Nothing
Set dbs = Nothing

End Sub

The code uses DAO (Data Access Objects) to reference the database objects and their properties. In this case, we need to reference the database itself, a query and a recordset that will hold the query results. The database is set to the current database (CurrentDb) and then the QueryDef is set to the MembersByDate query which exists as a query definition with the database.

QueryDef objects contain whatever parameters have been defined in them and those parameters can be referred to as you see above, in quotes without the brackets. The parameter values are set to the matching fields from the form.

The QueryDef object also has an OpenRecordset method which will return whatever records the query finds in the form of a recordset which can then be assigned to the Recordset property of a form as shown.

When assigning objects in code, always remember to cleanup at the end and set them to nothing when they’re no longer being used. In this example, the data will continue to display on the form even though the VBA objects are disposed of.

This is also possible with action queries such as INSERT and UPDATE except that the QueryDef’s Execute method would be used to carry out the action after setting the parameters.

All sample data is fictitious and was obtained from Mockaroo.com.

Comeau Software Solutions can help you with your Microsoft Access project or find alternative solutions for your database needs. If you need assistance and would like more information, please contact us for details.