Please Help with CheckBox Filter

[dshato]

Hello everyone, I am trying to create a database email filter based on selected checkboxes. I am using a table subform in the form in order to display filtered out content. However, I am having trouble with content display when more than one checkbox is selected. Here is the code I am using. If it is not too much trouble please help out. Thanks a lot...

-----------------------------------------------
Option Compare Database
Option Explicit

'--> Set default record source of form
Const strInfo = "SELECT strFaction,strLastName,strFirstName,strEmailAddress,strFaxNumber,strState FROM tblContacts subform"

------------------------------------------------

Private Sub cmdFilt_Click()
Dim strFilterSQL As String

If chk1.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Architecture';"

ElseIf chk2.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Government';"

ElseIf chk3.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Planning';"

ElseIf chk4.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'AEP';"

ElseIf chk5.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Client';"

ElseIf chk6.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Government';"

ElseIf chk7.Value = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Organization';"

ElseIf chk8.Value = True Then
strFilterSQL = strInfo & " Where [strState] = 'New York';"

ElseIf chk9.Value = True Then
strFilterSQL = strInfo & " Where [strState] = 'New Jersey';"

ElseIf chk10.Value = True Then
strFilterSQL = strInfo & " Where [strState] = 'Connecticut';"

End If

Me.RecordSource = strFilterSQL
Me.Requery

End Sub
--------------------------------------------------

 

[CautionMP]

dshato,
Change your [tt]If...ElseIf...[/tt] statements to individual [tt]If...Then[/tt] statements. The way you have it set up now once there is a match on one of the check boxes the routine exits without checking any other check boxes.

You may also look at using an [tt]IN[/tt] statement in your filter, it might be easier to setup.

Dim strFilterSQL As String
strFilterSQL = "'1'"

If chk1.Value = True Then
  strFilterSQL = strFilterSQL & ",'Architecture'"
End if

If chk2.Value = True Then
  strFilterSQL = strFilterSQL & ",'Government'"
End if
...

Me.RecordSource = strInfo & " Where [strFaction] [b]IN[/b]( " & strFilterSQL & ");"

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dshato]

Thank you very much for your expertise, however it seems to behave the same way, only displays one faction at a time even though I selected two or more check boxes.

 

[CautionMP]

dshato,
If you paste the results of [tt]strInfo & " Where [strFaction] IN( " & strFilterSQL & ");"[/tt] into an new query do you get the records you expect?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[TheAceMan1]

dshato . . .

Here's the complete code . . .

[blue]Public Sub cmdFilt()
   Dim SQL As String, idx As Integer, ctl As Control
   Dim Facts As String, States As String, Cri As String
   
   For Each ctl In Me.Controls
      If (ctl.Tag = "F" Or ctl.Tag = "S") And ctl = True Then
         idx = Val(Right(ctl.Name, 1))
         Fltr = Choose(idx, "Architecture", "Government", "Planning", _
                            "AEP", "Client", "Government", _
                            "Organization", "New York", "New Jersey", _
                            "Connecticut")
         If ctl.Tag = "F" Then
            If Facts <> "" Then
               Facts = Facts & ", '" & Fltr & "'"
            Else
               Facts = "'" & Fltr & "'"
            End If
         Else
            If States <> "" Then
               States = States & ", '" & Fltr & "'"
            Else
               States = "'" & Fltr & "'"
            End If
         End If
      End If
   Next
            
   If Facts <> "" Then
      Cri = " WHERE [strFaction] IN (" & Facts & ")"
   End If
   
   If States <> "" Then
      If Cri <> "" Then
         Cri = Cri & " AND [strState] IN (" & States & ")"
      Else
         Cri = " WHERE [strState] IN (" & States & ")"
      End If
   End If
   
   SQL = "SELECT strFaction, strLastName, strFirstName, " & _
         "strEmailAddress, strFaxNumber, strState " & _
         "FROM tblContacts subform" & _
         Cri & ";"
   Me.RecordSource = SQL

End Sub[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[dshato]

Thanks a lot guys for all your help, sorry for getting back so late. I'll make sure to give all of you credit before presenting it to my boss. Thanks again...