<%dim crumb,crumb1 crumb="Query Parameter Form" crumb1="Access Query Criteria" %> Pass Parameters to Query in Access
Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
<% response.write crumb1 %>

Home > Microsoft Access Tutorials > Access Forms Tutorial <% response.write ">" & crumb %>
<% Gnum="9096031968" Ynum="7841" dim Page_Type Page_Type="Prog" response.write "" %>

Form Subtotal Fields
Master/Detail Forms
NotInList Event
Speed Up Forms
Unbound Form
Change Textbox Height
Get Website Data
Access Audit Trail

Form Query Parameter

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 Free Discount Coupons

Access Parameter Query

T_States temp table:
query parameters multiple selections

multiselect list box query parameter

Q_Customers query:

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:
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:

Query Parameter Form

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:

Extended Selection List Box Parameters

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 Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>