CheckBox Filter Help (Again)

[dshato]

I already posted this question before, however some programmers although tried to help did not produce much of result, perhaps it was my fault that I did not described my problem very clearly. Anyhow, my question is this; I am trying to create a checkbox sort of the database based on the faction and the state.

I created a subfom which I linked to the table in the middle of the form and below it I created 10 checkbox options: Architecture, Engineering, Planning, AEP Firms, Private Clients, Organizations, Government, New York, New Jersey, and Connecticut.

In the table I created five categories strLastName, strFirstName, strEmailAddress, strFaction, and strState.

In strFaction I have variation of classifications from Architecture to Government, while in strState I inputted New York, New Jersey and Connecticut.

There are two main functions that I need this filter to perform. First is to filter information based on selected checkbox and output them in the subform, and second filter information in combination with state. (For example Architects from New York or Planners from New Jersey)

Any help will be greatly appreciated, thank you very much in advance...

 

[Remou]

Please post the code you have so far and please say in which way it is not working.

 

[dshato]

The code I used only filters only one faction at a time, and not in combination.

Here is what I have orignally:

Option Compare Database
Option Explicit

'--> Set default record source of form
Const strInfo = "SELECT strFaction, strLastName, strFirstName, strEmailAddress, strState FROM tblContacts"
----------------------------------------------------------
'--> Email Button Functions
Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim stWhere As String '<-- Criteria for DLookup
Dim varTo As Variant '<-- Address for SendObject
Dim stText As String '<-- E-mail text
Dim stSubject As String '<-- Subject line of e-mail
Dim stWho As String '<-- Reference to tblContacts

'--> Combo of names to email to
stWho = Me.strEmailAddress
stWhere = "tblContacts.strEmailAddress = " & "'" & stWho & "'"

'--> Looks up e-mail address from TblContacts
varTo = DLookup("[strEmailAddress]", "tblContacts", stWhere)

'--> Cutsomize subject line
stSubject = ":: Test ::"

'--> Customize e-mail body
stText = "Test"

'--> Write the e-mail content for sending to client
DoCmd.SendObject , , acFormatTXT, , , , stSubject, stText

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:

MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub
-----------------------------------------------------------
Private Sub cmdFilt_Click()

Dim strFilterSQL As String

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

If chk2 = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Engineering';"
Else
End If

If chk3 = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Planning';"
Else
End If

If chk4 = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'AEP';"
Else
End If

If chk5 = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Client';"
Else
End If

If chk6 = True Then
strFilterSQL = strInfo & " Where [strFaction] = 'Government';"
Else
End If

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

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

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

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

Me.RecordSource = strFilterSQL
Me.Requery

End Sub

----------------------------------------------------------
Private Sub Form_Timer()

Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")

End Sub

 

[PHV]

I guess that strInfo is a global variable defined elsewhere ?
What about this ?

Private Sub cmdFilt_Click()
Dim strFilterSQL As String
strFilter = " WHERE 1=1 "
If chk1 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Architecture' "
ElseIf chk2 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Engineering' "
ElseIf chk3 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Planning' "
ElseIf chk4 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='AEP' "
ElseIf chk5 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Client' "
ElseIf chk6 = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Government' "
ElseIf chk7.Value = True Then
  strFilterSQL = strFilterSQL & "AND strFaction='Organization' "
End If
If chk8.Value = True Then
  strFilterSQL = strFilterSQL & "AND strState='New York' "
ElseIf chk9.Value = True Then
  strFilterSQL = strFilterSQL & "AND strState='New Jersey' "
ElseIf chk10.Value = True Then
  strFilterSQL = strFilterSQL & "AND strState='Connecticut' "
End If
Me.RecordSource = strInfo & strFilterSQL
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886