Help w/ Multiple Filters on Combo Boxes

[dmuroff]

Hi,

I have multiple combo boxes at the top of a form which are used to filter records in the details section of the form.

I have a procedure called FilterCommand which is called everytime one of the combo boxes is updated (afterupdate event) Here is the procedure:

Private Sub FilterCommand()
Dim strFilter As String
strFilter = "1 = 1"
    If Len(cboInternalCode) > 0 Then
        strFilter = strFilter & " AND InternalCode='" & cboInternalCode & "'"
    End If
    If Len(cboBallastType) > 0 Then
        strFilter = strFilter & " AND ballasttype = " & cboBallastType
    End If
    If Len(cboInput) > 0 Then
        strFilter = strFilter & " AND InputWatts = " & cboInput
    End If
    If Len(cboType) > 0 Then
        strFilter = strFilter & " AND Type =" & cboType
    End If
    If Len(cboLampType) > 0 Then
        strFilter = strFilter & " AND lamptype = '" & cboLampType & "'"
    End If
    If Len(cboBase) > 0 Then
         strFilter = strFilter & " AND base = '" & cboBase & "'"
    End If
    If Len(cboWatts) > 0 Then
        strFilter = strFilter & " AND watts = '" & cboWatts & "'"
    End If
    If Len(cboVolts) > 0 Then
        strFilter = strFilter & " AND volts = '" & cboVolts & "'"
    End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

Now the problem is that lets say a user selects a value from cboWatts which returns 5 records. Now the user selects a value from cboVolts (making the filter cbovolts and cboWatts) If there are no records which meet this filter the cboVolts combobox displays nothing (It appears to display nothing but in fact in the text in the box is just invisible).

Is there some way to improve how I am filtering these records? or modify my code so the value of the box will not be invisible if the filter does not return any records?
Any suggestions would be greatly appreciated. Thanks!

 

[TooGoon]

Not having read AceMan's code, the way I was thinking of it working:
3 combo boxes: cbo1, cbo2, cbo3

Click on cbo1 (Code filters row source to display all available options: as nothing is selected in the other cbos, every option is displayed). Select option 2.

Click on cbo2 (Code filters row source to display options which appear in records which contain option 2). Select option 5.

Click on cbo3 (Code filters row source to display options which appear in records which contain option 2 and option 5). Select option 29.

Click on cbo2 (Code filters row source to display options which appear in records which contain option 2 and option 29).

So only the row source of the currently selected combo box would be modified. You wouldn't need to worry about the others.

 

[hkaing79]

Question: Does Me.Filter work on unbound forms?

AceMan: I like that Choose command. Instead of checking the WHERE clause in each loop, why not just check it at the end?

Loop through your combobox then:

If Not IsNull(strFilter) Then
  strFilter = " WHERE " & left(strFilter, len(strFilter)-5)
End If

Me.RecordSource = strSQL & strFilter

dmuroff: What exactly are you filtering (entire form, combobox, listbox)? Is the form itself bound to a table? I've never experienced a combobox going blank or invisible. Perhaps you have some other piece of code that is changing the rowsource property of the combobox?

 

[dmuroff]

Hi hkaing79,

I am filtering a form which is directly bound to a table.

What happens is that one 2 combo boxes are chosen for 'filtering' and they don't return any records, the last combo box loses focus and goes blank. Once it gets focus again the value displays as it should.

 

[hkaing79]

I used two combobox to filter a table. When no records matched it still displays the two choices. I used bound form with unbound combobox.

Here's my code:

Private Sub ftr1_AfterUpdate()
    ftrTurnOn
End Sub

Private Sub ftr2_AfterUpdate()
    ftrTurnOn
End Sub

Private Sub ftrTurnOn()
    Dim strFilter
    
    strFilter = ""
    
    If Not IsNull(ftr1) Then
        strFilter = strFilter & "field1 = '" & ftr1 & "'" & " AND "
    End If
    
    If Not IsNull(ftr2) Then
        strFilter = strFilter & "field2 = '" & ftr2 & "'" & " AND "
    End If
    
    If Not IsNull(strFilter) Then
        strFilter = Left(strFilter, Len(strFilter) - 5)
        
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

Perhaps the problem lies in some other part of your coding? How are you populating your comboboxes?

I guess another approach would be to loop through all your combobox and use the .setfocus?

 

[dmuroff]

There practically is no code on this form that should be causing this problem.

The combo boxes are all unbound and I set the rowsource from columns in the main products tables.

I have tried setting the focus to the specific combobox after it is updated but it still loses focus.

It's really not a huge problem mainly because users will most likely know what they are searching for but it's just nice to get everyting working properly.

 

[hkaing79]

Is it possible that because your table is bound to the form, that when you filter your table, that in turn filters your combobox? So if you get 0 results, then your combobox also filtered out all the records leaving you with 0 choices?

I guess that's not possible if you're able to see the combobox after it gets focus.

If you haven't made any changes to the form itself, you might want to change the "Limit to List" = Yes. By default, it's set to No.

That's a really strange problem. Sorry I couldn't help. Good luck.