- 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 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.).
- 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.
- 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 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 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.
- Click the Build button next to the RowSource property to open the Query Builder.
- In the Query Builder, click SQL View on the View menu.
- In the SQL window, select the entire SQL statement, and then press CTRL+C to copy it. Close the Query Builder.
- Click in the RowSource property box, and press CTRL+V to paste the SQL statement into the property box.
- 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.
Set the SupplierID column (first column) to 0 to hide it.
Specify a width for the SupplierName column.
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.
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.