Dlookup Problem in Access 2007

[David Stone]

I am currently testing office 2007. I have created a form in Access 2007
(using access 2003 mdb) with a large number of Dlookup statements within the
fields control source. (i.e. Lookups based on Client Id; Site Id; Employee ID
etc.) It seems that the first set of lookups work, however further lookups
do not work. I then saved the database and loaded it within Access 2003. The
problem disappeared, therefore is there a problem with dlookup function
within Access 2007 or perhaps is there a better way of processing dlookups.

 

[Allen Browne]

I don't see a reason why it should work for some but not others, unless
something is invalid in the expression.

For example, if you have:
=DLookup("MyField", "MyTable", "[ID] = " & [ID])
that will work unless the ID text box on your form is null.
It is is, the criteria argument becomes just:
[ID] =
Access can't make sense of that, and so returns #Error.

You can solve that kind of issue with Nz(), e.g.:
=DLookup("MyField", "MyTable", "[ID] = " & Nz([ID],0))

As for a better solution, yes there probably is a better way. Each DLookup()
is slow, so when you have many of them the form updates at a snail's pace.

Some alternatives:
a) Bind the form to the table. You can now read the fields directly.

b) If the form is already bound to a table, you may be able to bind it to a
query, so you have the fields of the other table as well.

c) If you cannot do that either, you may be able to create a subform bound
to the other table, and use the subform to display the related fields. (The
speed gain is that this is a single read, not one for each DLookup().)

d) If that won't work either, leave the fields unbound. In the Current event
of the form, OpenRecordset to get all the fields you need in one go, and
assign the values to the unbound text boxes.