About creating a list box, combo box, or drop-down list box that looks up values

To create a list box, drop-down list box (drop-down list box: A control on a data access page that, when clicked, displays a list from which you can select a value. You cannot type a value in a drop-down list box.), or combo box that looks up values, you need to decide two things:

  • Where will the rows for the list come from?
  • When a user selects an item in the list box, drop-down list box, or combo box, what will Microsoft Access do with that value?

The rows for a list box, drop-down list box, or combo box can come from a fixed list of values that you enter when you create the list box, drop-down list box, or combo box (use when the list won't change very often), or they can come from 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.) (use when you'll frequently update the list). For example, you could use a fixed list for a list box that contains entries for Mr., Mrs., or Ms. If, on the other hand, you have a Products form and you want to display a list of product suppliers in a list box, but the list will change frequently, base the list box on a record source. The list box looks up values in the Suppliers table and displays an up-to-date list of the suppliers that you can choose from.

List box displaying values from Suppliers table

Callout 1 Enter a product.

Callout 2 Pick a supplier from the list.

When a user selects a value in a list box, drop-down list box, or combo box, Microsoft Access can do one of two things: Access can store the selected value in a table (not the same table that the list gets its rows from), or Access can pass the value to another control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.). For example, for the Supplier list box on the Products Form in the preceding illustration, if a user selects "Pavlova, Ltd." , Access looks up the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) value (SupplierID) for Pavlova, Ltd. in the Suppliers table and sets the SupplierID field (the 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.)) for the current record in the Products table to the same value. This is the value that is stored. Because Access is storing a value based on a selection in the list box, the list box is 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.). (Note that the SupplierName value that comes from the Suppliers table is displayed in the list box but not stored.)

If, on the other hand, you want Access to use the value selected in the list box, drop-down list box, or combo box to determine the contents of another control or controls, you would create an 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.) list box, drop-down list box, or combo box. For example, you might want to create a list box or drop-down list box that you can use to find a related record on a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). Or you might want to create a list box or combo box that you use to filter the records in another list box or combo box on a form.

ShowShould I create a list box, a combo box, or a drop-down list box?

To decide whether to create a list box, combo box, or drop-down list box, think about where you're going to use the control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), how you want the control to appear on your form or data access page, and how you expect people to use it. Each control offers advantages:

Advantages of a list box     You can use list boxes on forms and data access pages. The list appears at all times and the value of the control is limited to the set of alternatives in the list. To move quickly to the first value that starts with a particular letter, you can type that letter. When you're using a form to enter or edit data, you can't add a value that's not in the list.

Advantages of a combo box     You can use combo boxes on forms. Since the list isn't displayed until you open it, the control uses less room on the form. You can type the first few characters of a value in the combo box to quickly find that value. You can also control whether any value can be entered in the list, or only text that matches one of the values in the list can be entered.

Advantages of a drop-down list box     You can use drop-down list boxes on data access pages. Because the list isn't displayed until you open it, the control uses less room on the data access page. The value of the control is limited to the set of alternatives in the list. To move quickly to the first value that starts with a particular letter, you can type that letter.

ShowShould I use a table, a query, an SQL statement, or a function to provide the values?

The row source of a list box, combo box, or drop-down list box provides the values that are displayed. In most cases, you can use a table as the row source when you create the list box, drop-down list box, or combo box with a wizard.

 Note   To use a wizard to create a control on a form or report, make sure the Control Wizards button is selected in the Toolbox before you insert your control. After you insert the control, the appropriate wizard will start. The Combo List Box, List Box, Subform, Subreport, and the Command Button Wizards are all available from the Toolbox.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

Use a query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) in the following cases:

  • If you want to include calculated fields in the list box, drop-down list box, or combo box. For example, you could concatenate a first name and a last name by typing the following expression: Name: [FirstName] & " "& [LastName]. You could define this calculated field in a query, and then include this field in the list box, drop-down list box, or combo box.
  • If you want to include fields from more than one table in the list box, combo box, or drop-down list box.

When you select fields from a table or query by using a wizard in a form, Microsoft Access creates an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) based on the fields that you select and sets the RowSource property of the list box or combo box to that SQL statement. When you select fields from a table or query by using a wizard in a data access page, Access sets the ListRowSource property of the list box or drop-down list box to the table or query you selected.

You can also set the row source of a list box, combo box, or drop-down list box yourself. For example, you can set the row source to a table or a saved query, or you can set the row source to an SQL statement so that the query doesn't show up in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.). However, list boxes and combo boxes based on SQL statements are slower than list boxes and combo boxes based on saved queries.

In addition, you can use a function (Function procedure: A procedure that returns a value and that can be used in an expression. You declare a function with the Function statement and end it with the End Function statement.) as a type of row source when the values in a list are constantly changing, or if the values aren't stored in a database. For example, you could write a function that fills a list box with a list of dates, each one a specific number of days after the current date.

ShowWays to create a list box, combo box, or drop-down list box

Wizards     Using a wizard is an easy way to create a list box, combo box, or drop-down list box. However, wizards are not available in a stand-alone data access page, or when you point a data access page to another database than the one currently open. In this case, you will have to create a list box or drop-down list box on your own. To use a wizard to insert a control on a form or report, make sure the Control Wizards button is selected in the Toolbox before you insert your control.

ShowIf the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the Office Online article About Microsoft Jet Expression Service sandbox mode.

Lookup fields     You can create a list box, combo box, or drop-down list box that is 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.) to a 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.) in a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) or in a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) that is connected to a database in Microsoft SQL Server 2000 or the Microsoft Access SQL Server Desktop Engine. When you create a Lookup field, you only need to create the list box or combo box once. Then you can use the same lookup list on any form. If you don't plan to use the same lookup list in more than one form, you can use the List Box Wizard or Combo Box Wizard in form Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.). You can't use a Lookup field to create a list box or drop-down list box in a data access page.

On your own     You might want to create a list box, combo box, or drop-down list box without using a wizard or lookup field if you want to set the properties of the control yourself. For example, you might not want to use a wizard or lookup field if you want to populate the list box based on the value of another control on the form.

 
 
Applies to:
Access 2003