If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Subform Total
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 |
#3
|
|||
|
|||
Subform Total
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 . |
#4
|
|||
|
|||
Subform Total
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 |
#5
|
|||
|
|||
Subform Total
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 . |
#6
|
|||
|
|||
Subform Total
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 . |
#7
|
|||
|
|||
Subform Total
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 . . |
Thread Tools | |
Display Modes | |
|
|