Currently I have a form (tblTaxonomy) with 6 combo boxes that each will filter the results on a Subform (tblTaxonomysubform). Via this:
Private Function RunFilter()
On Error GoTo Error_RunFilter
Dim FilterClause As String, D As Long
Dim BoxSource As String
'Hold whether we Use AND or OR in our Filter Criteria
D = 1
If Nz(Me.cboType.Column(0), 0) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[txtType]='" & Me.cboType.Value & "'"
'Fill this Form wide variable so that it can be used for
CurrentFilter = FilterClause: FilterClause = ""
'Place our created Filter Criteria into the Filter property of SubForm.
tblTaxonomysubform.Form.Filter = CurrentFilter
'Turn on the Filter
tblTaxonomysubform.Form.FilterOn = True
Each combo box is also set to filter the results of the next combo box via this AfterUpdate:
This works fine if the user is going to go through the boxes in order, but it has been requested that I find a way to allow them to be used out of order. Where I am having trouble is I want to have all combo boxes filter by all boxes that have been selected. For Example:
Field One Values: Trees, Birds, Fish
Field Two Values: Pine, Birch, Crow, Seagull, Trout, Bass
Field Three Values: Water, Land, Air
If someone started with the Combo Box for field two and selected Birch then I would only want box one to say Trees and box three to say Land. Thats a really simplified example, but I hope you get the idea.