How To Calculate A Subtotal In A Continuous Subform

[dr_destructo]

Hello all...this is my first time posting here. I'm relatively new at the whole Access game, but am working on an ordering guide for my work. The part that I'm stuck on, is that I have Form with client contact information. Within that form, there is a Subform with the order information. The Subform is continuous. Now in the footer, I have a subtotal textbox which isn't updating. Here's what I've tried so far:
=Sum([txtTotal])
=Sum(nz(txtToal]),0)
=Sum(([txtQty])*([txtPrice]))


None of these seem to work. Is what I'm trying to do even possible?

Many thanks in advance for your help.

[HMEpartsmanager]

Hello all...this is my first time posting here. I'm relatively new at the whole Access game, but am working on an ordering guide for my work. The part that I'm stuck on, is that I have Form with client contact information. Within that form, there is a Subform with the order information. The Subform is continuous. Now in the footer, I have a subtotal textbox which isn't updating. Here's what I've tried so far:
=Sum([txtTotal])
=Sum(nz(txtToal]),0)
=Sum(([txtQty])*([txtPrice]))
None of these seem to work. Is what I'm trying to do even possible?

Many thanks in advance for your help.

I have a text box in the footer area (of the subform) with this;

=Sum([ordlineQtyOrdered]*[ordlinePrice])


Then in my Form, I have this text box to display the result;

=[Purchase Orders - Sub].Form!OrdersSubFormSubTotal

This gives me a total of all items ordered, and the total displayed up top in the main form. I think all you would have to do is have your Text Box display in your footer if you wanted it there with the items.

I am running Access 2003, so if you are too, you may change your line 3 you have tried to look similar to mine. That might make it click.

[dr_destructo]

Question -
Inside the brackets, where you have 'ordLine', is that part of your textbox name or is it code?

[HMEpartsmanager]

Question -
Inside the brackets, where you have 'ordLine', is that part of your textbox name or is it code?

Text Box in FORM
Located in the FORM's Detail Area
--------------------------------------
Name: txtOrderTotal
Control Source: =[Purchase Orders - Sub].Form!OrdersSubFormSubTotal


Text Box in SubFORM
Located in the SubFORM's Footer Area
--------------------------------------
Name: OrdersSubFormSubTotal
Control Source: =Sum([ordlineQtyOrdered]*[ordlinePrice])


Just text boxes. [ordlineQtyOrdered] is how many I purchased on that line in the subform, and the [ordlinePrice] is the price I paid this time for this item. These two multiplied together get total cost of the line item, and then Sum it all up so I have total to display on the Form (Purchase Order) with how much this Purchase Order cost.

So the ordline you speak of are the fields used in the detail area of the SubForm and keeps track of items purchased.

Does that help you a bit?

Tim

[dr_destructo]

Yes, that does clear things up as far as your answer goes. But, when I put that in, I get $0.00 as the end result. Any thoughts on that?

[HMEpartsmanager]

Yes, that does clear things up as far as your answer goes. But, when I put that in, I get $0.00 as the end result. Any thoughts on that?

Do you have the SUMS being totaled in the subform's footer?

And do you have the TOTAL (answer from the SUMS) being displayed on the Form in the Form's Detail area?

That is how I did mine.

If you are only using the Sum in the footer and trying to display it, I have not done this.

Tim

[dr_destructo]

Yes, I have a text box in the sub form footer with the =Sum(([])*([])) equation and another text box on the main form pointing to the subform text box. Both of which show 0.00

[HMEpartsmanager]

Yes, I have a text box in the sub form footer with the =Sum(([])*([])) equation and another text box on the main form pointing to the subform text box. Both of which show 0.00

I can't think of anything off the top of my head. I have it set up with Format: Currency and 4 Decimals for the Subform calculation and Format: Currency and 2 Decimals for the Form Display portion.

Maybe someone else will jump in with something obvious to check for.

Tim

[HMEpartsmanager]

Just checking, but you do only have 1 set of parentheses, right?

In mine, I have one set; but in yours, you have 2 sets.

Actually, you have a few set of parentheses in your statement. See if it will work with just the 1 set; a start, and an end.

=Sum(([txtQty])*([txtPrice]))

Control Source: =Sum([txtQty]*[txtPrice])



Tim

[dr_destructo]

Tim -
I've tried messing around with how many sets up parenthases are used and it doesn't seem to change the end result.

[maximus]

I have done some thing for you. simple mdb with two tables Clients and Orders. the orders list all the products,price and quantity, totals of orders placed by clients. I have a main form Client and a order sub-form. the subform has a total Text box. Now I have a Total Box on my main Client form whic shows me the subform Total. Check it out try entering some data in the subform. see if this is what your require.

[dr_destructo]

Maximus -
That's perfect. But what I don't understand what the difference between your and mine is. The only thing I can see different is your has the id box for each record visible. How'd you do it?

[maximus]

its simple and has nothing to do with orderId being visible in the subform. see the expression of the text box on the main form that displays the suform total
it like this

=[Form].NameofSubForm!Name of text box in the subform

just reference all the textboxes on the subform that you want to dshow on the main form like this and thats it.

This are the expressions that you had probably used do you see the difference.


Name: txtOrderTotal
Control Source: =[Purchase Orders - Sub].Form!OrdersSubFormSubTotal


Text Box in SubFORM
Located in the SubFORM's Footer Area
--------------------------------------
Name: OrdersSubFormSubTotal
Control Source: =Sum([ordlineQtyOrdered]*[ordlinePrice])


if you problem is solved please mark this thread solved!!! welcome to the forum.....

[maximus]

its simple and has nothing to do with orderId being visible in the subform. see the expression of the text box on the main form that displays the suform total
it like this

=[Form].NameofSubForm!Name of text box in the subform

just reference all the textboxes on the subform that you want to dshow on the main form like this and thats it.

This are the expressions that you had probably used do you see the difference.


Name: txtOrderTotal
Control Source: =[Purchase Orders - Sub].Form!OrdersSubFormSubTotal


Text Box in SubFORM
Located in the SubFORM's Footer Area
--------------------------------------
Name: OrdersSubFormSubTotal
Control Source: =Sum([ordlineQtyOrdered]*[ordlinePrice])


if you problem is solved please mark this thread solved!!! welcome to the forum.....

[dr_destructo]

Well the issue that i'm having is not transferring the info from the subform to the main form, it's just getting the subtotal textbox on the subform footer to actually calculate. so far all i get is 0.00 or #error# depending on what I put in as the control source.