Multicriteria search form - adding checkboxes

[miked1978]

I have a search form and a subform underneath in datasheet view. On the search form i currently have a combobox where the user can select a name and press the search button (code below) and it gives the results for the name they selected. That part works fine. However I want to add 2 checkboxes to the main form that will filter on a field named "Status" The checkboxes will be named "Filter for Open" and "Filter for Closed". By Default both checkboxes will be checked so if the user only wants to filter for one they would need to unselect whichever checkbox they don't want to see.

I'm just not sure how to add the 2 checkboxes to my code below.

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

 

[June7]

Use conditional code to build criteria. Only use checkbox if it is checked. Review http://allenbrowne.com/ser-62.html

 

[miked1978]

I added the code below but I'm not sure what to put for my checkboxes in the strWhere line. Both are looking at the status field and the status field in the table will be populated with: Closed, In Work, On Hold, & Not Started as its values.

Open = In Work, On Hold, Not Started
Closed = Closed

Not sure how to tell VBA this and get the quatations correct!!!

    If Not IsNull(Me.cbo_Employee) Then
        StrWhere = StrWhere & "([Full Name] = " & Me.cbo_Employee & ") AND "
    End If
    
    If Not IsNull(Me.chkOpen) Then
        StrWhere = StrWhere & "([Status] = " & ??? & ") AND "    
    End If
    
    If Not IsNull(Me.cbo_ChkClosed) Then
        StrWhere = StrWhere & "([Status] = " & ??? & ") AND "
    End If

 

[Gasman]

If Closed is True then add the string "AND Status = Closed"
If Open is True, add the string "AND status <> Closed"

If both True, no additional string required?

 

[miked1978]

not sure if its working or not but now getting error "syntax error in union query and

Me.[Tasks subform].Form.RecordSource = StrWhere​

line is highlighted.

Private Sub cbo_Search_Click()

Dim StrWhere
Dim lngLen As Long


    If Not IsNull(Me.cbo_Employee) Then
        StrWhere = StrWhere & "([Full Name] = " & Me!cbo_Employee & ") AND "
    End If
    
     If Me.chkOpen = -1 Then
        StrWhere = StrWhere & "([Status]  <> Closed) AND "
        
    ElseIf Me.chkOpen = 0 Then
        StrWhere = StrWhere & "([Status] = Closed) AND "
    End If




    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(StrWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        StrWhere = Left$(StrWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = StrWhere
        Me.FilterOn = True
    End If
    
    Me.[Tasks subform].Form.RecordSource = StrWhere

End Sub

 

[Gasman]

I would create another string strExtra
Set that depending on your checkboxes and just add it to StrWhere.

What you have coded is not what I specifed either?

 

[miked1978]

Yeah I kept getting syntax errors so I stripped the AND off from yours and I stopped getting the errors.

I think I may just abandon this. I'm too new at VBA to make this work

Thanks for the help though.

 

[June7]

Only need this filter if one checkbox is checked and other is not. If both are checked or unchecked don't need to apply this criteria.

I would probably use a combobox with 3 choices or a triple-state checkbox or an option group with 3 checkboxes - Open, Closed, Both. If user chooses Both then just don't include the criteria.

 

[Gasman]

Something along the lines of below?
I've tried to keep it simple, as the first If could have used NOT logic.
This assumes you have either both or at least one True?

Dim strExtra as String
...
...after combo check
...
If Me.chkOpen and Me.chkClose
    ' No need to do anything
Else
    If Me.chkClosed then
        strExtra = " AND status = Closed"
    Else
        strExtra = " AND status <> Closed"
    End If
End If

strWhere = strWhere & strExtra

 

[miked1978]

Thanks but I would need the option for both checkboxes to be checked.

 

[MajP]

Thanks but I would need the option for both checkboxes to be checked

Option One: Open
Option Two: Closed
Option Three: All