MS Access Combo Box

If you are an existing Access user, you will now how useful COMBO boxes are. They can be used to ensure valid data is selected on data entry, but even more useful is when they are used to select an item for filtering the data in a form or table view.
But what do you do if you have several COMBO boxes and your users can select from either, one or more, or none?

Then you need a way to either select an option from the COMBO box or not, and still have your data filtered.

Here is where we need to add an option “(ALL)”.

Let us see how this can be done.

Image 02

On your form, click on the option to – Design View
Select your COMBO box.
View the properties for the object.
In the properties navigate down to the – Row Source.
This should be displayed as “Select …..” etc

When you click on this text, two buttons now appear to the tight of the option- A small downwards pointing triangle and
the ellipsis – (Three dots). Click on the Ellipsis to enter the design view for the COMBO.
The columns that make up the COMBO should now be displayed.

(See Image 02 – On left)

NOW select the option to view the COMBO IN SQL view.

(See Image 03 – On Right)

Image 03

Image 04

The SQL select statement behind the COMBO should be somewhat similar to this example. (See Image 04 on Left)
After the select from section, but before any – ORDER BY – insert a few blank lines ready to type.
FOR the “ALL” option the COMBO needs to be a UNION of data selected from a table and data that is static and typed in as part of the SQL query.

If the Primary key column is a numeric (Autonumber) as is usually the case then this will be replaced by a 0 (Zero).
For each column in the COMBO, insert either “(ALL)” for text fields or 0 (zero) for numeric fields.
Finish the static select statement by adding the “FROM tbl…..” This will be the same table as for the data in the first section of the SQL statement.

(See Sample Image 05 on Right)

Image 05

Image 06

When run in design view the “(ALL)” should now appear as the first option in the list.

(See Image 06 On left)

Next we will look at the logic needed to do something with the 0 (Zero) if that has been selected or the regular data if that is what has been selected.

Image 07

Pros of Using this Method.

Allows for an (All) option as well as the individual

data items from the selected table.

Using brackets positions the (ALL) to the top of an alphabetically sorted list.

Cons of using This Method.

Having manually edited the SQL statement behind the COMBO box, it is no longer possible to use the visual design wizard. Any future editing also has to be done manually.