Create a list of choices by using a list box or combo box

When entering data on forms, it can be quicker and easier to select a value from a list than to remember a value to type. A list of choices also helps ensure that the value entered in a field is appropriate. A list control can connect to existing data, or it can display fixed values that you enter when you create the control. Read on to learn about the list controls available for Microsoft Access forms, and how to create and customize them.

What do you want to do?


Learn about the types of list box controls

Access provides two list controls for forms — the list box and the combo box.

List box    The list box control displays a list of values or choices. The list box contains rows of data, and is usually sized so that several rows are visible at all times. The rows can have one or more columns, which can appear with or without headings. If the list has more rows than can be displayed in the control, Access displays a scroll bar in the control. The user is limited to the choices given in the list box; it is not possible to type a value into a list box.

List box

Combo box    The combo box control provides a more compact way to present a list of choices; the list is hidden until you click the drop-down arrow. A combo box also gives you the ability to enter a value that is not in the list. In this way, the combo box control combines the features of a text box and a list box.

Using a combo box (drop-down list)

Callout 1 Click the arrow to display the drop-down list.
Callout 2 Click an option in the drop-down list.

List boxes and combo boxes can be bound (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) or unbound (unbound control: A control that is not connected to a field in an underlying table, query, or SQL statement. An unbound control is often used to display informational text or decorative pictures.) controls. These controls can look up values in a fixed list that you type yourself, or they can look up values in a table or query. To create a bound list box or combo box that looks up values in a table or query, make sure the form is based on a record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) that includes a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) field or Lookup field (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.). This makes it possible to create the relationships that are needed to link the data in the list box or combo box to the data on the form.

Create a list box or a combo box by using a wizard

  1. Right-click the form in the Navigation Pane, and then click Design View.

 Note   This procedure assumes that the form is bound to a table or query. Some of the steps will not apply if the form is unbound. To determine if the form is bound to a table or query, press F4 to display the property sheet. On the Data tab of the property sheet, the Record Source property box displays the table or query that the form is bound to.

  1. On the Design tab, in the Controls group, ensure that Use Control Wizards Button image is selected.
  1. Click either the List Box Button image tool or the Combo Box Button image tool.
  2. On the form, click where you want to place the list box or combo box.
    • Depending on your choice, the List Box Wizard or the Combo Box Wizard starts.
  3. When the wizard asks how you want to get the values for the control, do one of the following:
    • If you want to display the current data from a record source, click I want the list box/combo box to look up the values in a table or query.
    • If you want to display a fixed list of values that will seldom change, click I will type in the values that I want.
    • If you want the control to perform a find operation, rather than serve as a data entry tool, click Find a record on my form based on the value I selected in my list box/combo box. This creates an unbound control with an embedded macro that performs a find operation based on the value the user enters.
  4. Follow the instructions for specifying how the values will appear.
  5. If you chose one of the first two options on the first page of the wizard, the wizard asks what you want Access to do when you select a value. Do one of the following:
  6. Click Next and type a label for the control. This label will be displayed next to the control.
  7. Click Finish.

Create a list box or a combo box by adding a Lookup field to a form

You can create a bound list box or combo box by adding a Lookup field to a form.

  1. Create a Lookup field in a table. The Lookup field you create can be either multivalued or contain a single value.

For more information about creating Lookup fields, see the article Add or change a lookup field that lets you store multiple values.

  1. Do one of the following:
    • Create a new form that is based on a record source that includes the Lookup field. For example, in the Navigation Pane, select a table or query that contains the Lookup field, and then on the Create tab, in the Forms group, click Form Button image.

Access automatically creates a combo box for the Lookup field.

  • Add a list box or combo box to a form:
    1. In Design view, open a form that is based on a record source that includes the Lookup field.
    2. If the Field List pane isn't displayed, press Alt+F8 to display it.
    3. Double-click the Lookup field, or drag the Lookup field from the Field List pane to the form. Access automatically creates a combo box bound to the field.

 Tip   To change a combo box to a list box (or vice versa), right-click the control, click Change To on the shortcut menu, and then click the control type you want.

Create a list box or a combo box without using a wizard

When you create a list box or combo box without using a wizard, you set many of the properties of the control yourself. If you want more information about a particular property, click the appropriate property box and press F1.

  1. Open a form in Design view.
  2. On the Design tab, in the Controls group, ensure that Use Control Wizards is not selected.

Use Control Wizards button

  1. Click the List Box Button image tool or the Combo Box Button image tool.
  2. Click once inside the form to create a default-sized control, or click and drag until the control is the size you want.
  3. With the control still selected, press F4 to open its property sheet.
  4. Set the Row Source Type and Row Source properties, with guidance from the following table.
To do this... set the Row Source Type property to... and set the Row Source property as follows:
Show values from a table or query, or the results of an SQL statement Table/Query

In the drop-down list, select the table or query containing the values that you want to appear in the list box or combo box.

–or–

Type an SQL statement.

–or–

On the Data tab of the property sheet, click Button image to open the Query Builder. For more information about building a query, see the article Select data by using a query.

Show a fixed list of values Value List

Type a list of fixed values separated by semicolons (;). For example, North;South;East;West

–or–

On the Data tab of the property sheet, click Button image to open the Edit List Items dialog box, and then type the items on separate lines.

