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

1. Open a form or data access page in Design view.
2. In the toolbox, make sure that the Control Wizards tool is selected.
3. Click the List Box tool , the Combo Box tool , or the Dropdown List tool 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. In the wizard dialog boxes, follow the instructions and choose the options you want.

   How?

   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.
5. In a data access page, click Properties 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.

You can use a Lookup field in a table to create a bound list box, combo box, or drop-down list box in a Microsoft Access database or an Access project that is connected to a database in Microsoft SQL Server 2000 or the Microsoft SQL Server 2000 Desktop Engine.

1. In Design view of a table, create the Lookup field.

2. Do one of the following:

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

   • Create a new form that is based on a record source that includes the Lookup field. Access automatically creates the lookup list box or combo box.

   Create 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 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.

   Add 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 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 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.

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.
2. In the toolbox, make sure that the Control Wizards tool is not selected.
3. Click the List Box tool , the Combo Box tool , or the Dropdown List tool in the toolbox.
4. Click the 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 on the Form Design or Page Design toolbar to open the property sheet for the control.
6. Do one of the following:

   Set 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 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.

   Set 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.
7. 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.

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.
2. In the toolbox, make sure that the Control Wizards tool is not selected.
3. Click the List Box tool or the Combo Box tool in the toolbox.
4. With the control still selected, click Properties 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.

1. In Design view of the form or data access page, make sure that the list box, combo box, or drop-down list box is selected, and then click Properties on the toolbar to open the property sheet for the control.
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.

   • 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 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 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. 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, 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 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.