Create a text box on a form that looks up a value in a table

When you design a form in Access, you may want to display a value from a table or query other than the one that your form is bound to. For example, suppose that you have a Products form that is bound to a Products table. After you design the form, however, you decide that you want the form to display the name of the supplier contact for each product — data that resides in a table called Suppliers. The following procedure shows you how to use the DLookup function to accomplish this task. You can alter the expression in step 6 to suit your own data.

Add the text box

1. In the Navigation Pane, right-click the form that you want to change, and then click Design View on the shortcut menu.

2. On the Design tab, in the Controls group, click Text Box.

3. Locate the area in the form in which you want to add the control, and then drag the pointer on the form to create the text box.

4. Right-click the text box, and then click Properties on the shortcut menu.

5. In the property sheet, click the All tab.

6. Set the Control Source property to the following value:

   =DLookup("[ContactName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

7. Save your form changes, and then close the property sheet.

This expression searches in the Suppliers table and returns the contact name of the supplier whose supplier ID matches the value in the SupplierID control on the Products form. Note how the & operator is used to construct the third argument of the DLookup function (the third argument is "[SupplierID]=" & Forms!Products!SupplierID). A common error that you should avoid is placing quotation marks around the entire argument instead of around only the text that precedes the & operator.

Note: As an alternative to using the DLookup function, you can create a new underlying query to include only the information that you need. Using a query is often more efficient than using a function.