How to Implement the DLookup Function in Visual Basic

ID: Q99704

The information in this article applies to:

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

The following steps show by example how to create a Visual Basic custom DLookup 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 a field value such as book title based on the value of another field such as author 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 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")

If the criteria argument contains non-numeric text other than field names, you must enclose the text in single quotation marks. In the following 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 only one field. If no record satisfies criteria, or if the domain contains no records, DLookup returns a Null.

Additional query words: 3.00

Keywords :
Version :
Platform :
Issue type :