By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

DLookUp of multiple records in a query

P: n/a
I have a field that retrieves information from an expression in a
query. I have used a DLookup function to get the calculated field
from the query. However, the relationship is 1-to-many so one site
could have many units. How do I have the DLookUp field value change
to a newly calcuated field when I navigate through the units? please
see example below:

On the Form:
A site can have many units.

On the Subform:
Each unit has a calculated field, EM, from qryUnit.
On the subform, I use =DLookUp("[EM]","qryUnit","[SiteNum] =" &
[Forms]![frmSiteAsset]![SiteNum])to retrieve the units that are
particular to the site I am looking at. When I navigate through the
different units for the same site, it keeps the first "EM" value that
it finds from the query. The first "EM" value does that change at all
when I navigate to different units. I need to show each "EM" value
that is specific to each unit.

Any ideas? Thanks in Advance.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Karen,

Domain aggregrate functions (DLookup, DMax, DMin, etc.) only return the *FIRST* matching
record. That's the way they are designed to work.

There was tip printed in Access-VB-SQL Advisor magazine, in the July 2002 issue on page 12
by Mike Groh, titled "A better DLookup Function". This function does not have the same
first matching record limitation of Domain Aggregate functions. I believe Trevor Best
also has a "TLookup" function available on his web site:

http://easyweb.easynet.co.uk/~trevor....htm#baslookup
Tom

Watch for word wrap!
______________________________________________

Option Compare Database
Option Explicit

Public Function MyDLookUp(Expr As String, Domain As String, _
Criteria As String) As Variant
' From Access-VB-SQL Advisor magazine, July 2002, Page 12
' This function returns all hits, one at a time, instead of just the first matching record
' like DLookUp does. Continue calling this function until it returns Null.

' Example:
' ?MyDLookUp("LastName", "tblEmployees", "City = 'Seattle'")
' will return Davolio the 1st time it is called, Callahan the 2nd time it is called, and
Null the
' 3rd time it is called whereas DLookUp("LastName", "tblEmployees", "City = 'Seattle'")
' will only return the first matching record, which is Davolio.

' Examples with compound criteria:
' ?MyDLookUp("LastName", "tblEmployees", "[City] = 'Seoul' OR [City] = 'London'")
' ?MyDLookUp("ProductName", "tblProducts", "[UnitPrice] BETWEEN 30.00 AND 32.00")
Static sExpr As String
Static sDomain As String
Static sCriteria As String
Static sRS As DAO.Recordset

' If Expr, Domain or Criteria terms have changed, then set static recordset variable
' to nothing. Otherwise, retain the values for successive calls of the function.
If sExpr <> Expr Then
sExpr = Expr
Set sRS = Nothing
End If

If sDomain <> Domain Then
sDomain = Domain
Set sRS = Nothing
End If

If sCriteria <> Criteria Then
sCriteria = Criteria
Set sRS = Nothing
End If

' Either open a new recordset or move to the next record in the recordset,
' depending on the value of the static recordset variable.
If sRS Is Nothing Then
Set sRS = CurrentDb.OpenRecordset( _
"SELECT " & sExpr _
& " FROM " & sDomain _
& " WHERE " & sCriteria, _
dbOpenSnapshot)
Else
sRS.MoveNext
End If

If sRS.EOF Then
Set sRS = Nothing
MyDLookUp = Null
Else
MyDLookUp = sRS(Expr)
End If

End Function

______________________________________________

"KLAU" <ka*******@mail.utexas.edu> wrote in message
news:57**************************@posting.google.c om...

I have a field that retrieves information from an expression in a
query. I have used a DLookup function to get the calculated field
from the query. However, the relationship is 1-to-many so one site
could have many units. How do I have the DLookUp field value change
to a newly calcuated field when I navigate through the units? please
see example below:

On the Form:
A site can have many units.

On the Subform:
Each unit has a calculated field, EM, from qryUnit.
On the subform, I use =DLookUp("[EM]","qryUnit","[SiteNum] =" &
[Forms]![frmSiteAsset]![SiteNum])to retrieve the units that are
particular to the site I am looking at. When I navigate through the
different units for the same site, it keeps the first "EM" value that
it finds from the query. The first "EM" value does that change at all
when I navigate to different units. I need to show each "EM" value
that is specific to each unit.

Any ideas? Thanks in Advance.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.