How do I filter a Filter

[Keith]

I have a form with a make filter function to enable users to pick to display records. I have an option group allowing users to select records begining with a letter or number or begining with a group of letters. I have a check box control 'StatusActive' which when clicked should, when clicked, restrict the pick to display list box to active records only. Each record has a yes/no field [Active]. I cannot work out how to adapt the code (not my code) to make this work. Any pointers would be appreciated.

Private Function MakeFilter()
    On Error GoTo MakeFilter_error

    Dim s As String

    If Nz(Me![ctlLike], "") = "" Then
        Select Case Nz(Me![OpGroupIndex], 1)
        Case 1
            Me.FilterOn = False
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s
            Me![ctlLocateRecord] = Null
        Case 28
            Me.Filter = "IsNumeric(Left([" & Me![ctlField] & "],1))"
            Me.FilterOn = True
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] WHERE IsNumeric(Left([" & Me![ctlField] & "],1)) ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s
            Me![ctlLocateRecord] = Null
        Case Else
            Me.Filter = "[" & Me![ctlField] & "] LIKE """ & _
                Chr(Me![OpGroupIndex] + 63) & "*"""
            Me.FilterOn = True
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] WHERE [" & Me![ctlField] & "] LIKE """ & Chr(Me![OpGroupIndex] + _
                63) & "*"" ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s
            Me![ctlLocateRecord] = Null
        End Select
    Else
        Me.Filter = "[" & Me![ctlField] & "] LIKE ""*" & Replace(Me![ctlLike], _
            """", """""") & "*"""
        Me.FilterOn = True
        Me.OrderBy = Me![ctlField]
        Me.OrderByOn = True
        s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
            "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
            "] WHERE [" & Me![ctlField] & "] LIKE ""*" & Replace(Me![ctlLike], """", _
            """""") & "*"" ORDER BY 1"
        Me![ctlLocateRecord].RowSource = s
        Me![ctlLocateRecord] = Null
        Me![OpGroupIndex] = Null
    End If
    Me.ctlLike = ""
   
MakeFilter_Exit:
    Exit Function

MakeFilter_error:
    Select Case Err
    Case Else
        MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, _
            "Error in module MakeFilter"
        Resume MakeFilter_Exit
    End Select
End Function

 

[Keith]

Re: How do I filter a Filter (Solved)

As usual posted in haste. Sat looking at my post and the light came on

Private Function MakeFilter()
    On Error GoTo MakeFilter_error

    Dim s As String

    If Nz(Me![ctlLike], "") = "" Then
        Select Case Nz(Me![OpGroupIndex], 1)
        Case 1
            Me.FilterOn = False
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] "    ' ORDER BY 1"

            If Me![StatusActiveOnly] Then
                If s <> "" Then
                    s = s & " WHERE "
                End If
                s = s & "([Status] = ""Current"")"
                Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
                Exit Function
            End If

            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] "    ' ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
            Me![ctlLocateRecord] = Null
        Case 28
            Me.Filter = "IsNumeric(Left([" & Me![ctlField] & "],1))"
            Me.FilterOn = True
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] WHERE IsNumeric(Left([" & Me![ctlField] & "],1)) "    ' ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
            Me![ctlLocateRecord] = Null
        Case Else
            Me.Filter = "[" & Me![ctlField] & "] LIKE """ & _
                        Chr(Me![OpGroupIndex] + 63) & "*"""
            Me.FilterOn = True
            Me.OrderBy = Me![ctlField]
            Me.OrderByOn = True
            s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
                "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
                "] WHERE [" & Me![ctlField] & "] LIKE """ & Chr(Me![OpGroupIndex] + _
                                                                63) & "*"" "    'ORDER BY 1"
            Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
            Me![ctlLocateRecord] = Null
        End Select
    Else
        Me.Filter = "[" & Me![ctlField] & "] LIKE ""*" & Replace(Me![ctlLike], _
                                                                 """", """""") & "*"""
        Me.FilterOn = True
        Me.OrderBy = Me![ctlField]
        Me.OrderByOn = True
        s = "SELECT DISTINCT NZ([" & Me![ctlField] & _
            "],""**empty**"") AS [PICK TO DISPLAY] FROM [" & Me.RecordSource & _
            "] WHERE [" & Me![ctlField] & "] LIKE ""*" & Replace(Me![ctlLike], """", _
                                                                 """""") & "*"" "    ' ORDER BY 1"
        Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
        Me![ctlLocateRecord] = Null
        Me![OpGroupIndex] = Null
    End If
    Me.ctlLike = ""
    If Me![StatusActiveOnly] Then
        If s <> "" Then
            s = s & " AND "
        End If
        s = s & "([Status] = ""Current"")"
        Me![ctlLocateRecord].RowSource = s & "ORDER BY 1"
    End If
MakeFilter_Exit:
    Exit Function

MakeFilter_error:
    Select Case Err
    Case Else
        MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, _
               "Error in module MakeFilter"
        Resume MakeFilter_Exit
    End Select
End Function