Query Problem

[Dudy]

I have a subform that brings up recipe ingredients.
The subform record source is Recipe Ingredients table.
I place a textbox on the subform that pull a calculation from the query.
The control source for the textbox is set to
=[UnitExt Query]![UnitCost]
but when i run the subform it comes up #NAME?
If I run the query the calculation is correct.
What am I doing wrong?

 

[John W. Vinson]

I have a subform that brings up recipe ingredients.
The subform record source is Recipe Ingredients table.
I place a textbox on the subform that pull a calculation from the query.
The control source for the textbox is set to
=[UnitExt Query]![UnitCost]
but when i run the subform it comes up #NAME?
If I run the query the calculation is correct.
What am I doing wrong?

Just the syntax. You can't refer to a query unrelated to the Form, or by query
name.

If the UnitExt Query is (or can be made) part of the Form's Recordsource, just
use the fieldname. If not, use the DLookUp function:

=DLookUp("[UnitCost]", "[UnitExt Query]", <some optional criteria>)

where the criteria string is a String consisting of a valid SQL WHERE clause
(without the word WHERE) which returns the unit cost that you want - perhaps

"[IngredientID] = " & [txtIngredientID]

if there's a control on the form with that name.

John W. Vinson [MVP]

 

[Guest]

Thanks for the info, suggestion, and example John. I encountered the same
problem today & your code worked perfectly.

-=Brian

I have a subform that brings up recipe ingredients.
The subform record source is Recipe Ingredients table.
I place a textbox on the subform that pull a calculation from the query.
The control source for the textbox is set to
=[UnitExt Query]![UnitCost]
but when i run the subform it comes up #NAME?
If I run the query the calculation is correct.
What am I doing wrong?

Just the syntax. You can't refer to a query unrelated to the Form, or by query
name.

If the UnitExt Query is (or can be made) part of the Form's Recordsource, just
use the fieldname. If not, use the DLookUp function:

=DLookUp("[UnitCost]", "[UnitExt Query]", <some optional criteria>)

where the criteria string is a String consisting of a valid SQL WHERE clause
(without the word WHERE) which returns the unit cost that you want - perhaps

"[IngredientID] = " & [txtIngredientID]

if there's a control on the form with that name.

John W. Vinson [MVP]