dropdown lists not progressively filtered in MS Access 2003 filter by form

[Mike O.]

MS Access 2003 "filter by form" has drop down lists that allow the
user to select values for each field to filter by. However, once some
values are selected,the remaining dropdown lists remain the same and
aren't progressively filtered to reflect the selections already made
in other fields. This can be very tedious when filtering a large
datasheet. Unlike Access, the MS Excel autofilter has automatic
progressive filtering of value dropdown lists, however, Excel's
size limitation makes it useless for working with a large database.

Does anyone know of a way to get the MS Access filter by form dropdown
lists to become progressively filtered to reflect filter choices already made?

Thanks,

Mike O.

 

[Allen Browne]

The Filter By Form is not powerful enough to do what you want.

Use a different interface, e.g. unbound combos in the Form Header section.
You can then program them so the AfterUpdate of the first sets the RowSource
of the second, and so on.

It's dead easy to build a filter string from the non-blank boxes in the form
header section, and assign it to the Filter of the form (remembing to set
FilerOn as well).