filters on a form

Hello,

Has anyone been able to deploy comboboxes as filters on a form satisfactorily.

I have a form with several fields and am trying to use comboboxes as filter. The ControlSources for the comboboxes are the field from the table.

It is easy with one filter. I just set the filter and then filter on

When there are two filters it gets more difficult. I can filter the form by checking if the filter is empty and if it is setting it to the combobox selection and then turning the filter on and if it isnt empty setting the filter to the existing filter "AND" the new filter.

But I run into two problems :-

1) After the first filter I cant get the other comboboxes to only hold the values of the filtered dataset

2) If I then re-filter on the same field it gives no results as it is trying to look at the first filter "AND" the second one which gives a resulting empty dataset

Any ideas???

Cheers
Phil

I'll make some assumptions here:

Let's say that you have 2 comboboxes to filter on Category and Region.

When you select a Category, the record source for the Region combobox needs to be amended, so that you only see Regions within the selected Category.

At this point, unless you have a button specifically to apply the filter, the form data should be filtered for the selected Category. You need to build the filter every time that it's applied, testing all of the filter comboboxes. So you will have something like:

[vba]
If nz(me.cboFilterCategory,"") <> "" Then
strFilter = "[Category] = " & Chr(34) & me.cboFilterCategory & Chr(34)
End If

If nz(me.cboFilterRegion,"") <> "" Then
strFilter = strFilter & " AND [Region] = " & Chr(34) & me.cboFilterRegion & Chr(34)
End If

If Left(strFilter,5) = " AND " Then
strFilter = Mid(strFilter, 6)
End If

Me.Filter = strFilter
Me.FilterOn = True
[/vba]