#Name? error on text box control in Access 2003 form

[Guest]

I have a text control on a form whose control source is the following query
which is named qryPrimaryEmbossing2:

SELECT sEmbossing
FROM tblPrimaryEmbossing
WHERE iEmbossingIndex=Forms!frmMyInsulators![Embossing Variation]
And iCD=Forms!frmMyInsulators!CD;

It is meant to look up the text string sEmbossing using the the [Embossing
Variation] and CD numbers on the form.

If the form is open and I run the query (manually), it always returns the
correct results.

I have looked at all of the entries in help for the #Name? error and don't
see any that apply to my tables and form.
The query name in the control source for the text box is spelled correctly,
has no leading spaces and has an "=" i.e.,
=[qryPrimaryEmbossing2]![sEmbossing]
There are no duplicate field names in the underlying tables.

Can someone please give me some other ideas to fix this? I have run out of
ideas.

Thanks

 

[Guest]

Use DlookUp to return a value from the query

=DlookUp("sEmbossing","qryPrimaryEmbossing2")

 

[Guest]

Thank you Ofer - that works!

Use DlookUp to return a value from the query

=DlookUp("sEmbossing","qryPrimaryEmbossing2")

--
Good Luck
BS"D

I have a text control on a form whose control source is the following query
which is named qryPrimaryEmbossing2:

SELECT sEmbossing
FROM tblPrimaryEmbossing
WHERE iEmbossingIndex=Forms!frmMyInsulators![Embossing Variation]
And iCD=Forms!frmMyInsulators!CD;

It is meant to look up the text string sEmbossing using the the [Embossing
Variation] and CD numbers on the form.

If the form is open and I run the query (manually), it always returns the
correct results.

I have looked at all of the entries in help for the #Name? error and don't
see any that apply to my tables and form.
The query name in the control source for the text box is spelled correctly,
has no leading spaces and has an "=" i.e.,
=[qryPrimaryEmbossing2]![sEmbossing]
There are no duplicate field names in the underlying tables.

Can someone please give me some other ideas to fix this? I have run out of
ideas.

Thanks