Help with adding checkboxes to search form (1 Viewer)

miked1978

New member
Local time
Today, 04:51
Joined
May 22, 2020
Messages
25
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



C++:
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:51
Joined
Oct 29, 2018
Messages
14,049
I'm not sure how to add the checkboxes to the VBA code
Hi. Maybe something like:
Code:
strSQL = strSQL & "Tasks.[Assigned To] = '" & cbo_Employee & "' AND [FieldName]="  & Me.CheckboxName
 

Doubleecpu

New member
Local time
Today, 02:51
Joined
Apr 23, 2020
Messages
2

'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
 
Last edited:

Users who are viewing this thread

Top Bottom