Subform Total in Main Form

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:

Me.SubformName.Form!txtTotal

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
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
=======================================================================
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?
0
Derek BrownMDAuthor Commented:
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!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

tInvoiceLineItems
----------------------------
ID
InvoiceID
ItemID
ItemQty
ItemPrice
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Derek BrownMDAuthor Commented:
Hi

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.
Capture.PNG
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
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?
0
Derek BrownMDAuthor Commented:
Fabulous.

Many thanks.

derek
0
Derek BrownMDAuthor Commented:
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?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Derek BrownMDAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The correct syntax is

Forms!MainForm!NameOfYourSubformCONTROL.Form.ControlName

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).
0
Derek BrownMDAuthor Commented:
Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.