Filtering a subform

[JvdP]

About me
I'm what you would call a n00b and my skills in Access are rather simple. Nevertheless, I've taken lots of time to research this matter and I can't seem to find a problem to my solution, even though I feel like it is a rather simple one. It took me quite some hours to get to this point, but I'm most certainly stuck so I could use some expert help on this one!

Introduction


With a form-subform I am trying to ’filter’ products in a table. In theory, this lets me select a product that has the properties that I filter for. The name of the products are ‘Type-Height:Depth’ and I want to filter on these three criterias. The form contains list control boxes that are populated automatically with a query that gets all possible values of Type, Height and Depth for all products in the database. In case a new type of product is added to the database, it will automatically appear in the list boxes.
Filtering of the subform is done by linking the form-subform. To be clear, I have filled in the master fields and the child fields under the Data tab for the subform:

Link Master Fields: ListBoxType; ListBoxHeight; ListBoxDepth
Link Child Fields: Type; Height; Depth

The filterform works surprisingly well up to this point. But I want to be able to do more. For example, I want the filter to discard ‘unspecified’ criteria, because sometimes you simply want to filter product type, but see all possible dimensions. I tried to accomplish this for each criteria by setting the list box value to Null with a checkbox:

 Private Sub CheckBoxType_Click()
  If CheckBoxType Then
    Me.ListBoxType.Enabled = True
  Else
    Me.ListBoxType = Null
    Me.ListBoxType.Enabled = False
  End If
End Sub

The Problem
This VBA works very well in case I want to filter on Type, which is only 1 character. But in case I want to use this VBA on Height or Depth (both 2 characters) it gets troublesome. When the VBA sets the ListBoxHeight to Null, the filter returns no products at all though it should actually return all records since the Height is unfiltered.

What I already tried
So I tried many different things using my n00b-skills and the most successful so far aws to populate the list manually. So I entered the values as 10;20;30;”” where the last value is, as you can see, an empty string. So, when I am using the filter form and select the empty value in the list, it works! So I figured I could use this when I code the checkbox:

If CheckBoxType Then
    Me.ListBoxType.Enabled = True
  Else
    Me.ListBoxType = “”
    Me.ListBoxType.Enabled = False
  End If
End Sub

But somehow this gives the same problem again, even though I can see that the VBA selects the empty value when I uncheck the box. The other obvious downside of this way is that I have to populate the values in the lists manually, this defeats the purpose of my form.

Another Suggestion
From the Access help documents:

The fields or controls you use to set these properties don't need to have the same names, but they must contain the same kind of data and have the same or a compatible data type and field size. For example, an AutoNumber field is compatible with a Number field if the FieldSize property for the Number field is set to Long Integer.

So I have a feeling that I have to set the kind of data passed by the VBA equal to the type of data in the table. I'm not really sure how though.

The Question(s)
So does anyone have an idea to filter my subform according to my criteria? Why does it stop filtering when it hits a criteria of more than 1 character? Or maybe even a more drastical question: does anybody suggest a totally different approach to filtering/searching a table with a form?

[JvdP]

Did I scare everyone away with my long text? The problem is rather simple though, there must be somebody who can help!

Also, I don't neccesarily have to solve it. I could also make a whole new way of filtering a table with a form it's just that I don't know how.

[JvdP]

I was actually quite amazed that a big forum like this results in 0 response to my (simple?) question. Nevertheless, I worked around my problem by designing a dynamical query. I'm still wondering why the filter function didn't do it's work properly.