How to Implement the DLookup Function in Visual Basic

This article was previously published under Q99704
This article has been archived. It 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.
  1. 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.
  2. 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						
  3. Add the following code to the general declarations section of Form1:
       Dim gDefaultDatabase As Database						
  4. 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						
  5. 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						
  6. 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						
  7. From the Run menu, choose Start (ALT, R, S) or press F5 to run the program.
  8. Click the directional arrows on the Data control to display different author names in Label1.
  9. 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.
   AuthorName = DLookup("Author", "Authors", "Au_ID = 17")				

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.
   BookTitle1 = DLookup("Title", "Titles", "ISBN = '0895886448'")   BookTitle2 = DLookup("Title", "Titles", "Au_Id = 17")				

Even if more than one record satisfies criteria, DLookup returns onlyone field. If no record satisfies criteria, or if the domain contains norecords, DLookup returns a Null.

Article ID: 99704 - Last Review: 12/04/2015 09:28:43 - Revision: 1.1

Microsoft Visual Basic 3.0 Professional Edition

  • kbnosurvey kbarchive KB99704