Show a list of fields from a table or query Field List In the drop-down list, select the table or query containing the field names that you want to appear in the list box or combo box.
  1. If you want more than one column to appear in the control, click the Column Count property box and type the number of columns you want. Set the Column Widths property to adjust the widths of the columns. For more information about each property, place the cursor in the property box and then press F1.
  2. If you want Access to store the value you select, click the Control Source property box and select the field to which you want to bind the list box or combo box.

Edit a value list while a form is open in Form view

If the Row Source Type property of your list box or combo box is set to Value List, you can edit the list of values while the form is open in Form view — avoiding the need of switching to Design view or Layout view, opening the property sheet, and editing the Row Source property for the control each time you need to make a change to the list.

 Note   To edit the list of values, the Allow Value List Edits property for the list box or combo box must be set to Yes.

Edit a value list in Form view

When your cursor enters a list box or combo box (by clicking or tabbing to the box), a button appears below the list.

Combo box with Edit List Items button

  1. Click the button to open the Edit List Items dialog box.
  2. Edit the list items, keeping each item on a separate line.
  3. To select a default value for new records, click the drop-down arrow in the Default Value box, and then click the value you want.
  4. Click OK.

Prevent the editing of the value list in Form view

  1. Right-click the form in the Navigation Pane and click Design view or Layout view.
  2. Click the control to select it, and then press F4 to display the property sheet.
  3. On the Data tab of the property sheet, set the Allow Value List Edits property to No.
  4. Click File and then click Save, or press Ctrl+S Button image.

Specify a different form for editing the value list

By default, Access provides a built-in form for the purpose of editing the value list. If you have another form that you would prefer to use for this purpose, you can enter the name of the form in the List Items Edit Form property, as follows:

  1. Right-click the form in the Navigation Pane, and then click Design view or Layout view on the shortcut menu.
  2. Click the control to select it, and then press F4 to display the property sheet.
  3. On the Data tab of the property sheet, click the drop-down arrow in the List Items Edit Form property box.
  4. Click the form you want to use for editing the value list.
  5. Click the File and then click Save, or press Ctrl+S Button image.

Customize a list box or a combo box

With the form open in Design view, ensure that the list box or combo box is selected, and then press F4 to open the property sheet for the control. Then, do one of the following:

  • Change the sort order in a list box or combo box    If you used a wizard to create the list box or combo box, Access automatically sorts the rows that make up the list by the first visible column. If you want to specify a different sort order, or if you have set the Row Source property of the control to a saved query, use the following procedure:
    • Click the Data tab, and then click the Row Source property box.
    • On the Data tab of the property sheet, click Button image to open the Query Builder.
    • In the Sort row for the column you want to sort, specify the sort order you want.
  • Bind a column from a list box or combo box    

In the Bound Column property box of the list box or the combo box, specify a number that corresponds to the placement of the column in the list box or combo box. For example, type 1 to bind the first column in the list box or combo box to the underlying field specified in the Control Source property. Include hidden columns when you count columns.

If you set the Bound Column property to 0, Access saves the list index instead of a value from one of the columns. This is useful if you want to store a sequence of numbers instead of the list value.

  • Hide a column in a list box or combo box on a form    
    • In the Column Widths property box, type 0 for the column or columns that you want to hide.

For example, suppose you have a bound two-column combo box that has a 0.5" wide SupplierID column and a 2" wide SupplierName column. The SupplierID column is the first column in the list, so the Column Widths property is set to 0.5";2". To hide the SupplierID column, set the Column Widths property to 0";2". The SupplierID Column can still be the bound column, even though it is hidden.

  •  Note   In a combo box, the first visible column is displayed in the text box portion of the combo box when the list isn't displayed. For example, the SupplierName column in the previous example would be displayed because the SupplierID column is hidden. If the SupplierID column was not hidden, it would be displayed instead of the SupplierName column.

  • Add column headings to a combo box on a form    
    • In the Column Heads property box, click Yes to display column headings. Headings in combo boxes appear only when the list is open.

If the combo box or list box is based on a record source, Access uses the field names from the record source as the column headings. If the combo box or list box is based on a fixed value list, Access uses the first n items of data from the value list (Row Source property) as the column headings, where n = the number set in the Column Count property.

  • Turn off the fill-in-as-you-type feature for a combo box on a form    
    • In the Auto Expand property box, click No.

When the Auto Expand property is set to No, you must select a value from the list or type the entire value.

  • Set the width of the list box portion of a combo box on a form    
    • In the List Width property box, enter the width that you want, using the current unit of measurement (set in Windows Control Panel). To use a unit of measurement other than the default, include a measurement indicator. For example, enter 2 cm. Make sure to leave enough space for a scroll bar.

The list box portion of the combo box can be wider than the text box portion, but it cannot be narrower. The default setting (Auto) makes the list box the same width as the text box portion of the combo box.

  • Set the maximum number of rows to display in a combo box on a form    
    • In the List Rows property box, enter a number.

If the actual number of rows exceeds the number specified in the List Rows property, a vertical scroll bar is displayed in the combo box.

  • Limit combo box entries to items in the list portion of a combo box on a form    
    • In the Limit To List property box, click Yes.


 Notes 

  • If the first column displayed in a combo box is not the bound column, Access limits the entries to the list even if the Limit To List property is set to No.
  • If the Limit To List property is set to No, when you enter an entry that isn't in the list, if the combo box is bound, the entry is stored in the underlying field, but it isn't added to the list. To add new entries to the list, use the On Not In List property and the Not In List event.

Top of Page Top of Page

 
 
Applies to:
Access 2013, Access 2010, Access 2007