How to Sum a total from a subform in a Text Box on the Main Form

[DunnRight]

I am a novice at best. I have a project that I am working on in which I am trying to turn a template I found online into something I can use for my job. I work for a company that houses inventory and sells some of that inventory to customers. I have a subform on my main form that lists items sold to a specific customer and tracked by a sales order number. What I am having problems with is getting a text box on the main form to calculate the total value of the parts sold on that order. I am not sure what other info anyone may need to help me out here, but I would greatly appreciate any help offered.

[atuhacek]

I normally put a total field on the subreport pagefooter. Just a text box name txtTotal set the control property =Sum([yourfield]) make sure that the field name and text box name are different or it wil error.

then on your main for put another textbox named txtGrandTotal and set its control property to =Reports!YourSubReport!txtTotal

[ItsMe]

If your form is based on a query, it may be as simple as turning that Query Object into a Totals query. There is an option within the Ribbon (while in Design View of the Query Object) you can click. It is labeled "Totals" with an upper case Sigma.

Once you have selected the Totals option you can add the Field you want to sum to your query. Employ the pulldown provided within the grid (bottom of the window) to select "Sum".

[DunnRight]

I am still have difficulty getting this to work. Here is a shot of the Main Form. I need the extended price of all parts sold, which are listed in the sub form, to be totaled in the Text Box "Order Subtotal" at the bottom of the main form. I have tried what atuhacek suggested which, for me produced a blank text box rather than the error I had before. Any other suggestions? I can provide more info if anyone needs it.

[June7]

Since this is a form, not a report, don't use Reports in the reference (but that should have erred anyway).

What is name of subform container control? I always name the container control different from the object it holds, like ctrDetails. Then try expression:

=[ctrDetails]!txtTotal

[DunnRight]

So here is a shot of the sub form.

[June7]

The syntax I suggested works for me.

Your image shows properties of the textbox, still don't know the name of the subform container control.

[atuhacek]

You correct June7 that would have errored I forgot to address the container. And the total field goes in the page footer.

with a subReport container As [Child26] and total field [txtMaterial]

=Nz(Sum([Total_Bill]),0)

this will return the total the above generates

=IIf(IsError([Child26].[Report]![txt_MaterialTotal]),0,Nz([Child26].[Report]![txt_MaterialTotal]))

If you put a text box on your sub Form to generate the total you can call it like the above. Change the Reports to Form and the fields to your control names. Make sure your fields and controls have different names.

[June7]

The syntax does not require the [Report] (or [Form]) reference. The simpler syntax I posted works. If want to handle possible Null (although don't really see the need to):

=Nz([ctrDetails]![txtTotal],0)

[Missinglinq]

If your form is based on a query, it may be as simple as turning that Query Object into a Totals query...

The problem with this is that Totals Queries are always Read-Only, so the Subform itself would be Read-Only.

ExtendedPrice is a Calculated Field

=[Qty]*[UnitPrice]

and Aggregate Functions cannot be run against Calculated Fields. Instead of

=Sum([ExtendedPrice])

you have to use Sum against the calculation, itself:

=Sum([Qty]*[UnitPrice])

Then, on the Main Form, you have to reference this Control on the Subform, with syntax like this

=SubformControlName.Form!ControlNameOnSub

Linq ;0)>

[atuhacek]

@missinglinq Sum([Qty]*[UnitPrice]) Is correct

I normally use a total field "Total As [Qty]*[UnitPrice]" then Sum the total field.

@June7 I believe have to name a child explicitly you can't just name the control in the child

[June7]

My example references the container control and the textbox. I tested it with my db and it worked.

[atuhacek]

Ok. I always use formal to avoid confusion. But you are right it is implied.

[Missinglinq]

Ok. I always use formal to avoid confusion. But you are right it is implied.

Just to clarify, the secret to using June7's syntax of

=[ctrDetails]!txtTotal

is that the name of the Subform Control must be different from the name of the Form that is the Source Object of the Subform Control, and that the ctrDetails in his code is the name of the Subform Control.

Linq ;0)>

[DunnRight]

I appreciate all the help, but I am still doing something wrong. Here is a shot of the subform.


Here is a shot of the main form while i have the container selected for the subform.


I am having problems getting the "Order Subtotal" and the "Order Total" to work. Although, I am pretty sure the "Order Total" may not be working since the "Order Subtotal" is not working.