DLookup Question

[Guest]

Table: tblAccount
Field: TrackingNumber
Field: ClosedDate

Table: tblAccountActivity
Field: AutoNumber
Field: TrackingNumber (one to many relationship to tblAccount)
Field: Status (tblAccountActivity)

On the "After Update" event of the ClosedDate field, I am trying to do a
DLookup to see if if the tblAccountActivity table has a "Closed" status

This is what I have written:

Dim LookUpAccountStatus As Variant

LookUpAccountStatus = DLookup("[Status]", "[tblAccountStatusInfo]",
"[TrackingNumber] = Form.[TrackingNumber]")

If Not (IsNull([LookUpAccountStatus])) And [LookUpAccountStatus] = "Closed"
Then
MsgBox "........."
End If

Evidently this does not look at all the records (associated to the specific
tracking number in tblAccountStatus) to determine if there is a "Closed"
record. Is there a way to make this code loop to each record (within the
specified tracking number) to determine if there is or is not a "Closed"?

Thanks in advance for any help.

Rick

 

[Allen Browne]

Concatenate the value from the TrackingNumber field into the 3rd string in
DLookup():

LookUpAccountStatus = DLookup("[Status]", "[tblAccountStatusInfo]",
"[TrackingNumber] = " Me.[TrackingNumber])

If TrackingNumber is a Text type field (not a Number field), you need extra
quotes:
LookUpAccountStatus = DLookup("[Status]", "[tblAccountStatusInfo]",
"[TrackingNumber] = """ Me.[TrackingNumber] & """")

More info:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html