Subform Total

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

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,
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...


.
Click to expand...
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Mark said:
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.


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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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.

Reply to group, rather than allenbrowne at mvps dot org.

Mark said:
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
.
Click to expand...
Click to expand...
 
I just sent you an email at your web site address
([email protected]) 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.

Reply to group, rather than allenbrowne at mvps dot org.

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
.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top