How do you select multiple values from a lookup field in a record in Access?

This topic explains how to use check box drop-down lists and check box lists — lists that allow you to select and store more than one value in a table field. These multivalued lists are a new feature in Microsoft Office Access 2007. They function much like normal lists, but they provide check boxes next to each list item. You can select up to 100 items by selecting the check boxes.

The new lists help solve some common data entry and storage problems. For example, suppose you use Access to track work assignments. You can now assign several people to a single task by selecting their names from a single list — something you could not do in earlier versions of Access without using some Visual Basic for Applications (VBA) programming.

 Note   The new lists take their data from another database component called a multivalued lookup field. You must have a multivalued lookup field in one of your database tables before you can create multivalued lists. Explaining how to create multivalued lookup fields is beyond the scope of this article.

For information about creating a multivalued lookup field, see the articles Add or change a lookup field that lets you store multiple values and Guide to multivalued fields.

---

---

Understand the technology behind check-box drop-down lists and check box lists

Check box drop-down lists and check box lists rely on a new feature called multivalued fields. Put briefly, multivalued fields allow you to attach multiple items to a single field in a table, an action you could not take with earlier versions of Access.

Office Access 2007 presents multivalued fields to you in the form of several features, including check box drop-down lists and check box lists, multivalued table fields, and attachments. The lists and multivalued table fields give you the ability to choose multiple items from a single list, and attachments give you the ability to attach multiple pieces of data to a record, much like you attach images and other files to e-mail messages.

For more information about using attachments, see the article Attach files and graphics to the records in your database.

To create check box drop-down or check box lists, you start with a multivalued lookup field — a table field that uses a query to look up values contained elsewhere in the database. Explaining how to create multivalued lookup fields is beyond the scope of this article.

For information on creating them, see the articles titled Add or change a lookup field that lets you store multiple values and Guide to multivalued fields.

After you create a multivalued lookup field, you can use the field directly or you can bind a check box drop-down list or a check box list to the field. A check box drop-down list is a combo box control set to support multiple values.

These figures show a typical check box drop-down list in the closed and open states.

 Note   Normal combo-box controls allow you to select items from a list or enter new items directly in the control. However, when you set a combo box control to support multiple values, you can only select items from the list. You cannot enter values of your own.

The check box list is a list box control set to support multiple values.

Unlike the check box drop-down list, a check box list does not close after you make your selections, and it does not provide OK or Cancel buttons.

You can use either control on forms and reports, but when you use them on reports, they only display your selections. By default, you cannot use the controls on a report to change data.

Finally, you can also use a multivalued lookup field directly in a table by clicking or otherwise selecting the table field. When you do so, Access applies only the check box drop-down list by default — you cannot use another control.

The steps in the following sections explain how to select one or more items from a multivalued lookup field. The steps use the Issue Tracking database template that comes with Office Access 2007.

Top of Page

Use a multivalued list in a form

These steps assume you have a database table that contains a multivalued lookup field, and a form that contains a check box drop-down list or a check box list.

For more information on creating the lookup field, see the articles titled Add or change a lookup field that lets you store multiple values and Guide to multivalued fields.

Use a multivalued list

1. Open the form that contains the check box drop-down list or check box list.

In both types of list, a check box accompanies each item.

1. Click the check boxes that you want to select. If you use a check box drop-down list, click OK.

If you use a check box drop-down list, the control closes and displays your choices in a comma-separated list, like so:

If you can't see all of your choices, you can open the form in Design or Layout views and enlarge the list control. For information on doing so, see the section Enlarge a check box drop-down list to display more choices, later in this article.

 Notes 

• Remember that you can always recognize a multivalued list because check boxes appear next to each list item.
• In this example, the list in the sample database uses a check box drop-down list control. The check box list box control functions in the same way — you select items by clicking the check boxes — but the control does not provide OK or Cancel buttons.

As a reminder, you can select a maximum of 100 check boxes.

Top of Page

Use a multivalued list in a table

When you use a multivalued list in a table, Access provides only one control — a check box drop-down list — and you cannot change that control.

1. In the Navigation Pane, double-click the table that you want to use. This opens the table in Datasheet view.
2. Placed the focus on the multivalued field, and then click the down arrow adjacent to the field.

 Note   Your Windows Regional and Language Settings control the placement of the down arrow.

1. Click the check box next to each of the items that you want to select, and then click OK.

The list closes and displays your choices, separated by commas, like so:

Top of Page

Enlarge a check box drop-down list to display more choices

When you use a check box drop-down list to select a large number of items, you may not see all the selected items when you close the list. Database designers use drop-down lists (combo box controls) because they use screen space efficiently — they display the options that a user needs, and then close after the user makes a selection. If a check box drop-down list is not large enough to display all your selections, you can enlarge the control. The following steps explain how to enlarge the control and test the changes.

 Note   You typically do not need to resize check box list controls, because they provide a scroll bar when the list exceeds the size of the control window.

Resize a control

1. In the Navigation Pane, right-click the form that you want to change and click Design View or Layout View.

–or–

On the Home tab, in the Views group, click View, and then click Design View or Layout View.

1. Click the check box drop-down list control, point to one of the resize handles, and drag the edge of the control until it reaches the height or width you want, like so:

–or–

Right-click the control and click Properties. On the property sheet, click the All tab, click the Height or Width properties, and enter a size.