(Q) I have a MultiSelect listbox control on my form. I want to pass the selected items
to a query as a parameter. How do I do this?
(A) Unlike simple listbox controls which can be referenced as a parameter by a query,
MultiSelect listboxes cannot be used directly as a parameter. This is because calling the
listbox (Forms!frmMyForm!lbMultiSelListBox) from anywhere will not automatically
concatenate all the selected items. You need to build the criteria yourself.
Note: You can still use a parameterized query provided you pass the entire Where clause
to it via code as a parameter. (eg. Have the query reference a hidden control to which you
manually assign the complete WHERE clause using the following logic.)
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="