Filter Table via Cascading Combo Boxes

[Glenn77]

Apologies in advance if my terminology is rather basic. I'm a newcomer to Access and layman's terms are the best I can do right now!

I'm having trouble filtering a subform via combo boxes to show me the exact result I need.

The subform (frmOverview subform) is based on this table:

BusinessUnitAreaLocationDisciplineNorthGardenShedSeedsNorthGardenShedAnimalsSouthOrchardTreesSeedsEastFarmBarnAnimalsWestBankHedgesGardeningNorthStreamBridgeWateringSouthOrchardTreesSeedsEastFarmBarnUtilsWestBankHedgesWoodwork

In turn, the subform is placed in a form (frmOverview), which contains 4 cascading combo boxes to allow me to filter all the way down to the discipline field. Combo18 is where I filter for a Business Unit, which then displays Combo20 to allow me to filter for Area. This then displays Combo22 for the Location filter.

The final combo box (Combo24) is where I'm having problems. If I wish to then filter by Discipline, I want the filter to "remember" all of the previously selected choices. For example, if I choose 'North - Garden - Shed - Seeds', only the one row should show. However, when I pick 'Seeds' from Combo24, I see all rows that have 'Seeds' in the Discipline column (3 in total).

The code I currently have assigned to the "On Change" event procedure of each combo box is:

--------------------------------------------------------------------
Private Sub Combo18_Change()
Me.Combo20.Requery
Me.Combo20 = ""
Me.Combo20.Visible = True
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "BusinessUnit= '" & Me.Combo18 & "'"
End Sub

Private Sub Combo20_Change()
Me.Combo22.Requery
Me.Combo22 = ""
Me.Combo22.Visible = True
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "Area= '" & Me.Combo20 & "'"
End Sub

Private Sub Combo22_Change()
Me.Combo24.Requery
Me.Combo24 = ""
Me.Combo24.Visible = True
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "Location= '" & Me.Combo22 & "'"
End Sub

Private Sub Combo24_Change()
Me.[frmOverview subform].Form.OrderByOn = True
Me.[frmOverview subform].Form.FilterOn = True
Me.[frmOverview subform].Form.Filter = "Discipline= '" & Me.Combo24 & "'"
End Sub
-------------------------------------------------------------------
I guess I have to somehow link the combo boxes together but have no idea how to do this. If someone out there could modify the code for me, I can finally stop tearing my hair out!

Best wishes.

 

[Glenn77]

Oops,

Looks like my table didn't appear as planned.

See attached for the example.

 

[jdraw]

Oops,

Looks like my table didn't appear as planned.

See attached for the example.

Glenn77,

Here's a tutorial by Martin Green that shows some dynamic reporting and the use of Comboboxes and filters. He builds a string based on combo box selections and then does the filtering.
Hopefully it is helpful to your issue with subform.

http://www.fontstuff.com/access/acctut19.htm

 

[Glenn77]

I'll give that a look and see how I get on.

Thanks very much for your time, jdraw. It's most appreciated.