Need help with DLookup() works on subform but not master form

[Guest]

Hi all:
I am using the following DLookup() function on a subform and it works....as
in it shows the data I expect it to when I view the subform in "form view" by
itself and not as part of the master form. When I view the master form the
Dlookup up function returns "#Name?".

Can anyone help me by telling me why it would return this value? I assume
it has something to do with the fact that I am working with a master and
subform and think the problem is with the
"[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])"

DLookup() function being used:
=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

Details:
Master form Name: frmColmHeader
Subform Name: frmColmHeaderSubformColmDetail
Unbound control on subform Name: VarietyDLookup
qryIntakeLookup: is the query that contains the data I am looking for -
Variety
Variety & IntakeID: are both fields in the query qryIntakeLookup

Any help would be greatly appreciated.

Thanks,
FatMan

 

[Guest]

If you referencing controls on a sub form from a form, you must include a
reference to the form so Access will know where to find it.

=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[Forms]![frmColmHeader]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
Me![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

is also acceptable.

 

[Allen Browne]

Try:
=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[ColmDetailIntakeID])

Access should be able to find the object named ColmDetailIntakeID if it is
on the same form, without the full Forms!xxx reference.

Subforms are not open in their own right (i.e. they are not part of the
forms collection.) That's why the reference works as a stand-alone form, but
not as a subform.

 

[Guest]

Klatuu:
Thank you for your reply. I am sure it would work but I have gone with
Allene Brown's suggest as it is shorter and easier.

Once again thank you for your reply.
FatMan

If you referencing controls on a sub form from a form, you must include a
reference to the form so Access will know where to find it.

=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[Forms]![frmColmHeader]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
Me![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

is also acceptable.

Hi all:
I am using the following DLookup() function on a subform and it works....as
in it shows the data I expect it to when I view the subform in "form view" by
itself and not as part of the master form. When I view the master form the
Dlookup up function returns "#Name?".

Can anyone help me by telling me why it would return this value? I assume
it has something to do with the fact that I am working with a master and
subform and think the problem is with the
"[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])"

DLookup() function being used:
=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

Details:
Master form Name: frmColmHeader
Subform Name: frmColmHeaderSubformColmDetail
Unbound control on subform Name: VarietyDLookup
qryIntakeLookup: is the query that contains the data I am looking for -
Variety
Variety & IntakeID: are both fields in the query qryIntakeLookup

Any help would be greatly appreciated.

Thanks,
FatMan

 

[Guest]

Allen:
Thanks for the solution. It is right on target.

You are an Access Guru.

Thanks
FatMan

Try:
=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[ColmDetailIntakeID])

Access should be able to find the object named ColmDetailIntakeID if it is
on the same form, without the full Forms!xxx reference.

Subforms are not open in their own right (i.e. they are not part of the
forms collection.) That's why the reference works as a stand-alone form, but
not as a subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all:
I am using the following DLookup() function on a subform and it
works....as
in it shows the data I expect it to when I view the subform in "form view"
by
itself and not as part of the master form. When I view the master form
the
Dlookup up function returns "#Name?".

Can anyone help me by telling me why it would return this value? I assume
it has something to do with the fact that I am working with a master and
subform and think the problem is with the
"[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])"

DLookup() function being used:
=DLookUp("[Variety]","[qryIntakeLookup]","[IntakeID]=" &
[Forms]![frmColmHeaderSubformColmDetail]![ColmDetailIntakeID])

Details:
Master form Name: frmColmHeader
Subform Name: frmColmHeaderSubformColmDetail
Unbound control on subform Name: VarietyDLookup
qryIntakeLookup: is the query that contains the data I am looking for -
Variety
Variety & IntakeID: are both fields in the query qryIntakeLookup