ACC: How to Use DLookup() to Look Up Values in Forms/Reports

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You can display multiple fields in a combo or list box on a form or report even when those fields come from a table that is not bound to the form or report. You can also update controls, such as text boxes, with new information based on what a user selects from a combo or list box.

To accomplish these objectives, use one of these three techniques:

• Use AutoLookup in forms.
• Use multiple DLookup() functions in forms and reports. See the "More Information" section later in this article.
• Use the Column property of a multiple-column combo box to update a text box control with new information as focus moves from row to row in the combo box.

MORE INFORMATION

DLookup() Function

The syntax for the DLookup() function is a follows:

   DLookup(expr, domain, [criteria])

The following example looks in the Employees table (the domain) and returns the Last Name of the Employee ID specified in the [ControlName] control on the Form.

In Microsoft Access 7.0 and 97

   =DLookup("[LastName]", "Employees", "[EmployeeID] =_
            Forms![FormName]![ControlName]")

In Microsoft Access 1.x and 2.0

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   =DLookup("[Last Name]", "Employees", "[Employee ID] =_
            Forms![FormName]![ControlName]")

DLookup() returns one value from a single field even if more than one record satisfies the criteria. If no record satisfies the criteria or if the domain contains no records, DLookup() returns Null.

REFERENCES

For more information about the DLookup() function, search the Help Index for "DLookup," or ask the Microsoft Access 97 Office Assistant.