Create a list box, combo box, or drop-down list box

A list box, combo box, or 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.) can be a 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.) control, and it can look up values in a fixed list or in a table or query. To create a bound list box, combo box, or drop-down list box that looks up values in a table or query, make sure the form or data access page 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.).

ShowCreate a list box, combo box, or drop-down list box by using a wizard

  1. Open a form or data access page in 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.).
  2. In the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.), make sure that the Control Wizards tool Button image is selected (selected: A toolbar button always has a border around it when it's selected, even when the pointer is not resting on the button.).
  3. Click the List Box tool Control, the Combo Box tool Button image, or the Dropdown List tool Button image in the toolbox.
  4. On the form or data access page, click where you want to place the list box, combo box, or 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.). In the wizard dialog boxes, follow the instructions and choose the options you want.

ShowHow?

  1. When the wizard asks how you want to get the values for the control, do one of the following:
    • 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 to display the current data in a record source, click I want the list box to look up the values in a table or query.
  2. Follow the instructions for specifying how the values will appear.
  3. If you are creating a list box or combo box in a form, when the wizard asks what you want Microsoft Access to do when you select a value, do one of the following:
    • If you are creating an unbound control, click Remember the value for later use.
    • If you are creating a bound control, click Store that value in this field and select the field you want.
  4. Click Finish.

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.

  1. In a data access page, click Properties Button image on the Page Design toolbar to open the property sheet for the control. In the ControlSource property box, select the field you want to bind the list box or the drop-down list box to.

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

You can use 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 table to create a 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.) list box, combo box, or 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.) 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 an 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 SQL Server 2000 Desktop Engine.

  1. In 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.) of a table, create the Lookup field.
  2. Do one of the following:

ShowCreate a new form with a list box or combo box that is bound to the Lookup field

ShowCreate a new data access page with a list box or drop-down list box that is bound to the Lookup field

  1. Create a new data access page in Design view. The page must be connected to the database containing the Lookup field.
  2. If the field list isn't displayed, click Field List Button image on the Page Design toolbar.
  3. Drag the table or query that contains the Lookup field from the field list to the data access page. When the Layout Wizard appears, select Columnar or Tabular. Access automatically creates the lookup list box or drop-down list box.

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.

ShowAdd a list box, combo box, or drop-down list box to a form or data access page

  1. In Design view, open a form that 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 the Lookup field, or open a data access page that is connected to the database that includes the Lookup field.
  2. If the field list isn't displayed, click Field List Button image on the Form Design or Page Design toolbar.
  3. Drag the Lookup field from the field list to the form or data access page. Access automatically creates the lookup list box, combo box, or drop-down list box.

ShowCreate a list box, combo box, or drop-down list box from scratch

When you create a list box, combo box, or drop-down list box from scratch, 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 or data access page in 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.).
  2. In the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.), make sure that the Control Wizards tool Button image is not selected (selected: A toolbar button always has a border around it when it's selected, even when the pointer is not resting on the button.).
  3. Click the List Box tool Button image, the Combo Box tool Control, or the Dropdown List tool Button image in the toolbox.
  4. Click the form, report, or data access page to create a default-sized control, or click and drag until the control is the size you want.
  5. With the control still selected, click Properties Button image on the Form Design or Page Design toolbar to open the property sheet for the control.
  6. Do one of the following:

ShowSet properties for a list box or combo box in a form

  1. In the RowSourceType property box, do one of the following:
    • In an Access database, to show values from a table or query, or results of an SQL statement, select Table/Query.
    • In an Access project, to show values from a table or query, or results of an SQL statement, select Table/View/StoredProc.
    • To show a fixed list of values, select Value List.
    • To show a list of fields in a table or query, select Field List.
  2. In the RowSource box, do one of the following:
    • Select a table or query containing the values or field names that you want to appear in the list box.
    • Type a list of fixed values and separate each item with a semicolon (;).
    • Type an SQL statement or click the Build Button image button to open the Query Builder.
  3. If you want more than one column to appear in the control, click the ColumnCount property box and type the number of columns you want.

ShowSet properties for a list box or drop-down list box in a data access page

  1. Set the ListRowSource property box, select the recordset you want.
  2. In the ListBoundField and ListDisplayField property boxes, select the fields you want.
  1. If you want Access to store the value you select, click the ControlSource property box and select the field to which you want to bind the list box, combo box, or drop-down list.

ShowIn a form, create a list box or combo box that gets its rows from a Visual Basic function

You may want to use this procedure 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.

  1. Create a Visual Basic 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.).
  2. In the toolbox (toolbox: A set of tools that is available in Design view for adding controls to a form or report.), make sure that the Control Wizards tool Button image is not selected (selected: A toolbar button always has a border around it when it's selected, even when the pointer is not resting on the button.).
  3. Click the List Box tool Button image or the Combo Box tool Control in the toolbox.
  4. With the control still selected, click Properties Button image on the Form Design toolbar to open the property sheet for the control.
  5. In the RowSourceType property box, enter the name of the function. Don't put an equal sign before the function name.
  6. Leave the RowSource property box blank.

ShowCustomize a list box, combo box, or drop-down list box

  1. In 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.) of the form or data access page, make sure that the list box (list box: A control that provides a list of choices. A list box consists of a list and an optional label.), combo box (combo box: A control used on a form that provides the combined functionality of a list box and a text box. You can type a value in a combo box, or you can click the control to display a list and then select an item from that list.), or 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.) is selected, and then click Properties Button image on the toolbar to open the property sheet for 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.).
  2. Do one of the following:

