This article was previously published under Q99704
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Microsoft Access provides a set of domain, or record set, functions thatare useful in getting the value of one field based on criteria involvinganother field. The DLookup domain function is particularly useful.
Although Visual Basic does not contain the DLookup function, you can writethe equivalent using Visual Basic code. This article describes how toimplement the DLookup domain function in Visual Basic.
In Microsoft Access, the DLookup domain function returns the value of afield for a given set of criteria. The syntax for the DLookup function isas follows:
DLookup(expr, domain , criteria)
Argument Description ---------------------------------------------------------------- expr String expression identifying the field that contains the data you want to return. Operands in expr can include the name of a table field. domain String expression identifying the records that constitute the record set. It can be a table name, query name, or SQL expression that returns data. criteria Optional string expression used to restrict the range of data on which DLookup is performed. For example, criteria could be the SQL expression's WHERE clause without the word WHERE. If criteria is omitted, DLookup evaluates expr against the entire record set.
Step-by-Step to a Custom Visual Basic DLookup Function
The following steps show by example how to create a Visual Basic customDLookup function.
Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
Add the following controls with the associated properties to Form1:
Control Name Property Settings ------------------------------------------------------------- Command Button Command1 Caption = "Lookup" Label Label2 Data Data1 Databasename = "BIBLIO.MDB" RecordSource = "Authors" Label Label1 DataSource = Data1 DataField = Author
Add the following code to the general declarations section of Form1:
Dim gDefaultDatabase As Database
Add the following code to the general section of Form1:
'Enter the following two lines as one, single line: Function DLookup (ByVal FieldName As String, ByVal RecSource As String, ByVal Criteria As String) As Variant Dim dsResult As Dynaset Dim ReturnValue As Variant On Local Error GoTo Error_DLookup: 'Create a dynaset based on the record source or SQL string provided Set dsResult = gDefaultDatabase.CreateDynaset(RecSource) 'Find the first record that meets the criteria provided dsResult.FindFirst Criteria 'See if we found any records If Not dsResult.NoMatch Then 'Return the value of the field DLookup = dsResult(FieldName).Value Else DLookup = Null End If DLookup_Exit: Exit Function Error_DLookup: 'Display the error and get out MsgBox "Error (" & Err & "): " & Error(Err) & " in DLookup", 64 Resume DLookup_Exit: End Function
Add the following code to the Command1_Click event procedure:
Sub Command1_Click () 'Get the first book title for the current author. 'Enter the following two lines as one, single line: Label2.Caption = DLookup("Title", "Titles", "Au_ID = " & Format(data1.Recordset("Au_ID"))) End Sub
Add the following code to the Form_Load event procedure of Form1:
'Cause the records to be read from the database. This is 'needed to initialize the Database property. data1.Refresh 'Keep the default database in a global variable to be used 'by the DLookup function Set gDefaultDatabase = data1.Database
From the Run menu, choose Start (ALT, R, S) or press F5 to run the program.
Click the directional arrows on the Data control to display different author names in Label1.
Click the Lookup button and title to display one of the author's books in Label2.
As demonstrated in this example program, you can use DLookup to return afield value such as book title based on the value of another field such asauthor ID.
Examples Showing How to Use DLookup
Below are some more examples showing how you can use the DLookup function.
In the following example, from the Authors table in the Visual BasicBIBLIO.MDB sample database, DLookup uses the Au_ID field to return thecorresponding author name for the author whose ID is 17. Assume thatthe variable AuthorName is a string.
If the criteria argument contains non-numeric text other than fieldnames, you must enclose the text in single quotation marks. In thefollowing example from the Titles table of the BIBLIO.MDB database,ISBN is the name of a field, and 0895886448 is a string literal.