Subform Total

I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the Control
Source. Is it possible to autopopulate a text box on the
main form with a sum of the totals?
Thanks
Mark

1. From the Database window, open the subform in design view.

2. If you do not see a Form Footer section, select Form Header/Footer from
the View menu.

3. Add a text box to the form footer, and give it these properties:
Control Source =Sum([Price]*[Quantity])
Name txtTotal
Format Currency
Save and close the subform.

4. Open the main form in design view. Add a text box with this Control
Source:
=[NameOfYourSubformControlHere].[Form]![txtTotal]

If you do not know the name of your subform control, right-click the edge of
it and choose Properties. The Name property is on the Other tab of the
Properties box.

Note that this approach displays the total on the main form, but does not
save it there. You should not save the total back into the main form's
table, as doing so breaks basic rules of normalization. More information:
http://allenbrowne.com/casu-14.html

If you have a good reason to save the calculated total back into the main
form's table, use the AfterUpdate and AfterDelConfirm events of the form in
the subform to DSum() all the related records directly from the subform's
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" wrote in message
...
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the Control
Source. Is it possible to autopopulate a text box on the
main form with a sum of the totals?
Thanks
Mark

Allen,
Thanks for your help-it works great. Could you tell me
how to save the calculated [Price]*[Quantity] field back
to the subform's "Total" field? Since it already uses
this expression as the Control Source, I don't know how
to do this.
-----Original Message-----
1. From the Database window, open the subform in design
view.

2. If you do not see a Form Footer section, select Form
Header/Footer from
the View menu.

3. Add a text box to the form footer, and give it these
properties:
Control Source =Sum([Price]*[Quantity])
Name txtTotal
Format Currency
Save and close the subform.

4. Open the main form in design view. Add a text box
with this Control
Source:
=[NameOfYourSubformControlHere].[Form]![txtTotal]

If you do not know the name of your subform control,
right-click the edge of
it and choose Properties. The Name property is on the
Other tab of the
Properties box.

Note that this approach displays the total on the main
form, but does not
save it there. You should not save the total back into
the main form's
table, as doing so breaks basic rules of normalization.
More information:
http://allenbrowne.com/casu-14.html

If you have a good reason to save the calculated total
back into the main
form's table, use the AfterUpdate and AfterDelConfirm
events of the form in
the subform to DSum() all the related records directly
from the subform's
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" wrote in
message
...
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the
Control
Source. Is it possible to autopopulate a text box on
the
main form with a sum of the totals?
Thanks
Mark


.

Something like this, where "ID" represents the name of the foreign key
field:

Private Sub Form_AfterUpdate()
Me.Parent![SomeTextBox] = DSum("[Price]*[Quantity]", _
"NameOfSubformTableHere", "ID = " & Me.ID)
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" wrote in message
...
Allen,
Thanks for your help-it works great. Could you tell me
how to save the calculated [Price]*[Quantity] field back
to the subform's "Total" field? Since it already uses
this expression as the Control Source, I don't know how
to do this.

-----Original Message-----
1. From the Database window, open the subform in design
view.

2. If you do not see a Form Footer section, select Form
Header/Footer from
the View menu.

3. Add a text box to the form footer, and give it these
properties:
Control Source =Sum([Price]*[Quantity])
Name txtTotal
Format Currency
Save and close the subform.

4. Open the main form in design view. Add a text box
with this Control
Source:
=[NameOfYourSubformControlHere].[Form]![txtTotal]

If you do not know the name of your subform control,
right-click the edge of
it and choose Properties. The Name property is on the
Other tab of the
Properties box.

Note that this approach displays the total on the main
form, but does not
save it there. You should not save the total back into
the main form's
table, as doing so breaks basic rules of normalization.
More information:
http://allenbrowne.com/casu-14.html

If you have a good reason to save the calculated total
back into the main
form's table, use the AfterUpdate and AfterDelConfirm
events of the form in
the subform to DSum() all the related records directly
from the subform's
table.


"Mark" wrote in
message
...
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the
Control
Source. Is it possible to autopopulate a text box on
the
main form with a sum of the totals?
Thanks
Mark

This is what I entered but it isn't saving:

Private Sub Total_AfterUpdate()
Me.Parent![Total] = DSum("[Price]*[Quantity]", "Order
Details2", "Total = " & Me.Total)

End Sub
Order Details2 is the Order Table of the subform where I
need to save the info & Total is the field name & text
box name.
Thanks Again

-----Original Message-----
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the
Control
Source. Is it possible to autopopulate a text box on the
main form with a sum of the totals?
Thanks
Mark
.

That won't work:
1. Use the AfterUpdate of the *form*, not of the Total text box. (That's the
subform's AfterUpdate event.)

2. The calculated control will not be updated in time. You may be able for
force it with a Recalc, but the DSum() as illustrated in my previous reply
would be better (works even if filters are applied to the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" wrote in message
...
This is what I entered but it isn't saving:

Private Sub Total_AfterUpdate()
Me.Parent![Total] = DSum("[Price]*[Quantity]", "Order
Details2", "Total = " & Me.Total)

End Sub
Order Details2 is the Order Table of the subform where I
need to save the info & Total is the field name & text
box name.
Thanks Again

-----Original Message-----
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the
Control
Source. Is it possible to autopopulate a text box on the
main form with a sum of the totals?
Thanks
Mark
.

I just sent you an email at your web site address
) which mightexplain my problem
better. Thanks for your help.
Mark
-----Original Message-----
That won't work:
1. Use the AfterUpdate of the *form*, not of the Total
text box. (That's the
subform's AfterUpdate event.)

2. The calculated control will not be updated in time.
You may be able for
force it with a Recalc, but the DSum() as illustrated in
my previous reply
would be better (works even if filters are applied to
the form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" wrote in
message
...
This is what I entered but it isn't saving:

Private Sub Total_AfterUpdate()
Me.Parent![Total] = DSum("[Price]*[Quantity]", "Order
Details2", "Total = " & Me.Total)

End Sub
Order Details2 is the Order Table of the subform where
I
need to save the info & Total is the field name & text
box name.
Thanks Again

-----Original Message-----
I have a subform with fields Price, Quantity & Total
(among others). The "Total" field is autopopulated by
using =[Price]*[Quantity] as an expression in the
Control
Source. Is it possible to autopopulate a text box on
the
main form with a sum of the totals?
Thanks
Mark
.



.