Filtering a form with multiple check boxes

What I have is a form which shows company information and also multiple contact subform that shows contact information, seperated by what the contacts job responsibility is(tabbed).

I have a combo box on the company form which displays the job responsibilities, and once a responsibility is selected it will show only company records with a contact of the selected responsibility. This is the code;

Sub SetFilter()

Dim ASQL As String

If IsNull(Me.cboshowcat) Then
' If the combo and all check boxes are Null, use the whole table as the RecordSource.
Me.RecordSource = "SELECT company.* FROM company"

Else

ASQL = "SELECT company.* FROM company INNER JOIN Contacts ON company.company_id = Contacts.company_id WHERE Contacts.responsibility= '" & cboshowcat & "'"
Form_Startup.RecordSource = ASQL

End If

End Sub

The company table will then only show records with a contact of the type specified in the cboshowcat combo box, without showing duplicate c开发者_StackOverflowompanies on the main form.

I then want to apply some further filters based on check boxes on the main form, which relate to fields on the contacts subform. These are activated by a button along with the recordsource code from above;

Private Sub Command201_Click()

If Nz(Me.cboshowcat) = "" And Me.Check194 = True Or Nz(Me.cboshowcat) = "" And Me.Check199 = True Or Nz(Me.cboshowcat) = "" And Me.Check205 = True Then
    MsgBox "Please Select a Job Responsibility"
    Cancel = True
Else

SetFilter
If Me.Check194 = True Then
Me.Filter = "cedit <=Date()-90"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
If Me.Check199 = True Then
Me.Filter = "((copt)='No')"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
If Me.Check205 = True Then
Me.Filter = "exsite is null"
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End If
End If
End If

End Sub

At the moment the button filters out the selected contacts by category but if more than 1 of the checkboxes are checked it only uses one of the filters. How do I make it use multiple filters combined depending which tick box is ticked?

If you want to filter on multiple criteria then you need to construct the Filter string value to reflect that. Eg:

Me.Filter = "cedit <=Date()-90 AND exsite is null"

Start with an empty string and go through the checkboxes, building the string as appropriate: if at the end of that process there's a filter string, apply it.

Here's a suggestion on how to do this efficiently:

Dim sFilter as String
sFilter=""

'...
If Me.Check194 Then BuildFilter sFilter, "cedit <=Date()-90" 
If Me.Check205 Then BuildFilter sFilter, "exsite is null"
If Me.Check199 Then BuildFilter sFilter, "((copt)='No')" 
'... more checkboxes...


Me.FilterOn = Len(sFilter)>0
Me.Filter = sFilter
'....


Sub BuildFilter(byRef sFilt as string, sAdd as string)
   If len(sFilt)>0 Then sFilt = sFilt & " AND "
   sFilt = sFilt & sAdd
End sub