Filtering a Filter

[Kaosu]

My database holds a list of names for all employees from my comany past and present. There is a tickbox on one of my forms that displays in red writing that the person is no longer with the company. I wanted to know if there was a way to use this tickbox in a name search that will eliminate the leavers from the current staff in the results list.

Name search:

Private Sub fnSRH_AfterUpdate()
    Dim strFilter As String
    strFilter = ""
    If IsNull(Me!fnSRH) Then
        strFilter = strFilter
    Else
        If strFilter = "" Then
            strFilter = strFilter & "FullName Like '*" & Me!fnSRH & "*'"
        Else
            strFilter = strFilter & " AND FullName Like '*" & Me!fnSRH & "*'"
 
    End If
    End If
 
    Me.Filter = strFilter
    Me.FilterOn = True
    Me!fnSRH = Null
 
End Sub 

ID search:

Private Sub idSRH_AfterUpdate()
    DoCmd.GoToControl "ID"
    DoCmd.FindRecord Me!idSRH
    Me!idSRH = Null
 
End Sub 

[JKing]

If I understand your problem correctly, then all you would need to do is extend your filter criteria. You can do this by adding an "AND" clause to your statement that checks to see if your checkbox field is true or false.

Example:

Assuming your checkbox field is called "unemployed", this example would filter the form by any names that match the control fnSRH and also have the field unemployed set to false.

strFilter = strFilter & "FullName ='" & Me!fnSRH & "' AND unemployed = FALSE"
 

You can filter by any field you want and as many fields as you want or need to. You just have to ensure that your filter strings are properly formed.

Hope this helps you,
Jared

[Kaosu]

thanks, that worked very well.