Add a list box or combo box

When you enter data on forms in Access desktop databases, 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 make sure 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 Access forms and how to create and customize them.

What do you want to do?

• Learn about the types of list box controls
• Create a list box or a combo box by using a wizard
• Create a list box or a combo box by adding a Lookup field to a form
• Create a list box or a combo box without using a wizard
• Customize a list box or a combo box

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 it 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 the control can display, Access shows a scroll bar in the control. Users are limited to the choices in the list box, and they can't type a value into a 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 select the drop-down arrow. A combo box also lets you enter a value that isn't in the list. In this way, the combo box control combines the features of a text box and a list box.

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

List boxes and combo boxes can be bound or unbound 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 that the form is based on a record source that includes a foreign key field or Lookup field. This makes it possible to create the relationships needed to link the data in the list box or combo box to the data on the form.

Top of Page

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

1. In the Navigation Pane, right-click the form, and then select Design View.

   Note

   This procedure assumes that the form is bound to a table or query. Some of the steps don't apply if the form is unbound. To determine whether 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.

2. On the Form Design tab, in the Controls group, make sure that Use Control Wizards is selected.

3. Select the List Box tool or the Combo Box tool.

4. On the form, select 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.

5. 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, select 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, select 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, select 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 that the user enters.

6. Follow the instructions to specify how the values will appear.

7. 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:

   • To create an unbound control, select Remember the value for later use. This means that Access will hold the selected value until the user changes it or closes the form, but it won't write the value to a table.
   • To create a bound control, select Store that value in this field, and then select the field that you want to bind the control to.

8. Select Next, and then type a label for the control. This label is displayed next to the control.

9. Select Finish.

Top of Page

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 that you create can be either multivalued or contain a single value. For more information about creating multivalued Lookup fields, see Create or delete a multivalued field.

2. 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, select Form. 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 the other way around, right-click the control, select Change To on the shortcut menu, and then select the control type that you want.

Top of Page

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 control's properties yourself. If you want more information about a particular property, select the appropriate property box and press F1.

1. Open a form in Design view.

2. On the Form Design tab, in the Controls group, make sure that Use Control Wizards isn't selected.

3. Select the List Box tool or the Combo Box tool.

4. Select once inside the form to create a default-sized control, or select and drag until the control is the size that you want.

5. With the control still selected, press F4 to open its property sheet.

6. Set the Row Source Type and Row Source properties by using the following table as guidance.

   To do this	Set the Row Source Type property to	Set the Row Source property as follows
   Show values from a table or query, or the results of a SQL statement	Table/Query	In the drop-down list, select the table or query that contains the values that you want to appear in the list box or combo box. Or type a SQL statement. Or, on the Data tab of the property sheet, select the Build button to open the Query Builder. For more information about building a query, see Create a simple select 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, select the Build button 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 that contains the field names that you want to appear in the list box or combo box.

7. If you want more than one column to appear in the control, select the Column Count property box and type the number of columns that 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.

8. If you want Access to store the value that you select, select the Control Source property box and then select the field to which you want to bind the list box or combo box.

Top of Page

Customize a list box or a combo box

With the form open in Design view, make sure that the list box or combo box is selected, and then press F4 to open the control's property sheet. 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:

  • Select the Data tab, and then select the Row Source property box.
  • On the Data tab of the property sheet, select the Build button to open the Query Builder.
  • In the Sort row for the column that you want to sort, specify the sort order that you want.

• Bind a column from a list box or combo box: In the Bound Column property box of the list box or 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 that you have a bound two-column combo box that has a 0.5-inch-wide SupplierID column and a 2-inch-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 weren't 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, select 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, select 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 by 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 that you 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 can't 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, select Yes.

    Note

    • If the first column displayed in a combo box isn't 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