Microsoft Office Tutorials and References
In Depth Information
3. Click the right side of the Data Type text box for the CustomerID field to display
the list of data types, and then click Lookup Wizard . A message box appears,
warning you to delete the relationship between the tblCustomer and tblContract
tables if you want to make the CustomerID field a lookup field. See Figure 5-31.
Warning message for an existing table relationship
When you click or select
text in many Access text
boxes, Access displays an
arrow, which you can click
to display a list box with
options. You can display
the arrow and the list box
simultaneously if you click
the text box near its
Access will use the lookup field to form the one-to-many relationship between the
tblCustomer and tblContract tables, so you don’t need the relationship you previ-
ously defined between the two tables.
4. Click the OK button, close the tblContract table, and then click the No button
when asked if you want to save the table design changes.
5. Click the Database Tools tab on the Ribbon, and then, in the Show/Hide group on
the Database Tools tab, click the Relationships button to open the Relationships
6. Right-click the join line between the tblCustomer and tblContract tables, click
Delete , and then click the Yes button to confirm the deletion.
Trouble? If the Delete command does not appear on the shortcut menu, click a
blank area in the Relationships window to close the shortcut menu, and then
repeat Step 6.
7. Close the Relationships window.
Now you can resume changing the CustomerID field to a lookup field.
To finish changing the CustomerID field to a lookup field:
1. Open the tblContract table in Design view.
2. Click the right side of the Data Type text box for the CustomerID field, and then
click Lookup Wizard . The first Lookup Wizard dialog box opens.
This dialog box lets you specify a list of allowed values for the CustomerID field in
a record in the tblContract table. You can specify a table or query from which users
select the value, or you can enter a new list of values. You want the CustomerID
values to come from the qryCustomersByName query.
3. Make sure the option for looking up the values in a table or query is selected, and
then click the Next button to display the next Lookup Wizard dialog box.
4. Click the Queries option button in the View section to display the list of queries,
click Query: qryCustomersByName , and then click the Next button to display the
next Lookup Wizard dialog box. See Figure 5-32.
The leftmost column in this
dialog box contains the
values that will appear in
the table datasheet for the
lookup field. If you need to
rearrange the order of the
columns, you can click a
column heading and drag
it to the left or to the right.