-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules