Access VBA DLookup

This article will demonstrate how to use the DLookup Function in Access VBA.

The DLookup function can be used in Queries, calculated controls on a form or report, a macro or in VBA code to get a value of a particular field from a specified table or query.

DLookup in VBA Code

DLookup can be used within a VBA function to return a value to a form or report, or it can be used within  sub procedure.

Consider the following sub procedure:

Sub FindCompany()
  Dim strCompany As String
  strCompany = DLookup("Companyname", "tblClientCompanies", "CompanyID = " & 29)
  MsgBox strCompany
End Sub

DLookup looks up the name of a company in a table where the Company identification ID is equal to 29.

The syntax is as follows:

Expr as String – The name of the field that we are looking up eg: CompanyName.

Domain – The table or query that we are looking in eg: tblClientCompanies.

[Criteria] – The selection criteria that needs to be taken into account eg: the CompanyID = 29.

To use DLookup in a procedure, we need to return the value to a variable (strCompany as String in the example above) or return the value with a function:

Function GetCompany(lngCompanyID As Long) As String
  GetCompany = DLookup("Companyname", "tblClientCompanies", "CompanyID = " & lngCompanyID)
End Function

We can call the function from a sub procedure:

This returns a message box with the company name.

Alternatively, we could use the function within a form.

Consider the following form:

If we click on the unbound CompanyID text box, we can create an Event Procedure for the After Update event of that text box.

Select the Unbound Control and then, in the Property Sheet of the form, (1) select the Event tab and then (2) select Event Procedure in the drop-down list.   Then, click on the 3 little dots to the right of the drop down list to create the event procedure.

The Event Procedure will be created for you.  Type the following code into the event:

Private Sub CompanyID_AfterUpdate()
  Me.CompanyName = GetCompany(CompanyID)
End Sub

If you then change to Form view, you can type a company number into the Company ID text box and the Company Name will automatically pop up in the Company Name text box due to the DLookup function we created.