Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
Access Query Criteria







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.



Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.

Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.


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

Need help passing a parameter in Access? Give us a call and we will solve the problem or there is no charge! $100 1/2 hour.
410-708-1417

Access Parameter Query

T_States temp table:
query parameters multiple selections

F_Parameter_Form
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.

A Blue Claw Database Design Template:

Subscription Order Control Database
 
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:

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.

 









A Blue Claw Software Design Template:

HOA Software Template
 








A Blue Claw Database Design Article:

Microsoft Access On The Internet
 









Microsoft Office Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013




Contact Information

Copyright 2000-2017 Blue Claw Database Design