Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

DLookup and “NiceDLookup”

by | Jul 12, 2024 | MS Access, MS Access VBA Coding | 2 comments

A lot of Access users (especially newer ones) like to use the function DLookup to quickly lookup a value from a table or query. It is quite handy shorthand and can be used in VBA and in queries.

The usage of DLookup is:

DLookup (Expr, Domain, Criteria)

Or to put in more Access friendly terms:

Expr is a field name you want.

Domain is a table or query name.

Criteria is what you’d put in the WHERE clause to filter the results.

So you could do something like this to return the phone number of an Employee named “Jon” from the Employees table.

Debug.Print DLookup("PhoneNumber","Employees","FirstName='Jon'")
Visual Basic

This is little more readable and easy to type than something like this:

Debug.Print CurrentDb.OpenRecordset("SELECT PhoneNumber FROM Employees WHERE FirstName='Jon'")(0)
Visual Basic

Those two statements would return the same result. They are both doing essentially the same thing, opening a new database object, executing a SQL command, and returning the result of that command.

Now the first thing to understand about that is:

  1. It’s a very expensive operation.

So this would be very slow to do 1000 times in a loop compared to simple VBA operations.

  1. It also does not immediately release the database object it creates.

This is something you’d likely never know unless you’ve experienced the dreaded “Out of Resources” error, but DLookup doesn’t release the database object it creates after it’s done. Garbage collection in Access seems to clean up after it, but calling it recursively in queries or in long code loops can eventually garner you a nice error message which is not at all clear.

To fix this condition, as well as allow a few extra features (like grouping functions and table joins) we have written this function that we use in our HAL library in our Access JumpStart product and affectionately call “Nice” DLookup (NiceDLookup) It tries to behave in a similar manner as far as what it returns in terms of error messages and values and nulls, but it cleans up after itself after each call. Here it is for your viewing pleasure:

' ----------------------------------------------------------------
' Procedure Name: NiceDLookup
' Purpose: Replace the DLookup function so that the new CurrentDB
'          instance will be explicitly closed and released at termination
' Procedure Kind: Function
' Procedure Access: Public
' Parameter Expr (String): {same as DLookup}
' Parameter Domain (String): {same as DLookup}
' Parameter Criteria (Variant): {same as DLookup}
' Return Type: Variant {same as DLookup} Null if no record
'              satisfies criteria or if domain contains no records
' Author: jon
' Date: 2/3/2022
' Modified: 2/9/2022; Throw same error codes as DLookup
' ----------------------------------------------------------------
Public Function NiceDLookup(Expr As String, Domain As String, Optional Criteria As Variant) As Variant
   Dim DbCurrent As DAO.Database, rsTemp As DAO.Recordset
   Dim sCriteria As String, strSQL As String
   Dim newErrNum As Long
   Dim newErrDesc As String

   newErrNum = 0
   NiceDLookup = Null
   On Error GoTo ProcError
   If IsMissing(Criteria) Then
      sCriteria = ""
   ElseIf Criteria = Null Then
      sCriteria = ""
   Else
      sCriteria = Criteria
   End If
   Set DbCurrent = CurrentDb
   strSQL = "SELECT " & Expr & " FROM " & Domain & _
      IIf(sCriteria <> "", " WHERE " & sCriteria, "")
   Set rsTemp = DbCurrent.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
   If Not (rsTemp.EOF) Then
      NiceDLookup = rsTemp(0)
   End If
ProcExit:
   On Error Resume Next
   rsTemp.Close
   Set rsTemp = Nothing
   DbCurrent.Close
   Set DbCurrent = Nothing
   On Error GoTo 0  ' we will propagate errors as would DLookup
   If newErrNum <> 0 Then
      Err.Raise newErrNum, "NiceDLookup", newErrDesc
   End If
   Exit Function
ProcError:
   ' Process error numbers so that they will be the same as DLookup
   newErrDesc = Err.Description
   Select Case Err.Number
      Case 3061 ' "To few parameters" when Expr or Criteria field name is not found
         newErrNum = 2471  ' what DLookup throws instead
         'newErrDesc = "The expression you entered as a query parameter produced this error: '|'@@@2@1@11805@1"
         If sCriteria = "" Then
            newErrDesc = "The expression you entered as a query parameter produced this error: '" & Expr & "'"
         Else
            newErrDesc = "The expression you entered as a query parameter produced this error: '" & Expr & "' OR '" & Criteria & "'"
         End If
      Case 3078 ' when Domain not found
         newErrNum = 3078  ' same for DLookup
      Case Else ' hopefully everything else will be the same
         newErrNum = Err.Number
   End Select
   Resume ProcExit
End Function
Visual Basic

