Multiple Multi-Select Boxes to Filter Report

[turk1559]

Hi y'all, I'm using this form to filter a report, but am running into a few problems. The first problem is that when I select a value/values from the list box and hit the command button, it asks me to fill in a parameter. It's that parameter that is used for sorting. I also want to adapt this to be used with multiple multi-select boxes. Any ideas?

Private Sub CommandButton_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 = "Incident Report"


    'Loop through the ItemsSelected in the list box.
    With Me.DepartmentList
        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 = "[DepartmentName] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Department: " & 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, , "CommandButton_Click"
    End If
    Resume Exit_Handler
End Sub

[rpeare]

Can you provide a sample database?

have you used debug.print at all to find out where it is breaking down and whether or not it constructing your strings correctly? I don't see anything glaring at me, just a few small things that I am not sure if it would affect your code or not.

[ItsMe]

You are going to run into a problem when you reach the limit of parameters allowed within your WHERE clause. Solution is to write key values to a temp table as you iterate your list boxes and use temp table in query.