faster alternative to series of DLookups

[Klaus-Dieter Gundermann]

You may try the function ALookup:
-----------------------------------------------------------
Public Function ALookup(ByVal Expr As String, ByVal Domain As String, _
ByVal Criteria As String, _
ParamArray Vars()) As Boolean

On Error GoTo Err_ALookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Expr -> List of Field Names / Expressions
'Author: Allen Browne. (e-mail address removed)
'Modified by: KD Gundermann
'Examples: ok = ALookup("Field1, Field2, Field3","MyTable", _
' "Record=123", Var1, Var2, Var3)

Dim db As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Dim i As Integer

'Build the SQL string.
strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain & " WHERE " &
Criteria

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
ALookup = False
Else
For i = 0 To UBound(Vars)
If IsObject(Vars(i)) Then
Vars(i).Value = Nz(rs(i).Value)
Else
Vars(i) = Nz(rs(i).Value)
End If
Next
ALookup = True

End If
rs.Close

Exit_ALookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ALookup:
MsgBox Err.Description, vbExclamation, "ALookup Error " & Err.Number
Resume Exit_ALookup
End Function