Sum Line Items from Subform on Main form

[Hey Lucy]

I have an Orders form (frm-Orders) that includes a subform (frm-Order Details Subform). The subform has line item totals.

I want to be able to sum the line item totals and show the result on the main form, but I can't get it to work. Seems like it should be an easy thing to do.

Someone suggested I try this but it didn't work:
In the footer of the subform I created this expression - =SUM([Line Item Total].

Then in the main form I created a textbox with this -- =[Forms]![frm.Orders].[Form]![frm-Order Details Subform].[Form]![txtSum].

How can I accomplish this simple task?

 

[highandwild]

I would use DSUM on the query upon which the form is based.

You can probably test it independant of the form and then just paste it in when you know it works.

 

[Hey Lucy]

Thanks High,

Won't work for me as both the frm-Order Details Subform and frm-Orders are based on tables. Hmmm.

 

[Mihail]

High's solution should work with tables as well as with queries. The same syntax.

Also try this:
=Forms![frm-Order Details Subform]!txtSum

Also, note that names like frm-Order Details Subform are a very very bad practice because the "minus" sign and because the spaces.

Why not frm_OrderDetailsSubform ?!?!?!?
This way, your formula can be simplified again:
=Forms!frm_OrderDetailsSubform!txtSum

 

[gemma-the-husky]

you could sum them on the subform, and then just read the value from the subform.

probably more efficient that way.


set the control datasource on the main form to

=mysubform!totalfield

 

[Hey Lucy]

Mihail thanks for your suggestion about my naming convention. Really never thought of that. Am going to rename all my objects.

 

[Hey Lucy]

Thanks for all the help! Finally got this to work by creating a query and using the Totals function to sum the line totals, then created my form based on this query. Now I am trying to figure out how to change that field name, since it automatically names the field (SumofLineItemTotal) when you use the Sum function.

In a 2nd query to pull orders by a specific client, I'd like that particular field name to be Order Total rather than SumofLineItemTotal. If anybody has any ideas, I'm open.

I just really want to thank all who offered solutions and suggestions! The people of this forum are the greatest!

 

[Hey Lucy]

Duh....no wonder I'm called Lucy! Change the field name in the Caption option of the properties.

 

[RainLover]

Lucy

Try this in your query.

NewName:SumofLineItemTotal

Also if you use the link in my signature there is an article on Naming Conventions. It may help you to create a standard naming convention of your own.

 

[Mihail]

Change the field name in the Caption option of the properties.

I strongly advice you to do NOT this.
Just replace the automatic name of what you wish.

You will have a lot of troubles if you change the caption property because at design time you will see a name, at run time other name... and I don't know what you see if you ever will use this query in another query or into a report.

 

[Hey Lucy]

Thanks all!

 

[RainLover]

Thanks all!

Hey Lucy.

It is good that you got back to say all OK, so many people don't.

It is even better when you say, Hey I used XYZ to solve the problem.

We advisers like to know when we are wrong or right. Well I do.

Happy Birthday.