Access Search Multiple Combo on Continuous Form
I'd be inclined to do this with VBA code.
I'd change the AttendeeFilter command button into a check box, "chkFilterAttendees" -- when checked, a filter on attendees < 7 is desired; unchecked = no filter on attendees.
A single command button, "cmdFilter", would be used to construct and apply a filter determined by the values of cboCourse, cboLocation, and chkFilterAttendees. The command button's Click event procedure would be something like this:
'------ start of code ------
Private Sub cmdFilter_Click()
Dim strFilter As String
If Len(Me.cboCourse & "") <> 0 Then
' If Course is numeric, use this:
strFilter = strFilter & " AND (Course = " & Me.cboCourse & ")"
' If Course is text, use this:
'strFilter = strFilter & " AND (Course = """ & Me.cboCourse & """)"
End If
If Len(Me.cboLocation & "") <> 0 Then
' If Location is numeric, use this:
strFilter = strFilter & " AND (Location = " & Me.cboLocation & ")"
' If Location is text, use this:
'strFilter = strFilter & " AND (Location = """ & Me.cboLocation & """)"
End If
If Nz(Me.chkFilterAttendees, 0) <> 0 Then
strFilter = strFilter & " AND (AttendeeCount < 7)"
End If
If Len(strFilter) > 0 Then
Me.Filter = Mid$(strFilter, 6)
Me.FilterOn = True
Else
Me.FilterOn = False
Me.Filter = ""
End If
End Sub
'------ end of code ------
Of course, you'd have to adjust field names and allow for text vs. numeric fields in the above code.
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
1 person found this reply helpful
·
Was this reply helpful?
Dirk, beat me to it, but he illustrated the same method I use in the same situation. You build your filter control by control. You test to see if the control has a selection. If it does then you add to the existing filter or start a filter. Once you have built the string, you set the Form's Filter property to the string and apply the filter.
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007
Was this reply helpful?
Personally I'd adopt a different approach, and base the form on a query which references the unbound controls as parameters in its WHERE clause:
WHERE (Course = Forms!YourForm!cboCourse
OR Forms!YourForm!cboCourse IS NULL)
AND (Location = Forms!YourForm!cboLocation
OR Forms!YourForm!cboLocation IS NULL)
AND ((AttendeeCount < 7 AND
Forms!YourForm!chkLocationchkFilterAttendees = TRUE)
OR Forms!YourForm!chkLocationchkFilterAttendees = FALSE)
The paraentheses are very important in the above to force the AND and OR operations to evaluate independently. In the form, to apply the restriction you just need to requery the form with:
Me.Requery
which you can do in the Click event procedure of a separate button, or in the AfterUpdate event procedure of each of the three controls to apply the restriction progressively as soon as a value is selected in one of the controls or the control is cleared of its value (or unchecked in the case of the check box).
To clear the controls and show all rows you can have a 'Show All' button with the following code in its Click event procedure:
Me.cboCourse = Null
Me.cboLocation = Null
Me.chkLocationchkFilterAttendees = False
Me.Requery
With the above approach you do not have to concern yourself at all with the data types of the columns.
If you are unfamiliar with entering code into a form's or control's event procedures, this is how it's done in form design view:
1. Select the form or control as appropriate and open its properties sheet if it's not already open.
2. Select the relevant event property and select the 'build' button (the one on the right with 3 dots).
3. Select Code Builder in the dialogue and click OK. This step won't be necessary if you've set up Access to use event procedures by default.
4. The VBA editor window will open at the event procedure with the first and last lines already in place. Enter or paste in the code as new lines between these.
_____________________ "Don't write it down until you understand it!" - Richard Feynman
Ken Sheridan,
Cannock, England
3 people found this reply helpful
·
Was this reply helpful?
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/filtering-a-continuous-form-using-multiple-combo/2d3f78c8-a74d-4bc5-a4e0-9d488ce9d63d
Post a Comment for "Access Search Multiple Combo on Continuous Form"