Solved

dLookup - a better way

Posted on 2013-12-18
8
534 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

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 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 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 50

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

707 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