Private Sub cboSortOrder1_BeforeUpdate(Cancel As Integer)
' Check if sort field has already been chosen
    If Me.cboSortOrder1.Value <> "Not Sorted" Then
        If Me.cboSortOrder1.Value = Me.cboSortOrder2.Value _
        Or Me.cboSortOrder1.Value = Me.cboSortOrder3.Value Then
            MsgBox "You already chose that item."
            Cancel = True
            Me.cboSortOrder1.Dropdown
        End If
    End If
End Sub
 
Private Sub cboSortOrder1_Change()
' Disable following sort options if "Not Sorted" is chosen
    Dim i As Integer
    If Me.cboSortOrder1.Value = "Not Sorted" Then
        For i = 2 To 3
            With Me.Controls("cboSortOrder" & i)
                .Enabled = False
                .Value = "Not Sorted"
            End With
        Next i
        For i = 1 To 3
            With Me.Controls("cmdSortDirection" & i)
                .Enabled = False
                .Caption = "Ascending"
            End With
        Next i
    Else
        Me.cboSortOrder2.Enabled = True
        Me.cmdSortDirection1.Enabled = True
    End If
End Sub
 
Private Sub cboSortOrder2_BeforeUpdate(Cancel As Integer)
' Check if sort field has already been chosen
    If Me.cboSortOrder2.Value <> "Not Sorted" Then
        If Me.cboSortOrder2.Value = Me.cboSortOrder1.Value _
        Or Me.cboSortOrder2.Value = Me.cboSortOrder3.Value Then
            MsgBox "You already chose that item."
            Cancel = True
            Me.cboSortOrder2.Dropdown
        End If
    End If
End Sub
 
Private Sub cboSortOrder2_Change()
' Disable following sort options if "Not Sorted" is chosen
    Dim i As Integer
    If Me.cboSortOrder2.Value = "Not Sorted" Then
        With Me.cboSortOrder3
            .Enabled = False
            .Value = "Not Sorted"
        End With
        For i = 2 To 3
            With Me.Controls("cmdSortDirection" & i)
                .Enabled = False
                .Caption = "Ascending"
            End With
        Next i
    Else
        Me.cboSortOrder3.Enabled = True
        Me.cmdSortDirection2.Enabled = True
    End If
End Sub
 
Private Sub cboSortOrder3_BeforeUpdate(Cancel As Integer)
' Check if sort field has already been chosen
    If Me.cboSortOrder3.Value <> "Not Sorted" Then
        If Me.cboSortOrder3.Value = Me.cboSortOrder1.Value _
        Or Me.cboSortOrder3.Value = Me.cboSortOrder2.Value Then
            MsgBox "You already chose that item."
            Cancel = True
            Me.cboSortOrder3.Dropdown
        End If
    End If
End Sub
 
Private Sub cboSortOrder3_Change()
' Disable sort direction button if "Not Sorted" is chosen
    If Me.cboSortOrder3.Value = "Not Sorted" Then
        With Me.cmdSortDirection3
            .Enabled = False
            .Caption = "Ascending"
        End With
    Else
        Me.cmdSortDirection3.Enabled = True
    End If
End Sub
 
Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strFilter As String
    Dim strSortOrder 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
' Build sort string
    If Me.cboSortOrder1.Value <> "Not Sorted" Then
        strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
        If Me.cmdSortDirection1.Caption = "Descending" Then
            strSortOrder = strSortOrder & " DESC"
        End If
        If Me.cboSortOrder2.Value <> "Not Sorted" Then
            strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
            If Me.cmdSortDirection2.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
            End If
            If Me.cboSortOrder3.Value <> "Not Sorted" Then
                strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
                If Me.cmdSortDirection3.Caption = "Descending" Then
                    strSortOrder = strSortOrder & " DESC"
                End If
            End If
        End If
    End If
' Apply filter and sort to report
    With Reports![rptStaff]
        .Filter = strFilter
        .FilterOn = True
        .OrderBy = strSortOrder
        .OrderByOn = True
    End With
End Sub
 
Private Sub cmdRemoveFilter_Click()
    Dim varItem As Variant
    Dim i As Integer
' Remove filter and sort from report
    On Error Resume Next
    With Reports![rptStaff]
        .FilterOn = False
        .OrderByOn = False
    End With
    On Error GoTo 0
' Reset form to original values
    For Each varItem In Me.lstOffice.ItemsSelected
        Me.lstOffice.Selected(varItem) = False
    Next varItem
    For Each varItem In Me.lstDepartment.ItemsSelected
        Me.lstDepartment.Selected(varItem) = False
    Next varItem
    Me.fraGender.Value = 3
    For i = 1 To 3
        Me.Controls("cboSortOrder" & i).Value = "Not Sorted"
        Me.Controls("cboSortOrder" & i).Enabled = False
        Me.Controls("cmdSortDirection" & i).Enabled = False
        Me.Controls("cmdSortDirection" & i).Caption = "Ascending"
    Next i
    Me.cboSortOrder1.Enabled = True
End Sub
 
Private Sub cmdSortDirection1_Click()
' Toggle button caption
    If Me.cmdSortDirection1.Caption = "Ascending" Then
        Me.cmdSortDirection1.Caption = "Descending"
    Else
        Me.cmdSortDirection1.Caption = "Ascending"
    End If
End Sub
 
Private Sub cmdSortDirection2_Click()
' Toggle button caption
    If Me.cmdSortDirection2.Caption = "Ascending" Then
        Me.cmdSortDirection2.Caption = "Descending"
    Else
        Me.cmdSortDirection2.Caption = "Ascending"
    End If
End Sub
 
Private Sub cmdSortDirection3_Click()
' Toggle button caption
    If Me.cmdSortDirection3.Caption = "Ascending" Then
        Me.cmdSortDirection3.Caption = "Descending"
    Else
        Me.cmdSortDirection3.Caption = "Ascending"
    End If
End Sub