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: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.). 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 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.

  

  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.

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.