Subform Total in Main Form

Posted on 2013-09-21
Medium Priority
Last Modified: 2013-09-28
Hi All

I have a subform that calculates a total from data in the subform. The main form also has calculated totals. I want the total from the subform to be part of the grand total on the main form.

I have been told that this should not be done programmatically as part of an event.

I had this from the original question: You can refer to a textbox in your subform containing the total in VBA like this:


This gets the total onto my main form but I need it in a bound control to keep a permanent record or I need it to be part of the main forms underlying query so that it can automatically form part of a query for later use, in a report for example.

I hope that that’s clear
Question by:DatabaseDek
  • 7
  • 5
LVL 86
ID: 39511429
The usual warning: There is rarely a situation when you need to store calculated data in a table. Exceptions might be if you need to keep a historical record - for example, if you need something like a "LastPrice" column, in which the Price is calculated and would change if your root values changed. In cases like that you should store a calculated value, but if your reason for doing this is to display it on a form, or to more easily use it in a query, then I'd suggest you rethink your design


You can't do that - if your control on the main form uses syntax like this as the ControlSource:

= Me.SubformName.Form!txtTotal

Then you can't directly "bind" that Textbox control to a Field in your Form's Recordset. You'd have to do this with one of the Form or Control events. It's hard to suggest which one, since we don't really know the purpose of the calculation, or when it's done, etc etc.

How is txtTotal calculated on your subform?

Author Comment

ID: 39511483
Thank you. An historical record is what is required. The full storey is this:

Combo boxes (Description and price) on the subform provide data from the current prices table who's values when selected are added to bound controls on the subform. The subform's query calculates total values based on current prices multiplied by the quantity required. The underlying query also adds all of the values from these numerous bound controls to achieve a price for all components. There are two such subforms on the main form and the addition of both sets of data need to be stored as an historical record preferably on the main form.

Not an easy one!
LVL 86
ID: 39511537
So this is something like an invoicing system?

If so, then you should store the current price of the item in your "Invoice Line Item" table. That table should not be associated with the "current price" table, other than to pull the "default" price for that item.

Something like this:

etc etc

So your ItemPrice * ItemQty would always give you the Line Item Total, and you could then recreate the cost of your invoice:

SELECT SUM(ItemQty * itemPrice) As InvoiceTotal FROM tInvoiceLineItems WHERE InvoiceID=YourInvoiceIDNumber

You could use another table to supply the "default" value - often you'd use a combo for the ItemID, and that combo would perform a lookup for the current Price of the item, based on user selection.

So it's fine to store the Price of an item - that's an attribute of that item, at that specific point in time - but there's still no reason to store the Invoice Total.

Or perhaps I've misunderstood.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 39511741

It is similar to an Invoice in that respect.

There are other controls on the main form that are the result of calculations in the underlying query and we have two subforms how do we arrive at a grand total for the totals from the two subforms and the main form so that the total appears on the main form in real time.  Does SELECT SUM do that? and does it go into a control on the form or the query?

The attached screen shot shows the main form with one of the subforms. (Top right, but the subform is meant to look like part of the main form)

The subform has a one to one relationship with the main form. The items being added are different fields and not the same structure as an item subform on an invoice. This is because the items being added are compulsory as opposed to additions or options.
LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39512771
SELECT SUM is used in a query:

SELECT SUM(Field1 * Field2) As MyCalculatedValue FROM MyTable

This would not go in a field, it would be run in a code module:

Dim rst As DAO.Recordset
Set rst = OpenRecords("SELECT SUM(Field1 * Field2) As MyCalculatedValue FROM MyTable WHERE YourIDValue=" & Me.YourIDField)

Me.YourPriceField = rst("MyCalculatedValue")

These are generally run in your Form's AfterUpdate event - and in this case, you'd probably run this in the Subform's AfterUpdate event. In cases like this, however, I generally build a Function on the mainform that will calculate the price, and just call that function when needed.

Author Comment

ID: 39512987
Thank you.

Can I assume that I can get a single value from a query instead of a table? by still using FROM MyQuery WHERE YourIDValue=" & Me.YourIDField

Does it go in the QBD grid or in the sql?

Author Closing Comment

ID: 39515502

Many thanks.


Author Comment

ID: 39520742
I'm getting an error on  Me.ItemNumber below. It's looking for a sub or function?

Dim rst As DAO.Recordset
Set rst = OpenRecords("SELECT(GCost + GICost) As TVPPrice FROM VisionPanels WHERE ItemNumber =" & Me.ItemNumber)

Me.VPTotals = rst("TVPPrice")

I have spent some time with this

SELECT SUM(Field1 * Field2) As MyCalculatedValue FROM MyTable in the query grid. I take it that it has to go in the top line but wondered where it will link the ItemNumber's from the query's table and the VisionPanels Table?
LVL 86
ID: 39522593
Your SELECT statement needs a space between the SELECT and the parentheses.

Me.ItemNumber would generally refer to a Control on a Form. Are you running this code on a Form? Or a Subform?

I take it that it has to go in the top line but wondered where it will link the ItemNumber's from the query's table and the VisionPanels Table?
I'm not sure what you mean by this.

Author Comment

ID: 39523802
Running from a Subform. So it should be Forms!MainForm!Subform.Form![ItemNumber] ?

"I take it that it has to go in the top line but wondered where it will link the ItemNumber's from the query's table and the VisionPanels Table?"

 I mean in the Select statement needs to be placed in the Field row rather than the criteria row.

The statement is asking for the data from VisionPanels> how will it select the correct row from that table without a WHERE clause   Where Itemnumber = Subforms ItemNumber

Thank you for comming back.
LVL 86
ID: 39523914
The correct syntax is


Where NameOfYourSubformControl is the name of the Subform CONTROL on the main form. This may or may not be the same as the form you're using as a subform.

HOwever, if you're building this with the Query designer, then you'd select the table, drag your fields to the grid, and then click the "Totals" button. You'd then see the "Total" row, and you select the correct aggregate method for that row (Group By, Sum, Average, etc).

Author Comment

ID: 39530169
Thank you!!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question