Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
 Access Form Design Tutorials
 
Access 2007 Form Tutorial Downloads
Form Design Tutorials

Create Forms in Microsoft Access
User Friendly, Fast & Simple Access Forms

Microsoft Access Tutorials>Access Form Tutorial>multiselect Parameter Form



Form Subtotal Fields
Master/Detail Forms
NotInList Event Tutorial
Speed Up Forms
Unbound Form Tutorial

Multiselect Parameter Forms

Conditional Formatting Downloadable Example


Multiselect Parameter Form in Microsoft Access

How to Pass Multiple Selections as Parameters To Access Query

How to pass parameters to queries and reports is often a stumbling block for the novice Microsoft Access programmer.  In this example we will demonstrate the use of an extend 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):
multiselect parameter forms

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

Extended Select List Box Query Parameters

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 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: Multiselect Parameter Query.

 



How much is your time worth?

Are you spending hours trying to get a form working they way you want?

Stop wasting time!    We are form programming experts!. 

$75 for 1/2hr of form programming help.

Contact Information

Contact Information

Microsoft Access Developer
Microsoft Access 2007 2003 2000 All Windows Versions