Using Three Combo Boxes to Apply Progressive Filter on Subform

[ataylo27]

I have a form with three combo boxes. I want the values selected in these boxes to filter the records returned in an attached subform.

The first combo, cboSelect Tester, has the following AfterUpdate code and works perfectly:

Private Sub cboSelectTester_AfterUpdate()
If Not IsNull([cboSelectTester]) Then
Me.Filter = "TesterName = '" & Me.cboSelectTester & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub


The second combo, cboSelectPatient, also works fine. If a value is selected in the first combo (cboSelectTester) and this second box, the filters work together and further limit the visible records. If nothing is selected in the first box, this second box selection correctly filters qualifying records. This combo has the following AfterUpdate code:

Private Sub cboSelectPatient_AfterUpdate()
If Not IsNull([cboSelectPatient]) And Not IsNull([cboSelectTester]) Then
Me.Filter = "PatientName = '" & Me.cboSelectPatient _
& "' AND TesterName = '" & Me.cboSelectTester & "' "
ElseIf Not IsNull([cboSelectPatient]) Then
Me.Filter = "PatientName = '" & Me.cboSelectPatient & "'"
ElseIf Not IsNull([cboSelectPatient]) Then
Me.Filter = "TesterName = '" & Me.cboSelectTester & "'"
End If
Me.FilterOn = True
End Sub


My problem is the third combo, cboSelectDay. If this is the ONLY combo where a selection occurs, it works fine. Here is the AfterUpdate code:

Private Sub cboSelectDay_AfterUpdate()
If Not IsNull([cboSelectDay]) And Not IsNull([cboSelectPatient]) And Not IsNull([cboSelectTester]) Then
Me.Filter = "Day = " & Me.cboSelectDay _
& "' AND PatientName = '" & Me.cboSelectPatient _
& "' AND TesterName = '" & Me.cboSelectTester & "' "
ElseIf Not IsNull([cboSelectDay]) Then
Me.Filter = "Day = " & Me.cboSelectDay
ElseIf Not IsNull([cboSelectPatient]) Then
Me.Filter = "PatientName = '" & Me.cboSelectPatient & "'"
ElseIf Not IsNull([cboSelectPatient]) Then
Me.Filter = "TesterName = '" & Me.cboSelectTester & "'"
End If
Me.FilterOn = True


But, if there are selections in either one of the two previous boxes, it fails to display the correct records, which should be a combination of both the first and third, or second and third combo boxes. Instead, the records displayed are those that qualify ONLY on this third combo.

If selections are made in BOTH the previous boxes, when I make a selection in the third combo, I actually get an error message:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Day = 4' AND PatientName = 'PATIENTNAME' AND TesterName = 'TESTERNAME''.


I suspect part of the problem (at least with the run-time syntax error) is that the third combo box contains NUMERIC values (the field is actually defined as NUMBER in the table design).

I would greatly appreciate any help!

 

[Uncle Gizmo]

I did this video and blog a while ago I think it relates to your question:-

MS Access - Combo Filter all or Some

 

[ataylo27]

Can't wait to give this look-see! Thank you!