"ALL" combo box returns no records (1 Viewer)

[dkinnz]

Hi all,
I have a combo box with an "All" option, but I'm having trouble figuring out why it fails to return any records. I'm using a search command button to filter the records. I've attached my database and the specific code I'm using is below. I really, really appreciate any help that can be given!
Cheers,
dkinnz

combo box Row Source:

SELECT DISTINCT qryFilterGroup.[Group Name] FROM qryFilterGroup UNION SELECT "(All)" as [Group Name] From qryFilterGroup ORDER BY qryFilterGroup.[Group Name];

VBA code:

Private Sub cmdSearch_Click()
    Me.subfrmFilter1.Form.RecordSource = "SELECT * FROM qryFilterGroup " & FilterIt
    Me.subfrmFilter1.Requery
End Sub

Private Function FilterIt() As Variant
    Dim varFam As Variant
    If Me.cboGroup > 0 Then
        varFam = "[Group Name] = '" & Me.cboGroup & "'"
    End If
    FilterIt = "Where " & varFam
End Function

 

[unclejoe]

Hi,

Change your code to this.

Private Function FilterIt() As Variant
    Dim varFam As Variant

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

FilterIt = "WHERE " & varFam
End Function

Hi all,
dkinnz
combo box Row Source:

SELECT DISTINCT qryFilterGroup.[Group Name] FROM qryFilterGroup UNION SELECT "(All)" as [Group Name] From qryFilterGroup ORDER BY qryFilterGroup.[Group Name];

VBA code:

Private Sub cmdSearch_Click()
    Me.subfrmFilter1.Form.RecordSource = "SELECT * FROM qryFilterGroup " & FilterIt
    Me.subfrmFilter1.Requery
End Sub

Private Function FilterIt() As Variant
    Dim varFam As Variant
    If Me.cboGroup > 0 Then
        varFam = "[Group Name] = '" & Me.cboGroup & "'"
    End If
    FilterIt = "Where " & varFam
End Function

 

[dkinnz]

Adding "All" to cascading combos and Multiple combo box Filtering

Thank you unclejoe! It works great!
However, I've recently run into a related problem...If anyone else has any insight as well I'd really appreciate it!

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/forums/showthread.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've attached my database if helps anyone
Thank you 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

 

[stopher]

Try...

varFam = "1=1 "
If Me.cboGroup <> "(ALL)" Then
    varFam = varFam & "and [Group Name]='" & Me.cboGroup & "' "
End If

If Me.cboLocation <> "(ALL)" Then
    varFam = varFam & "and [Location]='" & Me.cboLocation & "' "
End If

If Me.cboStatus <> "(ALL)" Then
    varFam = varFam & "and [Status]='" & Me.cboStatus & "'"
End If

FilterIt = "WHERE " & varFam

Note that "WHERE 1=1" deals with the problem where there is no criteria needed. Equally, in the statement "WHERE 1=1 AND [Location] = 'London'", the 1=1 bit becomes irrelevant so doesn't affect the actual result.

Also note the use of single quotations instead of double surround your string variables.

Btw, I think IF...THEN is neater than SELECT CASE where there are only two options imo.

hth
Stopher

 

[dkinnz]

Wow, that is some good stuff. Mile Grazie for your help!!!! I was initially using IF..Then statements, but someone suggested using the Select Case instead. I agree though, IF..Then is much neater.

I'll be sure to post my completed database when I'm finished. I think it may help some people out there.

Many Thanks again stopher!!!

 

[dkinnz]

Stopher,

Not sure if you know why this is happening, but if I select "All" from the Group combo box (cboGroup), the other combo boxes are blank. I'd like to still be able to filter by the other combo boxes if no specific choice is made in the first combo box.

Thanks,
dkinnz

 

[stopher]

I took a look at your latest database upload. It looks like you have only considered how to handle "(ALL)" in the final filter. But you also need to deal with it in each of the after update events. At the moment you are setting the rowsource to:

Select Distinct qryFilterGroup.Location
FROM qryFilterGroup
WHERE qryFilterGroup.[Group Name] = '(ALL)'
ORDER BY qryFilterGroup.Location

Clearly this won't yield any results.
Try this in the cbo_AfterUpdate event...

   If Me.cboGroup = "(ALL)" Then
   cboLocation.RowSource = "Select Distinct qryFilterGroup.Location " & _
            "FROM qryFilterGroup " & _
            "ORDER BY qryFilterGroup.Location;"
   Else
   cboLocation.RowSource = "Select Distinct qryFilterGroup.Location " & _
            "FROM qryFilterGroup " & _
            "WHERE qryFilterGroup.[Group Name] = '" & cboGroup.Value & "' " & _
            "ORDER BY qryFilterGroup.Location;"
   End If

The above could probably be tidied up but at least you can see the solution i.e. I removed the WHERE when ALL is selected

You'll need to do the same thing in the cboLocation_AfterUpdate event.

hth
Stopher

 

[dkinnz]

Cool. Thanks Stopher. I really appreciate your help with this.

Cheers,
dkinnz