Adding an (All) item to an Unbound Combo Box

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Adding an (All) Item to an Unbound Combo Box

by Susan Sales Harkins

Application: Access 97/2000

One of the purposes of combo boxes is to limit choices, but they can have the unfortunate consequence of being a bit inflexible. Specifically, a combo box limits you to selecting just one item from its list. There are times when you may want to work with all of the items in a list. A list box control can be configured to allow this, but there's no obvious indication to the user of this capability. Fortunately, you can add functionality and simplify the work required from your end users by adding an item choice to a combo or list box list that explicitly indicates how a user can select all of the list items. In this article, we'll show you a query technique that you can use to add any text you define to a combo box. In addition to that, our technique will ensure that the custom choice is obviously noticeable to users. Although we'll demonstrate the technique with a combo box, you could also apply it to list box controls.

The technique

To create our custom list item, we'll use a UNION query as the row source for a combo box. The query will combine the existing values from a table field with the custom item (All). For an example of how you might use such functionality, suppose you want to query employees by their city of residence. One solution is to display a list of cities in a simple parameter form, similar to the one shown in Figure A. Users can quickly return a list of employees in a particular city by selecting an item from the list. As the control stands, there's no way to allow users to return all of the employees in the database, regardless of city. However, a choice of (All) would clearly indicate that selecting the item would return data for all employees.

Create a simple example

To illustrate our technique, we'll create the form and control shown in Figure A, which is based on the Employees table in the Northwind database that comes with Access. Note that we'll simply show you how to customize the control's list--in our example application we won't perform any actions upon making a selection from the list. To start, launch the Northwind database and open a blank form by choosing Form from the Insert menu. Then, click OK in the New Form dialog box. Resize the form, using Figure A as a guide. To create the control, ensure that the Control Wizards button is not selected, add a combo box to the form and delete the associated label. If you do inadvertently launch the wizard, just click Cancel in the wizard's first dialog box.

Figure A: This example parameter form displays cities in a combo box.

We'll use a SQL statement to set the control's data source. First, if necessary, open the Properties sheet for the combo box. Next, enter the following expression as the Row Source property:

  SELECT DISTINCT City FROM Employees;

To see the results, save the form as frmCity and click the View button on the Form Design toolbar. Click on the form's dropdown arrow to view the list that displays the contents of the City field from the Employees table, as shown in Figure A. The DISTINCT keyword returns a unique list by omitting any entries that are duplicated in the table.

As is, you can select only one city from the list. If you were using a list box you could set the Multi Select property to Simple or Extended to let users select more than one item. However, the combo box doesn't offer such a property. Even if you did use a multiselect list box, your users might not know that it provides the functionality. In either case, adding an (All) item to the list provides a method for your users to select all of the cities, along with a clear indication of just how to do so.

To add an (All) item to the list, return to Design view and select the combo box control. Then, replace the Row Source property with the following SQL statement:

  SELECT City FROM Employees 
UNION SELECT "(All)" FROM Employees;

In this case, the DISTINCT keyword isn't necessary since UNION SELECT returns unique entries. Now, click View and open the control's dropdown list to display the list shown in Figure B. As you can see, (All) is at the top of the list.

Figure B: We added an (All) item to the list.

Why include ( )?

At this point, you might be wondering why we enclosed the All item in parentheses. The truth is, you don't have to, but doing so does sort the item to the top of the list. The opening parenthesis character has an ASCII value of 40; the ASCII value of A is 65, so the enclosed item, regardless of its contents, will always sort to the beginning of the list. In the case of our simple example list, the parentheses aren't needed, since All alphabetically comes before the city entries of Kirkland, London, Redmond, Seattle and Tacoma. If you're sure All will always sort first, you can omit the parentheses. However, the parentheses do serve another purpose--they visually set the item off from the others, helping to distinguish it from the rest of the list.

At this point, you might be wondering why the list is sorted at all, since the SQL statement doesn't include an ORDER BY clause. By nature, a sorted list is produced by both the DISTINCT and UNION keywords. In order to return a unique list, the list is sorted.

Why not use a bound control?

For the most part, you'll probably use this technique with unbound controls. You can use it with bound controls, but doing so creates a bit of work. There are two problems. The first is the UNION query's structure. The same number of columns must be used in both of the SELECT clauses in the UNION query. You could repeat the (All) string as many times as necessary, but that's an awkward solution. For instance, the statement

  SELECT LastName, City FROM Employees 
UNION SELECT "(All)","(All)" FROM Employees;

returns the list shown in Figure C (note that you'll need to set the control's Column Count property to 2 if you're going to test this).

Figure C: The first column is bound and also contains an (All) item.

Repeating (All) in both columns is fairly harmless, even though it doesn't look good. You can get around this problem by specifying an empty string "" instead of repeating the (All) string. The larger problem is the bound column--you probably won't want to store the string (All) or even an empty string when you select (All) from the list. There are workarounds, but they require VBA, and as such are a bit beyond the scope of this article.

Other possibilities

By now you've probably thought of a few uses for this technique. Adding the item (All) to the list isn't the only possibility. Another good candidate is (None). You can probably think of several others, including some that are unique to your applications.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.