Change the sort order in a list box, combo box, or drop-down list box

If you used a wizard to create the list box, drop-down list box, or combo box, Microsoft 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 RowSource property of the control to a saved query, you can use one of the following two procedures.

 Note   Wizards are not available in a stand-alone data access page, or in a data access page that is connected to a database other than the one currently open. In this case, you will have to create a list box or drop-down list box on your own.

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.

  • To change the sort order of a control on a form, click the Data tab and next to the RowSource property box, click the Build button Button image to open the Query Builder or SQL Statement Builder and specify the sort order you want.
  • To change the sort order of a control on a data access page, create a query with the sort order you want, and then in the data access page's ListRowSource property box, select the query from the list.

Bind a column from a list box, combo box, or drop-down list box

  • In a form     In the BoundColumn 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 ControlSource property. Include hidden columns when you count columns.

If you set the BoundColumn property to 0, Microsoft Access saves the list index (list index: The sequence of numbers for items in a list, starting with 0 for the first item, 1 for the second item, and so on.) 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.

  • In a data access page     In the ListBoundField property box of the list box or drop-down list box, enter the name of the field that corresponds to the placement of the column in the list box or drop-down list box.

Use an SQL statement as the row source for a list box or combo box on a form

You might want to use 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.) instead of a saved query for the row 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.) of a list box or combo box on a form 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.

  1. Click the Build button Button image next to the RowSource property to open the Query Builder.
  2. In the Query Builder, click SQL View on the View menu.
  3. In the SQL window, select the entire SQL statement, and then press CTRL+C to copy it. Close the Query Builder.
  4. Click in the RowSource property box, and press CTRL+V to paste the SQL statement into the property box.
  5. Delete the original query in the Database window.

Hide a column in a list box or combo box on a form

  • In the ColumnWidths property box, enter 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 SupplierID column and a SupplierName column. The SupplierID column is the first column in the list. To hide the SupplierID column, set the ColumnWidths property as shown in the following illustration.

Hide columns by using the ColumnWidths property

Callout 1 Set the SupplierID column (first column) to 0 to hide it.

Callout 2 Specify a width for the SupplierName column.

Callout 3 The SupplierID column is the bound column, even though it's 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 the only column that was displayed.

Add column headings to a combo box on a form

  • In the ColumnHeads 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 (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.), Microsoft 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, Microsoft Access uses the first x items of data from the value list (RowSource property) as the column headings, where x = the number set in the ColumnCount property.

Turn off the fill-in-as-you-type feature for a combo box on a form

  • In the AutoExpand property box, click No.

When the AutoExpand 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 ListWidth property box, enter the desired width 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 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 ListRows property box, enter a number.

If the actual number of rows exceeds the number specified in the ListRows 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 LimitToList property box, click Yes.

Notes

If the first column displayed in a combo box is not the 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.) column, Microsoft Access limits the entries to the list even if the LimitToList property is set to No.

If the LimitToList 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 OnNotInList property and the NotInList event.

 
 
Applies to:
Access 2003