This section describes one of the best and most common Microsoft Access techniques for displaying data from more than one table. Because combo and list boxes support multiple columns, they allow you to easily display related data from another table without basing your form on a query that joins the tables. This technique, which involves a bound combo or list box control that stores an ID number but displays names in a list, is used throughout the Northwind sample database.
For example, suppose you're creating a form to display product information, and you want to identify the supplier for each product record. In a well-designed database, you store only a supplier ID number with each product record, while you store the supplier's name and other information in a separate table. You want your form to include a combo box that displays supplier names in the list, but stores supplier ID numbers in the field. (For an example of this technique, see the "Combo 1" form in AC208.MDB.)
To achieve this, you create a multiple-column combo box. Set the RowSourceType property of the list box to Table/Query. You could base the list on a table, but you want the list of names to be sorted; instead, set the RowSource property to a query that includes supplier ID numbers in the first field, and names sorted ascending in the second field. The easiest way to do this is using the Query Builder for the RowSource property to create an SQL statement; alternatively, you can create and save a query to provide the list. Finally, because we are interested in only the first two columns, set the ColumnCount property to 2.
The first column contains the data that is saved by the control when a row is selected by the user. This column is identified by the BoundColumn property (set to 1 by default). The bound column containing ID numbers doesn't have to be visible to the user. The ColumnWidths property contains a semicolon-separated list of visible column widths for the columns in the drop-down menu. Microsoft Access uses default algorithms to determine the widths of any columns for which you do not explicitly choose a width. If you choose a width of 0 for any column, that column is effectively hidden from the user on the screen, but it is not hidden from the rest of your forms, Microsoft Access Basic code, or macros. In this case you can set the property to 0, indicating to Microsoft Access that you want the first column to be invisible and the second column to have the default width. (You can use additional semicolon-separated numbers to specify widths for any of the remaining columns, instead of allowing Microsoft Access to calculate default column widths.)
The second column, in this case the name, is the one the user's text input is matched against. The first visible column in the combo box is always used for this purpose.
Note that combo boxes with a bound column equal to the first visible column can be painted more quickly than those with a bound column other than the first visible column, since Microsoft Access takes longer to display a different value than it stores in the field.