How to get the grand total in main form from subform

Question

This is the screenshot of how the final output should look.

However, in the screenshot you will find that purchase total (calculated text label) is the sum total of the first order description Total (calculated at form level): Final Form view section display

The above screenshot result is the output of this formula

=[OrderDescription_tbl].[Form]![Total]

If I use this formula

=Sum([OrderDescription_tbl].[Form]![Total])

The output is #Error.

Troubleshooting I did,

1. I had created a grand total at the footer of the Subform using the Sum function and visible parameter set to No and wanted to reference the output to the Main form Purchase total. How to solve this problem?
2. However, in the screenshot you will see the number of boxes count (Not calculated and is a table column), I used the same Sum() function in the order_description subform. It gives the correct output. Why is there a difference in output for the same function one, against the Non-calculated table column and the other, a calculated form text label?

3. I have used this formula too

   =IIf([OrderDescription_tbl].[Form]![Total]>0,Sum([OrderDescription_tbl].[Form]![Total]),0)
   

Output is still #Error

4. I have used an query which calculates sum total of each line item and tried to reference this query as a)=Sum([Query1]![Expr1]) received #Error b)[Query1]![Expr1] received #Name?

Answer 1

Thanks! I have been able to solve it using the existing structure only. In this regard I referenced Northwind database.Although,my database would have been more specific and different from Northwind,some aspects from Northwind did give me hints on what to change. The trick was - I had several lookups fixed on numerical columns like unit_rate(and this unit_rate referred a product portfolio table containing product offering of our company).Once, I removed the lookup aspect of these column values, #Error as well as Name? error were automatically resolved. I created a grand total with Visible parameter set to No inside the order description subform and referenced the output of this form to the grand_total text label in the outer form as seen in the screenshots shared.😀 Thank you for the support@Ren Kessler and I will share this is Stackoverflow too so that others can take advice from my case scenario. Ren Kessler I will use your method in some other way,may be in this development scenario but for some other context. I found it quite interesting and I havent tried it. Apart from my reply, I would like to quote you and your solution method too.Total_Calculation_subforminternal_view_Expression_builder_for_grandtotal_textlabel

This is the solution Given by Ren Kessler(Facebook group member):" I solved it in a very convoluted way. I hope someone has a better solution than mine.

I created a series of fields on my form (not fields in a table, but just on the form) that reference the fields on the subform. Then I performed all my math using these form fields. It was the easiest way I could find.

It seems clumsy, but works. As well, it made it easy to use these fields on a report (in my case, an invoice).

In previous versions I did not have to do this. I took a 2002 app where the math worked on the sub form, and used the exact same steps in 2016 and got #error, just like you. This system solved it, but it just doesn't seem right."

Answer 2

As an alternative to the DSum solution proposed by Kostas, you can use the following workaround:

• in the footer of the subform, insert a textBox with the =Sum([Total]) formula, name the textBox BigTotal (or whatever you want)
• in the main form, refer to that control: =mySubFormControl.Form!BigTotal
• make the subform's footer hidden if you want.

If you have large data sets, this should be faster than the DSum. Also if the subform has an embedded query as its recordSource, you avoid the creation of a queryDef.

Answer 3

Try the DSum() function:

=DSum("[Total]",[OrderDescription_tbl].[Form].[RecordSource])