MS Office Help

(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 ************************