Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, 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.

The steps

1. Open the Northwind database.
2. Open the query named Products by Category in design view, and add Categories.CategoryID to the grid. Save, and close.
3. Create a new form, not bound to any table or query.
4. Add a list box from the Toolbox. (View menu if you see no toolbox.)
5. Set these properties for the list box:

   	Name	lstCategory
   	Multi Select	Simple
   	Row Source Type	Table/Query
   	Row Source	SELECT Categories.CategoryID, Categories.CategoryName FROM Categories ORDER BY Categories.CategoryName;
   	Column Count	2
   	Column Widths	0

6. Add a command button, with these properties:

   	Name	cmdPreview
   	Caption	Preview
   	On Click	[Event Procedure]

7. Click the Build button (...) beside the On Click property. Access opens the code window.
8. Paste the code below into the event procedure.
9. 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:
       =[Report].[OpenArgs]
   The code builds a description of the filter, and passes it with OpenArgs. See note 4 for earlier versions.

The code

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "Products by Category"

    'Loop through the ItemsSelected in the list box.    
    With Me.lstCategory
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    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)
        End If
    End If
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.    
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

Notes

1. 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 = """"
2. 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.
3. 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.)
4. 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.
5. If you need to combine the results with other optional criteria, see the Search Criteria database.