Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009

    Unanswered: using two multi-select list boxes to filter report


    I'm trying to figure out how to use two multi-select list boxes to filter a report. I want the user to select a CR code (a code that identifies the reason for a value change) from a multi-select list box and select a district from another multi-select list box and have the report be based on which values they select from the two boxes. I'm able to filter the report using one multi-select list box using this VBA code

    Private Sub Open_Report_Click()

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

    If Me.CRlb.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Change Reason Code"
    Exit Sub
    End If

    Set ctl = Me.CRlb
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

    Next varItem

    strWhere = Left(strWhere, Len(strWhere) - 1)

    DoCmd.OpenReport "RPTAddDel", acViewReport, , "cr IN(" & strWhere & ")"

    End Sub

    But when I create the district multi-select list box, I'm not sure how to incorporate that into the VBA code.

    Any help is appreciated

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    1. Use a loop similar to the one you use for building the strWhere criteria, but for the second listbox (--> strWhere2).

    2. Join both criteria with an And operator:
    "cr IN(" & strWhere & ") AND District IN(" & strWhere2 & ")"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts