DLookup

[Dan M]

Did the domain functions change after Access 97? Used to
be, you could reference a form control in a DLookup
function to provide criteria. Now (in an Access XP
project) the control containing the DLookup function
returns #Error.

I'm trying to have a control on my form display a value
from a field, but that field is not part of my form's
recordsource. In fact, it can't be, as any method I use
to include it causes the recordsource to become non-
updateable. In 97, I simply used a DLookup function. In
Access XP, it doesn't work, returning #Error.

Is there more information available regarding some of
these strange differences between versions, especially
differences between Access 97 databases and Access XP
projects?

 

[Allen Browne]

AFAIK, there was no change to DLookup() in recent versions.

Typically, you need to concatenate the value of the control into the 3rd
argument, e.g.:

=DLookup("SomeField", "SomeTable", "Surname = """ &
[Forms]![Form1][txtSurname] & """")

Omit the extra quotes if the field type is Number. In this case you need to
consider whether the text box could be null, e.g.:
=DLookup("SomeField", "SomeTable", "MyNum = &
Nz([Forms]![Form1][txtSurname],0))