Control Source error "#Name?" on subform (1 Viewer)

[Phenomena]

I have a control on a subform that I need to populate with values from one of two possible tables.

The sub form is is populated from a table called SummaryLine. SummaryLine has a field called Price.

I want the control txtPrice to get it's value from SummaryLine.Price, unless Price is empty. If it's empty I want it to pull the value from Material.DefaultPrice field.

To do this I tried using the following expression:

=IIf([SummaryLine]![Price]=Null,[Material]![DefaultPrice],[SummaryLine]![Price])

I'm still pretty new to Access, but to my understanding that expression is saying:

IF SummaryLine.Price is null THEN get Material.DefaultPrice ELSE get SummaryLine.Price.

Yet I'm getting an error in the control "#Name?".

Any help would be appreciated.

i.imgur.com/D4e9Mya.jpg

 

[Foe]

try =IIf(IsNull([SummaryLine]![Price]),[Material]![DefaultPrice],[SummaryLine]![Price])

 

[Phenomena]

I'm still getting the #Name? error.

 

[CJ_London]

Is the code to go into your control source of an unbound control for price or to be calculated in the recordsource for the subform?

If the former then in your control source put the following

=nz(Price,dlookup("DefaultPrice","Material","MaterialID=" & materialID))

Note change materialID to the appropriate names

if the latter then in your recordsource I would left join your summaryline table to the material table on materialID? and in your query

SELECT ....., nz(summaryline.price,material.price) AS myPrice, .... FROM SummaryLine LEFT JOIN Material ON summaryline.materialID=material.materialID

 

[Phenomena]

Is the code to go into your control source of an unbound control for price or to be calculated in the recordsource for the subform?

If the former then in your control source put the following

=nz(Price,dlookup("DefaultPrice","Material","MaterialID=" & materialID))

Note change materialID to the appropriate names

if the latter then in your recordsource I would left join your summaryline table to the material table on materialID? and in your query

=nz(Price,dlookup("DefaultPrice","Material","MaterialID=" & materialID))

Worked, thanks! This leads me to a new question though. On my form I have a checkbox labeled "Override default price" when this is checked I want txtPrice to be editable to allow the user to enter a new price. This worked before, but now I can't edit the field because the control source is set. Is there any way around this?