Control Source error "#Name?" on subform

Phenomena

New member
Local time
Today, 09:42
Joined
Oct 31, 2013
Messages
8
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:

Code:
=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
 
try =IIf(IsNull([SummaryLine]![Price]),[Material]![DefaultPrice],[SummaryLine]![Price])
 
I'm still getting the #Name? error.
 
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

Code:
=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
 
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

Code:
=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

Code:
=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?
 

Users who are viewing this thread

Back
Top Bottom