How to Implement the DLookup Function in Visual Basic

- Microsoft Visual Basic Programming System for Windows, version 3.0

SUMMARY

Microsoft Access provides a set of domain, or record set, functions that are useful in getting the value of one field based on criteria involving another field. The DLookup domain function is particularly useful.

Although Visual Basic does not contain the DLookup function, you can write the equivalent using Visual Basic code. This article describes how to implement the DLookup domain function in Visual Basic.

MORE INFORMATION

In Microsoft Access, the DLookup domain function returns the value of a field for a given set of criteria. The syntax for the DLookup function is as 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

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.

Examples Showing How to Use DLookup

In the following example, from the Authors table in the Visual Basic BIBLIO.MDB sample database, DLookup uses the Au_ID field to return the corresponding author name for the author whose ID is 17. Assume that the variable AuthorName is a string.

   AuthorName = DLookup("Author", "Authors", "Au_ID = 17")

   BookTitle1 = DLookup("Title", "Titles", "ISBN = '0895886448'")
   BookTitle2 = DLookup("Title", "Titles", "Au_Id = 17")