Total function is not working

[MNNovice]

I am trying to get a grand total amount from a sub form to a main form. The text box on the sub form is called "Total". I created a text box on the main form and used =Sum([Total]) to get the grand total. However, it returns #Error message.

What am I doing wrong?

[timber910]

You need to call the total from the sub form to the main form.

Post your code around this field from the sub form and what your trying to put it into on your main form.

I am trying to get a grand total amount from a sub form to a main form. The text box on the sub form is called "Total". I created a text box on the main form and used =Sum([Total]) to get the grand total. However, it returns #Error message.

What am I doing wrong?

[NeoPa]

Refer to Referring to Items on a Sub-Form for help with this. Let us know if you still have problems.

[ADezii]

I am trying to get a grand total amount from a sub form to a main form. The text box on the sub form is called "Total". I created a text box on the main form and used =Sum([Total]) to get the grand total. However, it returns #Error message.

What am I doing wrong?

You simply cannot reference the Aggregate Expression in the SubForm from the Main Form. In the Orders/Orders SubForm Pair in the Northwind Sample Database, the Order SubTotal is calculated in the Orders SubForm in the following manner:

=Sum([ExtendedPrice])

To achieve this same result in the Main Form (Orders) would require:

=DSum("[ExtendedPrice]","Order Details Extended","[OrderID]= " & [Forms]![Orders]![OrderID])

Does this make sense to you?

[NeoPa]

Fair point ADezii. I missed that.

Just reread the OP and realised it shows a value of #Error, rather than any error message as such.

[MNNovice]

Hello all:

Thanks for your input. I will read and try to understand how to better write this formula. Meanwhile I solved my problem this way:

Main form: frmGrants
Sub form: sfrmGrantBudget
to record aggregate figures from the line items of the sub form a text box was added to the footer: txtTotal
To record the grand total in the main form's footer the text box is called: txtGrandTotal

When I entered this formula in the control for txtTotal: =Sum([Total]) I got yet another error message (#Error). Well, I then found out I cannot use a control's name with the Sum function. I changed the formula to read as:


=sum([BudgetAmount]) + sum([ChangeAmount]) (BudgetAmount and ChangeAmount are two fields on my sub form)

and then added this to the main form's text box (txtGrandTotal)

=sfrmGrantBudge t.Form!txtTotal

So this is what I did and it solved my problem.

Thanks for your help.

[NeoPa]

This is actually a better solution, and was what I was intending to suggest. I realised however, that expressing this without any information as to what is available on your form would almost certainly just add further confusion.

Suffice to say, you've discovered a very good (and most correct) method of producing the result you want. This uses data already available to you rather than shooting off to the table repeatedly, using a Domain Aggregate function.

This should not be taken in any way as a criticism of ADezii's post, as he was just as much in the dark as I, so he gave the best option he could suggest from what we knew. Congratulations though, for coming up with such a good answer.

[MNNovice]

NeoPa:

Thanks for the compliments. You know it's only natural to have some of brilliance rub off of all these Tech Gurus (like you and others on Bytes.com) I am talking everyday...

Thanks.