How do I add an <All> selection to my combo box drop-down?

A common use of the combo box is to set a criteria for a search, or to specify the records displayed in another form/subform or report. You may want to give your users the option of selecting all choices as the criteria. Here's one way to do it:

In this example I'm assuming a wizard-created combo based on a table named "tblMyTable" with fields "RecID" and "CompanyName" as the Row Source. The first column is the bound column and is hidden. After using the wizard to create the combo, the SQL of the combo will look something like this:


Now to add an <All> choice to this, we'll change the combo's SQL to a union query:


To directly edit the combo's SQL, just right-click in the Row Source field of the combo's property sheet and select "Zoom" from the pop-up menu.

The combo drop-down will now display a list of companies, with <All> at the top of the list. You can now use the <All> selection in your code just as you would any other selection. Specifying an asterisk as the bound value in this example will make it useful for queries in which you would use the "Like" operator.

Some things to remember:
1) There must be the same number of fields on both sides of the UNION statement. In this example there are 2 fields on each side of the UNION clause.
2) Remember which column is the bound column, as that is most often the value you will use for processing purposes. The order of the field items on the right side of the union is significant. In this example, the first column is the bound column; hence the asterisk as the value of the first column on the right side in the example.
3) It may seem strange to include the table name in the right-hand side of the UNION query, as you're adding values instead of field names. Don't worry about that. A table name must be in the SQL, but in this case the table name doesn't matter precisely because you're adding values, not field names. An alternative method is to create a single-record table for the right side of the union query, with fields to hold the * and <All> values. In that case you would specify the field names and table name on the right side of the UNION clause instead of the actual values.

Thanks to ESquared for comments and clarification.

**Addendum**

If you are using your combo value as the criteria in a query, i.e.:
...and you don't wish to use the Like operator as suggested above, you can change the values on the right-hand side of the UNION clause in the combo's rowsource to "<All>", "<All>" - then change the criteria of your query to:
(replace object names in blue with correct names from YOUR form)

With grateful acknowledgment to the expertise of PHV and TheAceMan1.

HTH,

Ken S.