Do you need to use a dialog box. How about creating a form that holds a multiselect list box to pass the selections into your query's criteria argument?
You need to create the list box on a form. Then create a button on the form that runs your query. In the relevant field in your query you need to enter the name of the control that you have created on the form to hold your list box.
Using only singleselect listbox:
So if your form is called frmMyForm and the list box is list0, then as a criterion in your query under [Employee ID] you enter
[forms].[frmMyForm].[list0]
This will then take the value selected in the list box and use it as the criterion in the query.
Using multiselect listbox:
Using a singleselect listbox you do not need vba code to pass the criteria to the field criteria argument in a query, however, using a multiselect listbox you need to pass the multiple criteria to the field criteria argument in your query in the form of an "IN" argument using vba as follows.
Adjust the below code to suit your object references and place this code in the "on click" event of a commandbutton on your form.
Code:
Dim varItem as variant
Dim mycriteria as String
With Me.list0 'this is your listbox on your form
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
mycriteria = mycriteria & "'" & .ItemData(varItem) & "'" & ","
End If
Next
End With
lngLen = Len(mycriteria) - 1
' The SQL statement will be passed to the query using vba as follows
mystring1 = "(([Table1].[Field1]) IN (" & Left$(mycriteria, lngLen) & "))" ' This represents your IN argument in the Queries SQL statement holds the multiple criteria selected in the listbox
myQuery = "[Query1]" 'name of query to run and pass the SQL statement to
Dim strSQL As String
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(myQuery)
st5 = "SELECT DISTINCT [Table1].field1, [Table1].field2 " ' adjust the object references and SELECT statement to match your objects and desired Query type
st6 = "FROM [Table1]" ' adjust the object reference here
st7 = " WHERE " & mystring1 & " ;"
strSQL = st5 & st6 & st7
qdf.SQL = strSQL
Set qdf = Nothing
Just a quick sample of code needs some reference adjustments if you think this is helpful reply with your object names (Table name, Query name, Form name, Listbox name) and someone can help you adjust the code for proper references.
