' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.x for DDL and Security
' (where x is the highest available number).

Private Sub cmdOK_Click()
    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
    Dim varItem As Variant
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strDepartmentCondition As String
    Dim strGenderCondition As String
    Dim strSQL As String
' Check for the existence of the stored query
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qryStaffListQuery" Then
            blnQueryExists = True
            Exit For
        End If
    Next qry
' Create the query if it does not already exist
    If blnQueryExists = False Then
        cmd.CommandText = "SELECT * FROM tblStaff"
        cat.Views.Append "qryStaffListQuery", cmd
    End If
    Application.RefreshDatabaseWindow
' Turn off screen updating
    DoCmd.Echo False
' Close the query if it is already open
    If SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryStaffListQuery"
    End If
' Build criteria string for Office
    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 for Department
    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 for Gender
    For Each varItem In Me.lstGender.ItemsSelected
        strGender = strGender & ",'" & Me.lstGender.ItemData(varItem) & "'"
    Next varItem
    If Len(strGender) = 0 Then
        strGender = "Like '*'"
    Else
        strGender = Right(strGender, Len(strGender) - 1)
        strGender = "IN(" & strGender & ")"
    End If
' Get Department condition
    If Me.optAndDepartment.Value = True Then
        strDepartmentCondition = " AND "
    Else
        strDepartmentCondition = " OR "
    End If
' Get Gender condition
    If Me.optAndGender.Value = True Then
        strGenderCondition = " AND "
    Else
        strGenderCondition = " OR "
    End If
' Build SQL statement
    strSQL = "SELECT tblStaff.* FROM tblStaff " & _
             "WHERE tblStaff.[Office] " & strOffice & _
             strDepartmentCondition & "tblStaff.[Department] " & strDepartment & _
             strGenderCondition & "tblStaff.[Gender] " & strGender & ";"
' Apply the SQL statement to the stored query
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryStaffListQuery").Command
    cmd.CommandText = strSQL
    Set cat.Views("qryStaffListQuery").Command = cmd
    Set cat = Nothing
' Open the Query
    DoCmd.OpenQuery "qryStaffListQuery"
' If required the dialog can be closed at this point
 '    DoCmd.Close acForm, Me.Name
' Restore screen updating
    DoCmd.Echo True
End Sub

Private Sub optAndDepartment_Click()
' Toggle option buttons
    If Me.optAndDepartment.Value = True Then
        Me.optOrDepartment.Value = False
    Else
        Me.optOrDepartment.Value = True
    End If
End Sub

Private Sub optAndGender_Click()
' Toggle option buttons
    If Me.optAndGender.Value = True Then
        Me.optOrGender.Value = False
    Else
        Me.optOrGender.Value = True
    End If
End Sub

Private Sub optOrDepartment_Click()
' Toggle option buttons
    If Me.optOrDepartment.Value = True Then
        Me.optAndDepartment.Value = False
    Else
        Me.optAndDepartment.Value = True
    End If
End Sub

Private Sub optOrGender_Click()
' Toggle option buttons
    If Me.optOrGender.Value = True Then
        Me.optAndGender.Value = False
    Else
        Me.optAndGender.Value = True
    End If
End Sub