Provided by Allen Browne, September 2004
Use a multi-select list box to filter a report
This article explains how to use a multi-select list box to select several items at once, and open a report limited to those items.
With a normal list box or text box, you can limit your report merely by placing a reference to the control in the Criteria row of its query, e.g. [Forms].[MyForm].[MyControl]. You cannot do that with a multi-select list box. Instead, loop through the ItemsSelected collection of the list box, generating a string to use with the IN operator in the WHERE clause of your SQL statement.
This example uses the Products by Category report in the Northwind sample database.
- Open the Northwind database.
- Open the query named Products by Category in design view, and add Categories.CategoryID to the grid. Save, and close.
- Create a new form, not bound to any table or query.
- Add a list box from the Toolbox. (View menu if you see no toolbox.)
- Set these properties for the list box:
||Row Source Type
||SELECT Categories.CategoryID, Categories.CategoryName
FROM Categories ORDER BY Categories.CategoryName;
- Add a command button, with these properties:
- Click the Build button (...) beside the On Click property. Access opens the code window.
- Paste the code below into the event procedure.
- Access 2002 and later only: Open the Products by Category report in design view. Add a text box to the Report Header section, and set its Control Source property to:
The code builds a description of the filter, and passes it with OpenArgs. See note 4 for earlier versions.
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
strDoc = "Products by Category"
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
- In the example above, ProductID is a field of type Number. If your field is a Text type, remove the single-quote from the beginning of this line, so the quote delimiters are added to each item in the IN clause:
strDelim = """"
- In this example, the list box contains two columns: CategoryID (zero-width), and CategoryName (displayed). The filter string is built with CategoryID, and the description string is built with the CategoryName. The Column() property is zero-based, i.e. the first column is 0, and next is 1, and so on.
- For Access 97 and earlier, omit the If block to see if the report is already loaded. Earlier versions do not have the AllReports collection. (Instead, use the IsLoaded() function from Northwind's Utility Functions module.)
- For Access 2000 and earlier, drop the OpenArgs:=strDescrip from the DoCmd.OpenReport line. In these versions, you can pass the description in a public string variable, and then use the Format event of the Report Header section to read the string and assign the value to an unbound text box.
- If you need to combine the results with other optional criteria, see the Search Criteria database.