MS Access how to SUM a Form with Subform?

Question

Hi,

I have two tables: CustomerTable and SalesInvoiceTable where I created a Form (CustomerTable) and Subform (SalesInvoiceTable).

I would like to see the SUM of the SalesInvoiceAmount per Customer. Keep getting error.

Answer 1

Create a text box named txtSum in the header or footer of the subform.

Set the Control Source of this text box to =Sum([SalesInvoiceAmount]) and set its Visible property to No.

Create a text box named txtSum on the main form.

Set its Control Source to =[SubformName]![txtSum] where SubformName is the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the navigation pane. Click once on the subform in the main form and check the Name property on the Other tab of the Property Sheet. This is the name to use.

Answer 2

To explain the answer, you have put the sum control in the MAIN form. But the Controlsource is:

SUM(SalesInvoiceAmount). But the Mainform doesn't know what SalesInvoiceAmount is sinced its on the subform. That's why you have to do the sum in the Subform, then reference it on the main form.

Answer 3

Alternatively, use a Continuous Forms view subform and sum the SalesInvoiceAmount column in the subform's footer.  For an example see DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes a simple orders form in which the total of all items per order is summed in a subform's footer.