Form/Sub Form formula

[darrenay]

Hi all,

I have a subform "Line Items" embedded in my main form "Transaction". The Line Items subform has a field "Amount Payable" and I am trying to sum this up for all the line items. The calculated filed would be named Sub Total. However, i kept getting an error message "#ERROR".

Main form: Transaction
Subform: Line Items
Subform field: Amount Payable
Main form field: Sub Total

The formula I used in the control source of Sub Total is:
=[Line Items Subform].Form!Amount Payable

Can someone advise what went wrong please?

Many thanks in advance!

 

[RuralGuy]

Embedding spaces in names just gives you grief down the road. Try:
=[Line Items Subform].Form![Amount Payable]

 

[darrenay]

Hi RuralGuy,

Thanks for the tip. I have now removed all the spaces in my field names.

Adding the square brackets didn't help. I did a test to see what is wrong and I think I have narrowed it to SUM.

I tested with formula: =[Subform1].[Form].[Quantity]

I managed to get a number returned.

But when i added SUM, i.e. =Sum([Subform1].[Form].[Quantity])

I get #ERROR message.

Would you know why this is happening?

 

[d_profesor]

That's happened to me too, don't know why.

But you can try this:
in the subform1 footer create new unbound textbox named txtTotQty with formula
=sum([Qty]), set subform1.visible=false

then in your main form set subtotal=subform1.Form!txtTotQty

 

[RuralGuy]

Access in its infinite wisdom defaults the name of a control to the same name as the control source for the control. Then when you reference it as we are doing here, Access gets confused as to what you want, the control or the field to which it is bound. I (almost) *always* preface my Control Names with the type of control (txtJunk, cboJunk, lstJunk) then even I know what I'm referencing.

 

[RuralGuy]

Here's a link to something closer to the point of this thread: http://www.mvps.org/access/forms/frm0022.htm
Sorry, just got carried away.