Cascading(?) Combo-box Filters, Selection reduction problem.

[MOCaseA]

I have a record lookup form that has several combo box filters set up. However I noticed a slight problem. The filters are working correctly, but there are now over 2000 entries and when filtering there are too many possible selection even with other filters applied.

Example 1:
POC Doe, John wants to look up a record he created on 01/01/2010. He selects his name and after updating he accidentally selects 01/02/2010 (despite that fact that none of the records he created have this date) filtering out all possible results.

Example 2: Doe, John created 50 separate records on 01/01/2010 and after filtering down to his name and the Date he still has to go looking for the record. There are 3 other filtering options but he can't remember the needed information off the top of his head and there are well over 300 possible selections.

Even after the first filter is applied there are still several hundred options in several of the other combo boxes that the user must go through to find the appropriate data.

Solution: Reducing the number of options available on the other combo boxes to only available records each time after a filter is applied.

Below is my current code being used:

Private Sub CAR_AfterUpdate()
    SetFilter
End Sub
Private Sub Date_AfterUpdate()
    SetFilter
End Sub
Private Sub System_AfterUpdate()
    SetFilter
End Sub
Private Sub MAJCOM_AfterUpdate()
    SetFilter
End Sub
Private Sub System_POC_AfterUpdate()
    SetFilter
End Sub
Private Sub SetFilter()
    Dim FilterCriteria As String
    If CAR & "" <> "" Then FilterCriteria = FilterCriteria & " AND [CAR]='" & CAR & "'"
    If Date & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Date]='" & Date & "'"
    If System & "" <> "" Then FilterCriteria = FilterCriteria & " AND [System]='" & System & "'"
    If MAJCOM & "" <> "" Then FilterCriteria = FilterCriteria & " AND [MAJCOM]='" & MAJCOM & "'"
    If System_POC & "" <> "" Then FilterCriteria = FilterCriteria & " AND [System POC]='" & System_POC & "'"
    If FilterCriteria = "" Then
            Me.FilterOn = False
    Else
        FilterCriteria = Mid(FilterCriteria, 6) 'REMOVE THE LEADING " AND "
        Me.Filter = FilterCriteria
        Me.FilterOn = True
    End If
End Sub

What would I need to do to make it to where each combo box limits the selectable options to only that data which has passed any of the filters shows in the remaining unused filter boxes.

Example:
With none of the filters selected there are 2100+ available records. I select Doe, John in the Car section and it filters down to 114 records. However all 2100+ records are still available in the System combo box as selectable options despite only 114 (or less) being viable options. I select a system I know his name is associated with and it limits it to 11 records, however 2100+ MAJCOM records are still selectable as filtering options when only 5 are viable.

I guess this is a cascading combo box filtering, but I can't seem to figure out how to do it. I looked through a lot of the forums and though there are some very good tutorials on building cascading combo boxes from multiple tables and from scratch, there doesn't seem to be anything posted on integrating a cascading effect into already established code, and I'm too afraid of breaking the code to experiment. Any help would be appreciated. Thanks.

BTW... I'm a coding "dummy"

[slenish]

Hello MOCaseA,

I have had some experience with this myself. Check out this link and see if it does not help answer your questions.

enjoy :D

http://www.databasedev.co.uk/filter_combo_boxes.html

[MOCaseA]

It looks like it might help... I'll have to tweak it and see what happens... Wish me luck.

[slenish]

Good Luck :D