?
Solved

dLookup - a better way

Posted on 2013-12-18
8
Medium Priority
?
555 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 38

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 51

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

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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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