Query Parameter Form in Access

Pass Parameters to Access Query

Passing parameters to queries and reports can be a stumbling block for the novice Microsoft Access programmers.  In this example we will demonstrate the use of an extended select list box as a method to pass parameters to an Access query.  The query can then be used directly or used as a data source for a report.

There are 4 Access objects used for the multiselect parameter form demonstration:

M_Customers is a table storing our customer data
T_States is  the temporary table holding the list of selected parameters
F_Parameter_Form is the form containing the multiselect list box
Q_Customers is the query that produces the desired list of results

Here is a little more info about each of the objects:

M_Customers table (source for multiselect list box):CLICK FOR

An alternative to the query design above is to include the T_States table in the query and join on the State fields from both tables.  This method will be faster.  However, if you want to include an additional function you’ll  need to build the query as above.One example of an additional function is to check if NO states have been selected and then allow all states to pass through if that is the case.  For this additional feature you can use a sub query or you can use the following simple (but slow) method:

in (select state from t_states) or dlookup(“Count(state)”,”T_States”)=0

Now let’s talk about the setup of the parameter form.  About the only special setting for the forms is the list box property ‘multiselect’.  Set the multiselect list box property to ‘Extended’.  This setting will allow the user to select multiple states by using the shift and ctrl keys just like in a spreadsheet. See Extended property selection below.

Here is the row source for the list box:
SELECT DISTINCT M_Customers.State
FROM M_Customers
ORDER BY M_Customers.State;

Note the Distinct clause.  The distinct clause prevents duplicates states from being listed in the multiselect list box.

Ok, let’s run this query parameter form example:

In the parameter form above we have selected 3 states using the ctrl key and clicking on AZ, CO, and DC.  There is no coding involved in this part of the multiple selections example.

When we press the run query button in the lower left of the form the following code gets executed:

Private Sub Run_Query_btn_Click()
Dim x As Long
‘
‘ clear out old selected states list
‘
DoCmd.RunSQL (“Delete * from t_states”)
‘

For x = 0 To Me.State_List.ListCount – 1
If Me.State_List.Selected(x) = True Then
DoCmd.RunSQL (“Insert into t_states (state) values (‘” & _
Me.State_List.ItemData(x) & “‘)”)
End If
Next x
‘
‘ open parameter query
‘
DoCmd.OpenQuery “Q_customers”
End Sub

In the code above we first clear out any old entries in the T_States table.  Then we loop through the list box elements and determine if any have been selected.  If one is selected the we use the insert into query to add the selected state to our T_States table.

Last is the opening of the Q_Customers query.  See below the SQL code of the query and how it uses the T_States table to restrict the list of customers returned to the screen based on the values in the temporary table:

SELECT M_Customers.Customer_Name, M_Customers.State
FROM M_Customers
WHERE (((M_Customers.State) In (select state from t_states)))
ORDER BY M_Customers.Customer_Name;

Here are the results of our multiselect list box  parameter query:

You can use the multiselect listbox method to filter a report directly.  The report filter would like this: “state in (select state from t_states)”.  This method would be slower than filtering in the query itself.  Remember to create an index on the State field in both the M_Customers and the T_States table in order to make the query faster.

Still confused?  Download a this Access database example:Multi-Select Access Parameter Query.

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016