Access Subform SUM() Displays #Error for calculated field on main Access Form 1

[Tottihope]

I am trying to calculate the Grand Total for my Order Items on my main order form using the =SUM() function. It displays on the subform itself but errors out on the main form. I find it hard to figure out where I went wrong after many days and my Acces skills are beginner level. Solutions suggested in the link bellow have not solve the problem. =SUM([Quantity]*[Price]) or =SUM(NZ([Amount],0))

OrderID is the PK in the order form and orderDetailID is the PK in the Order Details subform.

Any Help would be highly appreciated.
I have attached the microsoft access file.

I am using Access 2013 on Windows 10.enter image description here

 

[kjv1611]

I did not download your file to look, but I wonder if it has something to do with the linking between the main form and subform. If it works in the subform by itself but not main form, that tells me it has to do with the linking.

For instance, if you built the form to sum all sales for one client, but the main form pulls one sales record at a time, then the subform would then not be able to pull ALL sales records. In that case it would either error out or give you just the value for that one sale.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

You need to reference the name of the subform control, not the Source Object.

Change the Control Source of Text36 to:

=[Order Details].[Form]![totalPrice]

=Sum([Amount]) will not work in the main form since Amount is not a field in the main form's record source.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Tottihope]

Thanks a lot dhookom for your solution, That cleared the confusion on another aggregate which the total bill on that order.
But I was also interested in the Grand Total which is the SUM() of all order or subtotal.
I would highly appreciate a hint on how to go about pulling that Grand Total off.

Regards

 

[dhookom]

If you want the Sum of Amount for a set of records, that set of records must be in the current form's record source in order to use =Sum([Amount]).

You have some options:
1) use DSum()
=DSum("Amount","Your table or query name","Optional Where Condition")
2) use a subform with a record source of a totals query with the sum of Amount
3) use code to create a recordset that you can grab the sum and display it in an unbound text box.
4) write your own function similar to DSum() that you can use as a control source:
=MyFunctionName("maybe some arguments")

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016