Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strFilter As String
' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If
' Build criteria string from lstOffice listbox
    For Each varItem In Me.lstOffice.ItemsSelected
        strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strOffice) = 0 Then
        strOffice = "Like '*'"
    Else
        strOffice = Right(strOffice, Len(strOffice) - 1)
        strOffice = "IN(" & strOffice & ")"
    End If
' Build criteria string from lstDepartment listbox
    For Each varItem In Me.lstDepartment.ItemsSelected
        strDepartment = strDepartment & ",'" & Me.lstDepartment.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strDepartment) = 0 Then
        strDepartment = "Like '*'"
    Else
        strDepartment = Right(strDepartment, Len(strDepartment) - 1)
        strDepartment = "IN(" & strDepartment & ")"
    End If
' Build criteria string from fraGender option group
    SelectCase Me.fraGender.Value
        Case 1
            strGender = "='F'"
        Case 2
            strGender = "='M'"
        Case 3
            strGender = "Like '*'"
    End Select
' Build filter string
    strFilter = "[Office] " & strOffice & _
                " AND [Department] " & strDepartment & _
                " AND [Gender] " & strGender
' Apply the filter and switch it on
    With Reports![rptStaff]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub
 
Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
' Switch the filter off
    Reports![rptStaff].FilterOn = False
End Sub