Adding "All" option to Cascading Combos and Multiple combo box Filtering (1 Viewer)

[dkinnz]

Hi all,
I originally posted the following in the Forms section, but I think it belongs here.

My main goal in the project that I'm working on is to have multiple cascading combo boxes to filter the records. Through searching this forum, I was able to put together some cascading combos, but I can't figure out how to add the "All" option to the dependent combo boxes.
Here's my code for one of the cascading combos...how do you add an "All" to it?

Private Sub cboGroup_AfterUpdate()

   On Error Resume Next
   cboLocation.RowSource = "Select Distinct qryFilterGroup.Location " & _
            "FROM qryFilterGroup " & _
            "WHERE qryFilterGroup.[Group Name] = '" & cboGroup.Value & "' " & _
            "ORDER BY qryFilterGroup.Location;"

'In SQL it should look like this:
'SELECT DISTINCT qryFilterGroup.Location FROM qryFilterGroup UNION SELECT "(All)" as Location From qryFilterGroup ORDER BY qryFilterGroup.Location;

End Sub

The other issue I'm having is how to use an "AND" between Case Selects to show records based on each selected combo box value.
I attempted to incorporate the idea behind the example database found here:
http://www.access-programmers.co.uk/...ad.php?t=99353

Not sure if I'm on the right track, but below is my code so far which isn't working :-(

Private Function FilterIt() As Variant
    Dim varFam As Variant
    
'Multiple Combo Box Filtering

'User selects specific Location or "All" to return all Groups
Select Case Me.cboGroup
    Case "(All)"
    varFam = "[Group Name] LIKE '*'"
    Case Else
    varFam = "[Group Name] = " & Me.cboGroup & " AND "
End Select

'User selects specific Location or "All" to return all Locations
Select Case Me.cboLocation
    Case "(All)"
   varFam = varFam & "[Location] LIKE '*'"
    Case Else
    varFam = varFam & "[Location] = " & Me.cboLocation & " AND "
End Select

'User selects specific Status or "All" to return all Status
Select Case Me.cboStatus
    Case "(All)"
    varFam = varFam & "[Status] LIKE '*'"
    Case Else
    varFam = varFam & "[Status] = '" & Me.cboStatus & "'"
End Select

FilterIt = "WHERE " & varFam
End Function

I really appreciate the help given in this Forum.
I've attached my database if it helps anyone else.
Many Thanks if you can help!

 

[dkinnz]

I was able to figure out how to add in the "AND" to create multiple combo box filter conditions (see code below).
But, I'm still having trouble with inserting the "All" option into the cascading combo boxes to return all records if a specific combo box value is not selected. If you have any suggestions I could really use your help!!!

Cheers,
dkinnz

Here's my code for using multiple combo boxes to filter records...

Select Case Me.cboGroup
    Case "(All)"
    varFam = varFam & "[Group Name] LIKE '*'"
    Case Else
    varFam = varFam & "([Group Name] = """ & Me.cboGroup & """) AND "
End Select

'User selects specific Location or "All" to return all Locations
Select Case Me.cboLocation
    Case "(All)"
   varFam = varFam & "[Location] LIKE '*'"
    Case Else
    varFam = varFam & "([Location] = """ & Me.cboLocation & """) AND "
End Select

'User selects specific Status or "All" to return all Status
Select Case Me.cboStatus
    Case "(All)"
    varFam = varFam & "[Status] LIKE '*'"
    Case Else
    varFam = varFam & "([Status] = """ & Me.cboStatus & """)"
End Select

FilterIt = "WHERE " & varFam