Microsoft KB Archive/99704

= How to Implement the DLookup Function in Visual Basic =

Article ID: 99704

Article Last Modified on 1/9/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q99704



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 = &quot;Lookup&quot; Label           Label2 Data            Data1      Databasename = &quot;BIBLIO.MDB&quot; RecordSource = &quot;Authors&quot; 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 &quot;Error (&quot; & Err & &quot;): &quot; & Error(Err) & &quot; in DLookup&quot;, 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(&quot;Title&quot;, &quot;Titles&quot;, &quot;Au_ID = &quot; &        Format(data1.Recordset(&quot;Au_ID&quot;))) 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

</li> From the Run menu, choose Start (ALT, R, S) or press F5 to run the program.</li> Click the directional arrows on the Data control to display different author names in Label1.</li> Click the Lookup button and title to display one of the author's books in Label2.</li></ol>

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(&quot;Author&quot;, &quot;Authors&quot;, &quot;Au_ID = 17&quot;)

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(&quot;Title&quot;, &quot;Titles&quot;, &quot;ISBN = '0895886448'&quot;) BookTitle2 = DLookup(&quot;Title&quot;, &quot;Titles&quot;, &quot;Au_Id = 17&quot;)

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: KB99704

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.