When entering 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 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 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.
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.
1. Click the arrow to display the drop-down list.
2. Click 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 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 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
-
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.
-
On the Form Design tab, in the Controls group, ensure that Use Control Wizards is selected.
-
Click either the List Box
-
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.
-
-
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.
-
-
Follow the instructions for specifying how the values will appear.
-
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, click 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 will not write the value to a table.
-
To create a bound control, click Store that value in this field, and then select the field you want to bind the control to.
-
-
Click Next and type a label for the control. This label will be displayed next to the control.
-
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.
-
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 multivalued Lookup fields, see the article Create or delete a multivalued field.
-
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.
Access automatically creates a combo box for the Lookup field.
-
Add a list box or combo box to a form:
-
In Design view, open a form that is based on a record source that includes the Lookup field.
-
If the Field List pane isn't displayed, press Alt+F8 to display it.
-
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.
-
Open a form in Design view.
-
On the Form Design tab, in the Controls group, ensure that Use Control Wizards is not selected.
-
Click the List Box
-
Click once inside the form to create a default-sized control, or click and drag until the control is the size you want.
-
With the control still selected, press F4 to open its property sheet.
-
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
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
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.
-
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.
-
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.
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
-
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.
-
-
