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 (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.
Enter a product.
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. For example, for the Supplier list box in the preceding illustration, if a user selects "Pavlova, Ltd." , Access looks up the primary key value (SupplierID) for Pavlova, Ltd. in the Suppliers table and sets the SupplierID field (the foreign key) 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. (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 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. 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.
Should 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, 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.
Should 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. Use a query 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 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. 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 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.
Ways 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.
Lookup fields You can create a list box, combo box, or drop-down list box that is bound to a Lookup field in a Microsoft Access database or in a Microsoft Access project 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. 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 specify that the type of row source is a function.