BaldyWeb

Use a Multi-Select Listbox to Filter a Report

There are a number of ways to do this.  The one I usually use is to have the report based on a query that returns all records, and use the wherecondition argument of OpenReport to restrict the records.  It is a little trickier with a multiselect listbox, but the code would look like this:

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
  MsgBox "Must select at least 1 employee"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'," 
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"

Multiselect sample db

Home