Adding an "ALL" option to a combo-box

We're all familiar with using a combo-box (also known as a drop-down list) to select data from a pre-defined or data-driven list of values. It's also quite common to use a combo-box to select a data value which can then be used in some context, for example selecting a value which is then used to filter a form or report. When you're using a combo-box in this way, it can often be good to add an “ALL” option at the top of the list, to show that you want everything... but how do you do this?

Fortunately it's a piece of cake, but there are different methods depending on what the row-source of the combo-box is. Broadly speaking, there are four generic types of combo-box row-source:

1. The Value List - the simplest row-source, with data values typed in and separated by semi-colons, so a row-source of North;South;East;West will give the drop-down list four simple values.
2. The Simple Bind - if a combo is on a form that's bound to a table, then the row-source of that combo can just be a table field name; as long as that field has a lookup set up at a table level, that lookup will cascade to the form and populate the combo-box drop-down list.
3. The Simple SELECT - a combo-box can have a simple SQL statement as the row-source, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID; would produce a drop-down list of Region names in alphabetical order.
4. The Complex SELECT - often a combo-box will use a SQL statement that returns more than one field, typically a numeric ID field and one or more narrative text fields. The ID field can then be given a column width of zero in the combo-box, allowing the user to see and highlight an intuitive and meaningful text value but to actually be selecting a numeric ID value. Such a row-source might look something like SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so we're happy with the four basic types of combo-box row-source as they might appear on a form control. Before we consider how to change each type to add an "ALL" option, let me first explain the general principle of what we're going to do. We're starting out with a combo-box from which we can select a value and then use that value in some way. For example, you might have a button on your form that opens a report but filters the data therein based on the value selected in your combo-box - in this case, the OnClick event of that button might include some VBA along the lines of DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion where Me.cboRegion represents the value of the combo-box. But to add an “ALL” option, we're going to have to change our thinking because what we're actually going to be adding is a “*” wildcard character option. So, in our subsequent use of the combo-box value, instead of using an Equals operator (=) we're going to have to think about using a LIKE operator. In other words, the previous example will have to change to something like DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE " & Me.cboRegion

That's easy enough, isn't it? The important thing to remember is this works because the SQL WHERE constraint of “LIKE '*'” returns everything that isn't Null.

Okay, enough theory - here are some examples of how to change each of the four combo-box row-source types to add that “ALL” option.

Row-source	Without “ALL”	With “ALL”
Value List	North;South;East;West	*;ALL;North;North;South;South;East;East;West;West
Simple Bind	The cascaded table field's lookup, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;	A UNION statement, e.g. SELECT "*" AS RegionID, "ALL" AS RegionName, 0 AS SortOrder FROM tblRegion UNION SELECT RegionID, RegionName, 1 FROM tblRegion ORDER BY SortOrder, RegionName;
Simple SELECT	A SELECT statement with one output, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID;
Complex SELECT	A SELECT statement with more than one output, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so you probably have a few questions now, such as:

Q. Why does my combo-box now show an asterisk or a number, instead of my nice intuitive narrative text?

A. Unless you tell it otherwise, the combo-box will still display the same number of columns, with the same widths, as before you made the changes outlined above. To restore the nice formatting, you need to do all the following:

• increase the combo-box's Column Count property - in the example above, the Column Count property would be 3 (2 for the Value List example), as the SQL statement returns three fields;
• add a column width of 0cm to the start of the Column Widths property (so the */ID field is hidden) - in the example above the Column Widths property would be 0cm;2.54cm;0cm (0cm;2.54cm for the Value List example) as you wouldn't want to see the third column either;
• ensure that the Bound Column property is set to 1.

Q. What's that “SortOrder” value all about?

A. You want your “ALL” to appear at the top of your drop-down list, right? But what happens when sorting alphabetically doesn't put “ALL” at the top? For example, you might have a list of countries, in which you'd find that Albania came above “ALL” in your combo-box. By adding a simple numeric flag called SortOrder, this can be avoided: SortOrder has a value of 0 for your “ALL” option and 1 for everything else. SortOrder can then be used as the first item in your row-source SQL statement's ORDER BY clause, to ensure that “ALL” comes first - subsequent items can then be added to the ORDER BY clause to sort the remaining data items however you like.

Q. Hows does it work again?

A. Time to revisit the example. Let's say you have a combo-box called cboRegion on a form; previously it had a simple SELECT style row-source. You want to use this control to restrict the data included in a report - the report is opened by clicking a button on the same form. Previously, the button's OnClick event would have contained something like this:

DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion 

If you'd selected a RegionID of 1 from your combo-box, this would have parsed as

DoCmd.OpenReport "rptSalesman", , , "RegionID = 1" 

Now you have something like:

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '" & Me.cboRegion & "'" 

If you select “ALL” from your combo-box, this will now parse as

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '*'" 

And, as we learnt earlier, "LIKE '*'” returns everything, and so gives you your “ALL” option. Hooray!

Q. I still don't get it - can't I download an example database?

A. Yes you can (I'm good to you, aren't I?). There's no difference in the code at all but, due to popular demand, I've created Access 97, 2000 and 2002 versions of a database to illustrate the example we've talked about. They have two tables (tblRegion and tblSalesman), one form (misleadingly entitled frmSalesman) and one report (rptSalesman). Take a look at the properties of cboRegion and the OnClick event of cmdRpt in frmSalesman - you can surely work the rest out for yourself!

Free download samples: Access 2002 and above | Access 2000 | Access 97 - you'll need unzipping software for these downloads, such as WinZip or 7-Zip.