Dlookup() text field Blank in a continuous Form?

  • Thread starter Thread starter PeteP
  • Start date Start date
P

PeteP

Hi,

I have a continuous form that displays a list of orders with various
bits of individual order information.

The orders table only stores ID values and needs to reference these
values from other tables. Originally, I attempted to join to these
tables, but were prevented from performing an update to the subsequant
resordset.

It was suggested that I use DLookup() to return the values and all
appeared to work fine. However, when i open the form using VB and limit
the displayed Recordset by programmatically amending the WHERE clause,
all the DlookUp() values are returing blanks.

An Example of a Dlookup is:

=DLookUp("[dtblLookupCodes]!vchLUDescription","dtblLookupCodes","
[ActionTypeID] = [IntLUId] ")

I have tried Requerying and Refreshing the form to no avail. Can anyone
assist?
 
In the 3rd argument, concatenate the value into the string, e.g.:
=DLookUp("vchLUDescription","dtblLookupCodes", "[ActionTypeID] = " &
[IntLUId])

If ActionTypeID is a Text type field (not a Number type field), you need
extra quotes:
=DLookUp("vchLUDescription","dtblLookupCodes", "[ActionTypeID] = """ &
[IntLUId] & """")

If it is a number, the first example may give you an error when the IntLUId
control is blank, so once you get it working it would be better to use:
=DLookUp("vchLUDescription","dtblLookupCodes", "[ActionTypeID] = " &
Nz([IntLUId],0))
 
PeteP said:
Hi,

I have a continuous form that displays a list of orders with various
bits of individual order information.

The orders table only stores ID values and needs to reference these
values from other tables. Originally, I attempted to join to these
tables, but were prevented from performing an update to the subsequant
resordset.

It was suggested that I use DLookup() to return the values and all
appeared to work fine. However, when i open the form using VB and limit
the displayed Recordset by programmatically amending the WHERE clause,
all the DlookUp() values are returing blanks.

An Example of a Dlookup is:

=DLookUp("[dtblLookupCodes]!vchLUDescription","dtblLookupCodes","
[ActionTypeID] = [IntLUId] ")
Click to expand...


Dlookup has no idea what the value of intLUId is. I don't
either, but I'll guess that it's a bound text box on the
form. If that's correct, then try using one of these. If
the ID value is a numeric type:

=DLookUp("[dtblLookupCodes]!vchLUDescription",
"dtblLookupCodes", "[ActionTypeID] =" & [IntLUId])

or if the ID is a text type:

=DLookUp("[dtblLookupCodes]!vchLUDescription",
"dtblLookupCodes", "[ActionTypeID] =""" & [IntLUId] & """")
 
Hi,

Thank you both for your responses! :o)

Your suggestions, while not immediately fixing the issue, did at least
point me towards my own coding issue. Changing the Dlookup() to match
your text revealed the #Name? error, leading me to realise that I had
grabbed the wrong darn field in the RecordSource to begin with...

So while I'm blushing that I missed my own mistake, I'm still greatful
for your assistance which pushed me toward the solution.


Many Thanks
 
Back
Top