Combo Box Filter

Access 2000 - 2003

(Also OK for 2007 and later)

Note: Combo Boxes can also be referred to as drop down lists.

If you see a (?) I have included a help box, at the bottom of the page, to refer to if you need a bit more help with this step. Not everyone does, so in order to keep the instructions concise and to help those that are still learning, I thought this was a reasonable compromise.

Lets Begin your Combo Box Filter

Set up your two combo boxes. Make sure you choose a field in both combo boxes that can be used to link and filter each of the combo boxes. As an example, I have included a graphic below, that depicts two related tables. Table1 and Table2. Table1 has the list of Departments in a hospital and Table2 has a list of units.

Each of the units is linked to the Primary key in Table 1 as illustrated below.

To set up the Combo Box Filter:

• Open the properties for the first combo box (?)
• Choose the 'Other' tab take a note of the 'name' of the Combo Box, you will be using this later.
• Open the properties for the second combo box (?)
• Click on the 'Data' tab
• Place your cursor next to the 'Row Source' property
• Then click on the '...'

This opens the query related to the combo box.

In the criteria row under the Table1FK field (Column), type the following:

[Forms]![frmComboFilter]![Combo0] As illustrated below

Where frmComboFilter is the name of the form containing the combo boxes.

And, Combo0 is the name of the first combo box you have just made.

You will need to change these names to match the name of your form and combo box.

Close and save the query. Then:

• Open the properties window (?) for the first combo box
• Select the 'event' tab
• Place your cursor next to 'After Update'
• Click the '...' button and select 'Macro Builder'
• and click 'OK'
• Give your Macro a name i.e. ComboFilter
• Click 'OK'

The Macro Window will open.

In the Action column, select 'Requery' and, down the bottom in the box next to 'Control Name', type:

Combo2

Where Combo2 is the name of your subcategory combo box (the combo box you wish to filter).

Once again change Combo2 to the name of your combo box.

Close and save the Macro.

(This Macro will rerun the query each time the value changes in the first combo box.)

Try it out and see if the Combo Box filter works for you.

If you are a visual person, then you may find the video below useful: