Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

dLookup - a better way

Posted on 2013-12-18
8
Medium Priority
?
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

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
 

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 2000 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 27

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 52

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

609 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