Multicriteria search form - adding checkboxes (1 Viewer)

miked1978

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


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
 

miked1978

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

Code:
    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

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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

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


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

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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

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

AWF VIP
Local time
Today, 09:54
Joined
Mar 9, 2014
Messages
5,423
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

Enthusiastic Amateur
Local time
Today, 17:54
Joined
Sep 21, 2011
Messages
14,045
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?

Code:
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:54
Joined
Jul 9, 2003
Messages
16,244
By Default both checkboxes will be checked

Why not use an option group? You can set the option group to show checkboxes instead of option buttons. Now you can have one of the checkbox's selected, or none of the checkbox's selected, but you can never have both checkbox's selected. The point is, it automatically switches between checked and unchecked depending on which one you press. All the work is done for you WITHOUT CODING by the very nature of the control. Unless you have a pressing reason to have both checkboxes checked, which is making your life difficult for no good reason of course!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:54
Joined
Jul 9, 2003
Messages
16,244
If each checkbox is linked to an underlying field so that you can record which one was selected in the table, and I don't think this is a good idea because you only need one field to hold a value representing each checkbox but if you do happen to have two Fields (or more) then you might find my blog here useful:-


There's a YouTube Video demonstration of how you can can force only one checkbox to be checked. With this code you can duplicate an option group, but have individual fields store each option.
 

miked1978

New member
Local time
Today, 12:54
Joined
May 22, 2020
Messages
25
Thanks but I would need the option for both checkboxes to be checked.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:54
Joined
May 21, 2018
Messages
8,463
Thanks but I would need the option for both checkboxes to be checked
Option One: Open
Option Two: Closed
Option Three: All
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:54
Joined
Jul 9, 2003
Messages
16,244
Thanks but I would need the option for both checkboxes to be checked.

If the reason you need both checkboxes checked is to return "All the Records" then all you need do is have three selections in your option group. Add another selection for "All"
 

Users who are viewing this thread

Top Bottom