A useful fashion to code multiple filters on an Access form

Programming an form filter in Ms Access is simple enough. Assume you have a combo box with two values – TRUE or FALSE – on some sort of boolean control. You want the filter to set this value to TRUE when the combo box reads TRUE, and vice versa. Usually you would simply put some code in the afterUpdate() event of the combo box which would read something like this:

sub cbxSomeBox_AfterUpdate()
dim strFilter as string
if me.cbxSomeBox.value <> “” then
strFilter = me.cbxSomeBox.value
me.filter = strFilter
me.filterOn = true
end if
end sub

This works fine if you simply have one custom filter control on a form. Occasionally you may want to have multiple custom filter controls on a form, and then it gets slightly more complex. Using code similar to the preceding example will not preserve previous filtering choices – i.e you filter on one control, then select a new filter control and select a choice, the new filter control will filter the entire recordset and not the results of the previous filtered recordset (apologies for this explanation – it may sound rather confusing)

However, I’ve come across a useful solution to this dilemma that I wish to share with you.

This article at thescripts.com contains a sub-procedure called checkFilter() which, if you download the example mdb linked from this page, and have a quick examination of the code, you’ll realise that the checkFilter procedure is called during the ‘after update’ events of several custom filter controls based on a form. What this does is every time the user ‘does’ something with one of these custom filters, the procedure rebuilds the entire filter, and applies it to the form. This turns out to be REALLY useful.