is there a Dlookup to get entire record or multiple fields ??

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me. here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID = "
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with an
array? or something as they are all from the same record in the same table..

Thanks

Barry
 
What you've posted should be ok.

If you wanted, you could concatenate the fields' values into a single
string, with a delimiter between each value, in a DLookup, then parse out
the values, though I'm not sure if it'll be better for you.

These lines of code

InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")


Could be changed to this:

Dim varArray As Variant
Dim strValues As String
Dim strDelim As String
strDelim = "|"
strValues = DLookup("Nz([installTSN], ""UNK"") & strDelim &
Nz([installTSO], ""UNK"") & strDelim & Nz([RemovalParentTT], ""UNK"") &
strDelim & Nz([InstallParentTT], ""UNK"") & strDelim & Nz([ParentTRKID],
""UNK"")", "T_installhistory", "installID = " & InstallID)
varArray = Split(strValues, strDelim)
InstallTSN = varArray(0)
InstallTSO = varArray(1)
RemovalParentTT = varArray(2)
InstallparentTT = varArray(3)
ParentTRKID = varArray(4)
 
Barry said:
I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me. here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID = "
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with an
array? or something as they are all from the same record in the same table.
Click to expand...

Generally, it would be better to use a recordset something
like:

Dim strSQL As String

strSQL = "SELECT installID, installTSN, InstallTSO, " _
& "RemovalParentTT, InstallParentTT, " _
& "ParentTRKID, SerialNumber " _
& "FROM T_installhistory INNER JOIN T_Serialnumbers
" _
& "ON T_Serialnumbers.TRKID =
T_installhistory.ParentTRKID " _
& "WHERE T_installhistory.TRKID = " _
& Me![TRKIDCombo] _
& " AND RemovalDate = #" & RmvDate & "#"

With CurrentDb.OpenRecordset(strSQL)
'use any field with syntax like !InstallTSO
. . .
End With
 
Thanks for the quick response.
Yes the code i supplied works fine. it just seems odd to have to dlookup a
bunch of seperate times if all of the data i want is in the same record.. and
i seem to get the impression on here that dlookups are hogs so maybe im just
a little paranoid..

Ken Snell said:
What you've posted should be ok.

If you wanted, you could concatenate the fields' values into a single
string, with a delimiter between each value, in a DLookup, then parse out
the values, though I'm not sure if it'll be better for you.

These lines of code

InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")


Could be changed to this:

Dim varArray As Variant
Dim strValues As String
Dim strDelim As String
strDelim = "|"
strValues = DLookup("Nz([installTSN], ""UNK"") & strDelim &
Nz([installTSO], ""UNK"") & strDelim & Nz([RemovalParentTT], ""UNK"") &
strDelim & Nz([InstallParentTT], ""UNK"") & strDelim & Nz([ParentTRKID],
""UNK"")", "T_installhistory", "installID = " & InstallID)
varArray = Split(strValues, strDelim)
InstallTSN = varArray(0)
InstallTSO = varArray(1)
RemovalParentTT = varArray(2)
InstallparentTT = varArray(3)
ParentTRKID = varArray(4)


--

Ken Snell
http://www.accessmvp.com/KDSnell/


Barry A&P said:
I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me.
here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = "
&
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = "
&
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID =
"
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID =
"
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with an
array? or something as they are all from the same record in the same
table..

Thanks

Barry
Click to expand...


.
Click to expand...
 
Ken can i use the suggestion you made and somehow change the dlookups to a
SQL like the following?? im sure my syntax is wrong expecially with the d-max
in the where clause(can i do that)??


SELECT T_InstallHistory.TRKID, T_InstallHistory.ParentTRKID,
T_InstallHistory.InstallDate, T_InstallHistory.InstallparentTT,
T_InstallHistory.InstallTSN, T_InstallHistory.InstallTSO,
T_InstallHistory.RemovalDate, T_InstallHistory.RemovalParentTT,
T_InstallHistory.ReasonForRemoval
FROM T_InstallHistory
WHERE (((T_InstallHistory.TRKID)=[Me]![TRKIDCombo]) AND
((T_InstallHistory.RemovalDate)=Nz(DMax("[RemovalDate]","T_installhistory","TRKID = " & [Me]![TRKIDCombo]),"#0#")));

Thanks for any help

Barry




Ken Snell said:
What you've posted should be ok.

If you wanted, you could concatenate the fields' values into a single
string, with a delimiter between each value, in a DLookup, then parse out
the values, though I'm not sure if it'll be better for you.

These lines of code

InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")


Could be changed to this:

Dim varArray As Variant
Dim strValues As String
Dim strDelim As String
strDelim = "|"
strValues = DLookup("Nz([installTSN], ""UNK"") & strDelim &
Nz([installTSO], ""UNK"") & strDelim & Nz([RemovalParentTT], ""UNK"") &
strDelim & Nz([InstallParentTT], ""UNK"") & strDelim & Nz([ParentTRKID],
""UNK"")", "T_installhistory", "installID = " & InstallID)
varArray = Split(strValues, strDelim)
InstallTSN = varArray(0)
InstallTSO = varArray(1)
RemovalParentTT = varArray(2)
InstallparentTT = varArray(3)
ParentTRKID = varArray(4)


--

Ken Snell
http://www.accessmvp.com/KDSnell/


Barry A&P said:
I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me.
here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = "
&
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = "
&
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID =
"
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID =
"
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with an
array? or something as they are all from the same record in the same
table..

Thanks

Barry
Click to expand...


.
Click to expand...
 
Barry

Are you saying that you want to retrieve the values of multiple fields in a
single record? If so, look into creating a recordset using a query (check
Access HELP on recordsets).

I'm a little unclear on your underlying table structure. Perhaps you are
trying to use multiple DLookups because it represents a work-around to get
the data you want from a table structure that needs further normalization?

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
DLookup in a query is notoriously slow. Try this instead (using your same
syntax):

SELECT T_InstallHistory.TRKID, T_InstallHistory.ParentTRKID,
T_InstallHistory.InstallDate, T_InstallHistory.InstallparentTT,
T_InstallHistory.InstallTSN, T_InstallHistory.InstallTSO,
T_InstallHistory.RemovalDate, T_InstallHistory.RemovalParentTT,
T_InstallHistory.ReasonForRemoval
FROM T_InstallHistory
WHERE (((T_InstallHistory.TRKID)=[Me]![TRKIDCombo]) AND
((T_InstallHistory.RemovalDate)=Nz(SELECT Max([RemovalDate])
FROM T_installhistory WHERE TRKID = " & [Me]![TRKIDCombo]),0));

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Barry A&P said:
Ken can i use the suggestion you made and somehow change the dlookups to a
SQL like the following?? im sure my syntax is wrong expecially with the
d-max
in the where clause(can i do that)??


SELECT T_InstallHistory.TRKID, T_InstallHistory.ParentTRKID,
T_InstallHistory.InstallDate, T_InstallHistory.InstallparentTT,
T_InstallHistory.InstallTSN, T_InstallHistory.InstallTSO,
T_InstallHistory.RemovalDate, T_InstallHistory.RemovalParentTT,
T_InstallHistory.ReasonForRemoval
FROM T_InstallHistory
WHERE (((T_InstallHistory.TRKID)=[Me]![TRKIDCombo]) AND
((T_InstallHistory.RemovalDate)=Nz(DMax("[RemovalDate]","T_installhistory","TRKID
= " & [Me]![TRKIDCombo]),"#0#")));

Thanks for any help

Barry




Ken Snell said:
What you've posted should be ok.

If you wanted, you could concatenate the fields' values into a single
string, with a delimiter between each value, in a DLookup, then parse out
the values, though I'm not sure if it'll be better for you.

These lines of code

InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID =
" &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID =
" &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID
= "
& InstallID), "UNK")


Could be changed to this:

Dim varArray As Variant
Dim strValues As String
Dim strDelim As String
strDelim = "|"
strValues = DLookup("Nz([installTSN], ""UNK"") & strDelim &
Nz([installTSO], ""UNK"") & strDelim & Nz([RemovalParentTT], ""UNK"") &
strDelim & Nz([InstallParentTT], ""UNK"") & strDelim &
Nz([ParentTRKID],
""UNK"")", "T_installhistory", "installID = " & InstallID)
varArray = Split(strValues, strDelim)
InstallTSN = varArray(0)
InstallTSO = varArray(1)
RemovalParentTT = varArray(2)
InstallparentTT = varArray(3)
ParentTRKID = varArray(4)


--

Ken Snell
http://www.accessmvp.com/KDSnell/


Barry A&P said:
I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me.
here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID
= "
&
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID
= "
&
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory",
"installID =
"
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID
=
"
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with
an
array? or something as they are all from the same record in the same
table..

Thanks

Barry
Click to expand...


.
Click to expand...
Click to expand...
 
DLookup in my opinion should be a last resort.
You are looking up fields in the same table using the same lookup value.
The proper way to this would be to relate the tables
and set values in another table *ONLY* if you have to.
Descriptions, ID information, etc. will not change so those fields are used
from the related table.
If a price from the other table is obtained, it might be stored since it
might change over time.
 
Marshall
Yes!!! a Recordset,, that is exactly what i was trying to get to,,
thanks for throwing in the "'use any field with syntax like !InstallTSO"

Here is the code got me to where i needed to go.

Dim strSQL As String
strSQL = "SELECT T_InstallHistory.InstallID, T_InstallHistory.TRKID,
T_SerialNumbers.SerialNumber AS Parent, " & _
"T_InstallHistory.InstallDate, T_InstallHistory.InstallparentTT,
T_InstallHistory.InstallTSN, T_InstallHistory.InstallTSO, " & _
"T_InstallHistory.RemovalDate, T_InstallHistory.RemovalParentTT,
T_InstallHistory.ReasonForRemoval, " & _
"[RemovalParentTT]-[InstallParentTT] AS TimeinService,
[InstallTSN]+[TimeInService] AS TSN, " & _
"[InstallTSO]+[TimeInService] AS TSO" & _
"FROM T_SerialNumbers " & _
"RIGHT JOIN T_InstallHistory ON T_SerialNumbers.TRKID =
T_InstallHistory.ParentTRKID " & _
"WHERE (((T_InstallHistory.InstallID)= " & InstallID & "))"

With CurrentDb.OpenRecordset(strSQL)
InstallTSN = !InstallTSN
InstallTSO = !InstallTSO
RemovalParentTT = !RemovalParentTT
InstallparentTT = !InstallparentTT
Me!RemovedFrom = !Parent
TotalTIS = !TimeinService
End With

you saved me 8 dlookups and a ton of coding
Thank You
I will add this to my toolbox
Barry

Marshall Barton said:
Barry said:
I am using Dlookup to get a records key value then i am using 4 seperate
Dlookups with that key value to do some math in VBA and its killing me. here
are my lookups

InstallID = DLookup("[installID]", "T_installhistory", "TRKID = " &
Me![TRKIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentTRKID = Nz(DLookup("[ParentTRKID]", "T_installhistory", "installID = "
& InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers", "TRKID = "
& ParentTRKID), "UNK")

Is there a more efficient way to get these values into the code? with an
array? or something as they are all from the same record in the same table.
Click to expand...

Generally, it would be better to use a recordset something
like:

Dim strSQL As String

strSQL = "SELECT installID, installTSN, InstallTSO, " _
& "RemovalParentTT, InstallParentTT, " _
& "ParentTRKID, SerialNumber " _
& "FROM T_installhistory INNER JOIN T_Serialnumbers
" _
& "ON T_Serialnumbers.TRKID =
T_installhistory.ParentTRKID " _
& "WHERE T_installhistory.TRKID = " _
& Me![TRKIDCombo] _
& " AND RemovalDate = #" & RmvDate & "#"

With CurrentDb.OpenRecordset(strSQL)
'use any field with syntax like !InstallTSO
. . .
End With
Click to expand...
 
Back
Top