Applying ComboBox filters Sequentially

[SueHopson]

Hi All,

I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On the microsoft forum I found multiple examples of code from HansV and simliar code from NeoPa here that I am trying to understand and adapt.

The code below is works great for me so long as both independant combo boxes have selections, but despite everything I've read, it doesn't seem to leave all the values for the field when the one or either of the combo boxes is left blank.

Private Sub FilterMe()
    Dim strWhere As String
 
    If Not IsNull(Me.cmbVendorSelect) Then
        strWhere = strWhere & " AND [PartVendor]='" & Me.cmbVendorSelect & "'"
        'how is this handled if the cbo is left blank? trying to learn...
    End If
 
    If Not IsNull(Me.cmbTypeSelect) Then
        strWhere = strWhere & " AND [PartType]='" & Me.cmbTypeSelect & "'"
    End If
 
    If strWhere = "" Then
        Me.Filter = ""
        Me.FilterOn = False
        'code seems to fail here when both comboboxes are blank, or am I    misunderstanding the code?
    Else
        Me.Filter = Mid(strWhere, 6)
        Me.FilterOn = True
    End If
End Sub

Where I am having trouble is with the AND logic of the script, when a combo box is left blank (i.e they might just want to filter by Vendor or Part Type only). I have run the individual codes directly from the combo boxes using AFTER UPDATE, but am having the same trouble when I call the procedure.

As always, any help is greatly appreciated.

[NeoPa]

Hi Sue.

I have a couple of items out there that talk about filtering so I'm not sure which is the one you're referring to in your question, but the code looks like something I'd write &, as of this point in time when I'm just starting to look at it, I see no problem with it. Let me see if I can dig further.

No. I can't see why your code would fail. It looks fine to me. If you continue to find your system fails it would be helpful to see the results of executing the following code in your Immediate Pane :

?Me.cmbVendorSelect;"|";Me.cmbTypeSelect;"|";strWhere;"|";Me.Filter;"|";Me.FilterOn;

This should be run immediately after noticing the attempt has failed. Please Copy & Paste the results of that from there into your next post.

Separately, you have two questions embedded within your code so I'll try to answer them here :

1. 'how is this handled if the cbo is left blank? trying to learn...
   This is on line #6. If it is blank then line #4 ensures this code isn't run. An unbound ComboBox with no selection will return Null.
2. 'code seems to fail here when both comboboxes are blank, or am I misunderstanding the code?
   I would say it's a fair guess you are misunderstanding the code, but I'm not sure how that would have any impact when it fails as that would be an act of observation.
   Certainly I see no problem with the code. It sets the Filter Property to an empty string and also tells the Form not to apply any filter. I would expect this is what you want when neither ComboBox has been selected from.

[SueHopson]

Ok, so.... I just moved the tables and form into a blank (test) database, setting the tables as local and reworked my button code from scratch. I'm fairly certain I can laugh at and blame myself for this one...

It's amazing what a change in logic it makes when you realize that = "" should actually be = Null in your reset code...

Private Sub btnResetFilters_Click()Me.Filter = ""Me.FilterOn = FalseMe.cmbTypeSelect = NullMe.cmbVendorSelect = NullEnd Sub

It now works fine. I then copied that form back into my main database, with linked tables, and it's now working properly too. The worst injuries to one's ego are always self-inflicted LOL

[NeoPa]

Hi Sue.

We all manage to slip over things from time-to-time so you needn't be too hard on yourself for this.

Actually, this is a very common area for confusion. When is a value a ZLS (Zero-Length String, Empty string or "") and when is it a Null. Personally I'm very conscious of the need to determine what I'm working with nowadays, hence I can usually answer with some certainty. Others prefer the belt & braces (You may refer to them as suspenders.) approach where you do the tests in a way that handles both in the same way - especially for strings but for numbers too sometimes.

So, instead of using If Not IsNull(Me.cmbVendorSelect) Then, you could use either If Nz(Me.cmbVendorSelect, "") <> "" Then or even If Me.cmbVendorSelect > "" Then. Either of these replacements would yield the same result regardless of whether the item were a Null or a ZLS. Many choose to use such a format routinely and they are thus protected from ever having such issues bite them.

I prefer to be bitten early on and get the code to work as I require, but that doesn't mean you should. It may make sense to use such an approach & reap shorter development times in your work.