• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

dLookup - a better way

Presently I have the following code in an Access 2003 DB

        Me.OSVendorDisplay = DLookup("Vendor", "tCl", "CL = " & "'" & Me.OSBrand & "'")
        Me.OSTypeDisplay = DLookup("Type", "tCl", "CL = " & "'" & Me.OSBrand & "'")
        Me.OSRebate = DLookup("Rebate", "tCl", "CL = " & "'" & Me.OSBrand & "'")
        Me.OSvCode = DLookup("vCode", "tCl", "CL = " & "'" & Me.OSBrand & "'")


Is this the best way to perform various data retrieval from a table or might there be a better way?

Thank you.
0
thandel
Asked:
thandel
  • 2
  • 2
  • 2
  • +2
1 Solution
 
GrahamMandenoCommented:
Yes, there is definitely a better way.  Open a Recordset containing your target record, read the four field values, and close the Recordset:
Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset( "Select * from tCl where CL = '" _
                  & Me.OSBrand & "'", dbOpenForwardOnly)
With rs
    Me.OSVendorDisplay = !Vendor
    Me.OSTypeDisplay = !Type
    Me.OSRebate = !Rebate
    Me.OSvCode = !vCode
    .Close
End With
Set rs = Nothing

Open in new window

Best wishes,
Graham Mandeno [Access MVP since 1996]
0
 
MacroShadowCommented:
Use ELookup by Allen Browne (http://allenbrowne.com/ser-42.html) it's arguments are the same as DLookup but it has many advantages as mentioned in the aforementioned link, it uses a recordset but is a lot easier to maintain.
0
 
thandelAuthor Commented:
Thanks Grahm, I am testing it but getting an error if it can't find the data match in the table.  Is there a workaround?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PatHartmanCommented:
Put a stop in the code at the set statement.
Copy and past the SQL string to the immediate window so you can see what it actually looks like.  If you still can't see the syntax error, paste the string into the SQL view of the QBE where you frequently get better messages.
0
 
thandelAuthor Commented:
OK I must say I know VBA but I'm not a power user.  I'm not following what you would like me try.  I'm sorry for the hastle.

How to I put a stop in the code?  Do you mean a code break?  What variable should I be looking at?
0
 
GrahamMandenoCommented:
If there is no match for CL=Me.OSBrand then you will get an empty Recordset with no records.  You can check for this by inspecting the EOF property:
Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset( "Select * from tCl where CL = '" _
                  & Me.OSBrand & "'", dbOpenForwardOnly)
With rs
    If .EOF Then
        Me.OSVendorDisplay = Null
        Me.OSTypeDisplay = Null
        Me.OSRebate = Null
        Me.OSvCode = Null
    Else
        Me.OSVendorDisplay = !Vendor
        Me.OSTypeDisplay = !Type
        Me.OSRebate = !Rebate
        Me.OSvCode = !vCode
    End If
    .Close
End With
Set rs = Nothing

Open in new window


Allen Browne's ELookup is certainly superior to DLookup, but each call will create a Recordset, open it, read ONE value, and close it again.  By reading FOUR values from the one Recordset your code will be at least ten times faster.

-- Graham
0
 
MacroShadowCommented:
Allen Browne's ELookup is certainly superior to DLookup, but each call will create a Recordset, open it, read ONE value, and close it again.  By reading FOUR values from the one Recordset your code will be at least ten times faster.

-- Graham

Wouldn't it be only 3 times faster?

Two advantages of ELookup over retrieving all values at once via a recordset are, 1. Easier to maintain, because the code is so much shorter. 2. No learning curve for a newbie, just copy and paste the ELookup function in a new module and forget about it, the change a occurrences of DLookup to ELookup.
0
 
Gustav BrockCIOCommented:
You can use one call with DLookup and Split:

Dim aValues As Variant
aValues = DLookup("[Vendor] & ';' & [Type] & ';' & Str([Rebate]) & ';' & [vCode]", "tCl", "CL = " & "'" & Me.OSBrand & "'")

If Not IsNull(aValues) Then
   Me.OSVendorDisplay = Split(aValues, ";")(0)
   Me.OSTypeDisplay = Split(aValues, ";")(1)
   Me.OSRebate = Val(Split(aValues, ";")(2))
   Me.OSvCode = Split(aValues, ";")(3)
End If

/gustav
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now