Solved

dLookup - a better way

Posted on 2013-12-18
8
462 Views
Last Modified: 2013-12-19
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
Comment
Question by:thandel
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39728319
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39728321
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
 

Author Comment

by:thandel
ID: 39728332
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39728342
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:thandel
ID: 39728345
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
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 39728679
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39728715
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39728750
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now