A Blue Claw Database Design Template:
Student Grant Program Administration Software
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
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) & "')")
' open parameter query
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
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: Multiselect Access Parameter Query.