Bonus content: Eric Blomquist took the function and made a few modifications for his own library and sent me a copy. Check it out. He commented each change.

Function NiceDLookup(strExpr As String, strDomain As String, Optional ByVal strCriteria As Variant) As Variant
'	Jon Halder's DLookup() clone, revised and commented by Eric Blomquist, eb@bqco.com, eric.r.blomquist@gmail.com.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    'Dim strCriteria As String          'Declare the parameter ByVal to permit its revision w/o passback.
    Dim strSQL As String
    
    'NiceDLookup = Null                 'Do below.
    On Error GoTo ErrorProcedure
   
    If IsMissing(strCriteria) Then
        strCriteria = Null              'Exploit the Variant declaration to gain null propagation below.
    'ElseIf strCriteria = Null Then     'Retain any Null.
        'strCriteria = ""
    ElseIf (LenB(strCriteria) = 0) Then 'Handles vbNullString and ZLS (and Null), which a Variant can hold w/o being Missing.
        strCriteria = Null
    Else
        'No other cases.
    End If
   
    Set db = Access.Application.DBEngine(0)(0)  'A direct reference avoids the overhead of a CurrentDB method call, _
                                                 including cycles, memory, and the additional db connections this _
                                                 procedure is intended to conserve.
    'Substituting Null propagation for an IIF() call avoids the overhead of the latter:
    strSQL = "SELECT " & strExpr & " FROM " & strDomain & (" WHERE " + strCriteria)
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    
    If (rs.EOF) Then        'Initializing a Variant to Null early ordinarily is good practice _
                             but this structure consolidates the assignments concisely, which _
                             is more readable.  Also, avoids a "Not" condition, which is less so. _
                             Also, it conditions the Null assignment, avoiding it and its overhead _
                             when unneccessary.
        NiceDLookup = Null
    Else
        NiceDLookup = rs(0)
    End If
   
ExitProcedure:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    On Error GoTo 0  ' we will propagate errors as would DLookup
    If newErrNum > 0 Then                   'A Long can't be <0.
        Err.Raise newErrNum, "NiceDLookup", newErrDesc
    End If
    Exit Function
ErrorProcedure:
    ' Process error numbers so that they will be the same as DLookup
    Dim newErrNum As Long       'Doing here conditions the declaration on an error, conserving RAM.
    Dim newErrDesc As String

    newErrNum = 0
    newErrDesc = Err.Description
    Select Case Err.Number
        Case 3061 ' "To few parameters" when strExpr or strCriteria field name is not found
            newErrNum = 2471  ' what DLookup throws instead
            'newErrDesc = "The strExpression you entered as a query parameter produced this error: '|'@@@2@1@11805@1"
            If (LenB(strCriteria) = 0) Then     'Handles Null, vbNullString, and ZLS.
                newErrDesc = "The strExpression you entered as a query parameter produced this error: '" & strExpr & "'"
            Else
                newErrDesc = "The strExpression you entered as a query parameter produced this error: '" & strExpr & "' OR '" & strCriteria & "'"
            End If
        Case 3078 ' when strDomain not found
            newErrNum = 3078  ' same for DLookup
        Case Else ' hopefully everything else will be the same
            newErrNum = Err.Number
    End Select
    Resume ExitProcedure
End Function    'NiceDLookup()
Visual Basic

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    2 Comments

    1. mor10
      mor10 on September 5, 2024 at 9:56 am

      Nice, but if there are spaces in either the table or field name, the function will return nothing or an error. I know it may be unprofessional with spaces in these names, but Access do allow it.

      • Jonathan Halder
        Jonathan Halder on September 5, 2024 at 10:12 am

        Hi mor10! If you enclose your table name in brackets, this function will handle table names with spaces and special characters. That’s how Access handles them in the background with queries and in other expressions like using Forms!Form Name, you would have to use Forms![Form Name] in order for it to work.

        Like this: [My Table Name With Spaces]

        But I see you are right in terms of matching functionality with DLookup. DLookup requires an existing table or query object and won’t allow you to do fancy joins and SQL things in the domain argument, but if you just put the name of the table with spaces or special characters it doesn’t care if you use brackets.

        To make this a drop in replacement for this functionality, I could add a check at the beginning to see if the whole string is an existing table or query and if so, add brackets around it. Otherwise, run it as is. That would be relatively easy since I have a table exists function and a query exists function already.

        I will do that for today’s message!

    Trackbacks/Pingbacks

    1. Deftype statements in VBA – What? | Access JumpStart - […] Before continuing further, just wanted to give a shout out to Eric Blomquist who replied to my NiceDLookup article…
    2. Access Object Names With Spaces and NiceDLookup | Access JumpStart - […] DLookup and “NiceDLookup” | Access JumpStart […]

    Submit a Comment