Calculating values in a sub form

[owizard]

Hi,

I have two tables (Say X,Y) in a very simple inventory system. Table X has imported values from an Excel spreadsheet. The fields include Actual_Stock and Ave_Cost.
Table Y has Count1, Count2 and Count3 fields.

I have a form (D1) with the subform(D1Sub)for entering the values. Both forms are connected through productID. It also has calculated values (not stored in the database).

The calculations work OK as long as they involve values only in either the main form or the subform.

Ex: Counted_Stock in D1Sub

Counted_Stock=Nz([Count1])+Nz([Count2])+Nz([Count3])

However, I can't make calculations based on values drawn from both forms simultaneously. For instance, the following doesn't work.

Counted_Value =[Counted_Stock]*[AVE COST]

I have tried the following by moving the calculation control to the main form header.

Counted_Value=Forms![D1Sub subform]!Counted_Stock*D1![AVE COST]

It still doesn't work. Any info will be deeply appreciated. Thanks a lot.

 

[MoiraT]

When you are referring to a control on a subform you need to show the full path to that control as Access sees the subform as another control on the main form. For example if your result is in the subform:

=[Forms]![MainForm]![Subform].[Form]![ControlOnSubform]+[Forms]![MainForm]![ControlOnMainForm]

If result is on the main form then:

=[Forms]![MainForm]![Subform].[Form]![ControlOnSubform]+[ControlOnMainForm]

Hope that helps