This article gives a brief overview of how to implement a filter on a datasheet that updates automatically as you enter information into various search fields. We’ll use eateries as examples – restaurants stored with the Location, Category of food a brief description, and a flag that we can use to mark something as a favorite.

[wpdm_package id=’734′]

There’s a few examples of “search as you type” filtering out there, which is included here also, but I don’t recall many that demonstrate how to build a dynamic filter based on a multi-value list box, so I’ve included a few in here as well.

There’s four fields we’ll be able to search on:

  • Description: type characters into the search box and it will filter the list on what you’re typing, as you’re typing
  • Flagged Entries Only: a simple boolean (yes/no) field that will show only items that have been flagged
  • Locations and Categories: two multi-select list boxes that allow us to show any combination of various locations and categories

In this demo, any combination of the criteria can be used at any given time. There’s no Submit or Reset buttons, as everything happens as soon as you type or click.

Overview of Code

Here’s how it’s going to work: for each of the fields which we’ll be filtering against, we’ll put a module-level variable in the main form’s module (all the code in this demo is in the main form’s module). Providing module-level variables allows all of the procedures in this module read them, which is pretty handy, as we’ll be accessing them from a few places in the code later. Module-level variables go after the Option statements, but before any procedures.

Once those are set up, we’ll create a procedure that will actually build the filter string and apply it to the subform. This will be called any time a control value changes and will update the filter accordingly.

The last thing we’ll need is to make sure that when we change a value in a control, it changes the module-level variables accordingly. The procedure to build the filter string will be reading these module-level variables to determine what filter string it needs, so we’ll have to make sure they’re kept updated. We can do this by selecting the appropriate control event (OnChange, Click or AfterUpdate), and updating the variables as the control is updated.

There’s a few more details as we’ll see in a bit, but this is the general setup: a control changes, updates a variable accordingly, and tells the code to build a new filter based on the current state of the variables.

Setting up the Basics

First, let’s define those module level variables. By convention, we usually prefix module level variables with an “m_” (meaning ‘member’, for trivia). While you’re at it, this is a great time to make sure Option Explicit is set. We’ll also put the husk of the BuildFilter procedure in there also, though we won’t put code in there until a little later…

Now we can go ahead and set up the event procedures for each of the controls. For the Search textbox, we’ll use the OnChange event, because we want to refilter every time we type a new character. For the checkbox, we’ll use the Click event (because AfterUpdate sounds ideal, but doesn’t play well here), and for the Locations and Categories list boxes, we’ll use the AfterUpdate event, which does work correctly on these controls.

The code for the two list boxes will be a little tricky, so we’ll save them for last. For now, we’ll update the corresponding variable, then call the BuildFilter procedure:

Building a Filter on a Multi-Select Listbox

As promised, the multi-select filters will be a little more tricky. When we build the filter, we’re going to use an IN() clause, which allows for a list of items separated by a comma, and acts is if we’re saying this OR this OR this OR this.

We’ll have to store the string in the variable complete with the commas. To do this, we’ll loop through each item in the listbox, see if it’s selected, and if so, we’ll toss a comma as well as that selected value into the string. The string will build as we work through the loop, each time putting a new comma before the next item that’s entered. You’ll notice though, that when the string is done being build, the first two characters are going to be a comma and a space, which we don’t really want. We’ll clean those out after:

The code for the Categories list is nearly identical, except the control and variable name.

Building the Filter

Now that all of our events are set up and updating the variables and calling the BuildFilter procedure, we start setting up the BuildFilter sub to actually do something for us.

The BuildFilter sub will take a look at each variable, and if it finds something worthwhile, it’ll add it on to the filter string it is building. For each item it adds, it takes the existing string (if any), puts an opening parentheses at the beginning, followed by the existing string, followed by an AND, then the new piece of criteria, and finally a closing parentheses. By doing this, we allow for any reasonable amount of criteria to be added to the filter string.

You may notice a call to an AppendFilterItem() procedure in there… because we need to first check and see if there’s existing criteria (so we know if we need an AND in there or not), the code to check that would have to be included four times, one for each piece of criteria that we might have (well, just three times, because the first one won’t have pre-existing criteria).

In the spirit of code re-use, we’ll move this logic out of the BuildFilter procedure into a little function that returns the modified string for us:

Wrapping Up

At this point, we should have a dynamic filter that filters the results basic on any changes made to either of the four controls. There’s one last thing we’ll need, which is to make sure that when the form is opened, the variables in the module are matched up to any default values of the controls. Also, we’ll run the BuildFilter procedure after the form is initially loaded, just to make sure we open with an accurate list that incorporates any default values. To do so, put some code in the Form_Load() event which sets the variables and calls the builder.

In this example, all the default values are the same as the default defaults… (default defaults? yea)

And there you have it… if it works as well on your computer as it does on mine, you should be happy!

(the demo file can be downloaded from the top of this post, in case you missed it on your first run through)