Help with adding checkboxes to search form

[miked1978]

I have a combobox on my main form that the user selects the employee and the the subform displays the results in a datasheet view. The code is below. I'd like to add 2 check boxes as additional criteria the user can search with. First checkbox will be "Open Tasks" and the 2nd checkbox would be "Closed Tasks". By default i'd like both checkboxes selected.

My checkbox names:
Open Tasks checkbox name: chkOpen

Closed Tasks checkbox name: chkClosed​

Private Sub cbo_Search_Click()


Dim Varitem As Variant
Dim strEmployee As String
Dim strSQL As String


' Gets what Employee has been selected
For Each Varitem In Me!cbo_Employee.ItemsSelected
   strEmployee = strEmployee & ",'" & Me!cbo_Employee.ItemData(Varitem) & "'"
Next


strSQL = "SELECT * FROM Tasks WHERE "
    strSQL = strSQL & "Tasks.[Assigned To] = '" & cbo_Employee & "' "
 

        


Me.[Tasks subform].Form.RecordSource = strSQL

End Sub

 

[miked1978]

I'm not sure how to add the checkboxes to the VBA code

 

[theDBguy]

I'm not sure how to add the checkboxes to the VBA code

Hi. Maybe something like:

strSQL = strSQL & "Tasks.[Assigned To] = '" & cbo_Employee & "' AND [FieldName]="  & Me.CheckboxName

 

[June7]

Build SQL conditionally. Only include controls that have input. Review http://allenbrowne.com/ser-62.html

 

[Doubleecpu]

'Generates SQL statement if chKOpen = True AND chkClosed =True then strSQL = "SELECT * FROM Tasks " & _ "WHERE (((Tasks.[Assigned To] = '" & cbo_Employee & _ ")));" Else strSQL = "SELECT * FROM Tasks " & _ "WHERE (((Tasks.[Assigned To] = '" & cbo_Employee & "'" &_ ") AND (Tasks.[Open] = " & chkOpen & _ ") AND (Tasks.[Closed] = " & chkClosed & _ ")));" End IF 'debug.print strSQL Me.[Tasks subform].Form.RecordSource = strSQL

'if only open or not open then only need one AND statement
'Assumption is the task can exist in multiple states