Filtering a continuous form using multiple combo boxes and a separate button

Question

Good afternoon all!

I have a continuous form which I am trying to filter using independent combo boxes and a separate button. I have found that using a SetFilter or ApplyFilter macro has been easiest for me when creating individual filters and they work perfectly. 

This is what I have:

cboCourse - Combo box linked to the Course table. There is a SetFilter Macro assigned to a button which filters the form based on the cboCourse selection

cboLocation - Combo box linked to the Training Location table. There is a SetFilter Macro assigned to a button which filters the form based on the cboLocation selection

AttendeeFilter - Command button linked to a SetFilter Macro which filters the form based on the AttendeeCount field being below 7 attendees

What I would like to have is the ability to select information in one or more of the above and have the restricted data shown. At the moment it either applies just one filter or shows nothing at all! How can I see filtered information based on the details shown in the cboCourse and/or cboLocation and, if needed, the AttendeeFilter?

I also have a command button with the ShowAllRecords that clears the filters I have. Can you confirm that your answer will allow this to continue or would I need to reprogramme that as well. If so how?

Thanks for all your help now, in the past and the future!

Thanks

Alastair

Answer 1

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.

Answer 2

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.

Answer 3

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.