Part III: More-Advanced Access Techniques
692
want your form to include a combo box that displays organization names and addresses in the list
but stores organization ID numbers in the field. (For an example of this technique, see frmCon-
tacts_Northwind in Chapter19.accdb.)
To accomplish your design goal, create a multiple-column combo box. Set the ControlSource
to the OrgID field (the field in the Contacts table that contains the organization ID number for
each contact person). Set the RowSourceType property of the combo 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 OrgID numbers in the first field, and organization
names sorted ascending in the second field. The best way to do this is using the Query Builder for
the RowSource property to create a SQL statement; alternatively, you can create and save a query
to provide the list. In frmContacts_Northwind example (the Organization combo box), the
RowSource query is as follows:
SELECT Organizations.OrgID, Organizations.Name,
Organizations.AddressLine1, Organizations.AddressLine2,
Organizations.City, Organizations.State,
Organizations.ZipCode, Organizations.Country
FROM Organizations ORDER BY Organizations.Name
Because you’re interested in seeing all this data listed in the combo box, set the ColumnCount
property to 8. You hide the OrgID column in a minute, but you need it in the combo-box
RowSource because it contains the data that’s saved by the control when a row is selected by the
user. This column is identified by the combo box’s 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 col-
umns in the drop-down list. Access uses default algorithms to determine the widths of any col-
umns for which you don’t 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 isn’t hidden from the rest of
your forms, VBA code, or macros. In this case, you set the property to the following:
0”;1.4”;1.2”;0.7”;0.7”;0.3;0.5”;0.3”
This indicates to Access that you want the first column to be invisible and sets explicit column
widths for the other columns.
The second column — in this case, the organization name — is the one against which the user’s
text input is matched. The first visible column in the combo box is always used for this purpose.
Figure 19.4 shows the resulting drop-down list. Although this is a rather extreme example of load-
ing a combo box with data, it effectively illustrates the power of the Access combo-box control.
When working with combo boxes, if you set the Limit to List property to Yes, the user is
required to choose from only the entries in the drop-down list. You can then construct an event
procedure for the control’s NotOnList event to handle what should happen if a user enters a
value not in the list. You might want to open a form into which the user can enter new informa-
tion; or perhaps you want to display a message box that instructs the user what procedure to fol-
low to add data.
