DLookup coding help!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've set up a main form "Companies" with the city field using a DLookUp
expression, and it works fine. Here it is...
=DLookUp("[City]","Zip Codes","[ZipCode]='" &
Forms!Companies!SitessubformControl!PhysicalZip & "'")

Here's the problem. I am also wanting to use a similar expression in a
subform "Sites". Here's the expression I'm trying to use...
=DLookUp("[City]","Zip Codes", "[ZipCode]= '" & Forms![Companies]![Sites
subform]![PhysicalZip] & "'")

I have tested the different parts of the expression, and the separate parts
work fine. When I put it all together, I get a Null value returned.

Can anyone tell me what I'm doing wrong?

Thanks.
 
The problem is that only main forms are members of the Forms collection. A
subform needs to be referenced via its container control on the main form.
The container control has a Form property which points to the actual form
object:
Forms!Companies!SitesSubformControl.Form!PhysicalZip

However, if the PhysicalZip control is actually on the (sub)form where
you're referring to it, then it is in the current scope, so you don't need
to refer to it "from the outside":

=DLookUp("[City]","Zip Codes", "[ZipCode]= '" & [PhysicalZip] & "'")
 
Back
Top