Forms: Use Multi-Select List boxes as query parameters

(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.)

For example,

'******************** Code Start ************************
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]="
    'Assuming long [EmpID] is the bound field in lb
    'enumerate selected items and
    'concatenate to strSQL
    For Each varItem In ctl.ItemsSelected
        strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
    Next varItem

    'Trim the end of strSQL
    strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************