Using the SUM function in a main form -referring to a sub form
in [Form]
|
Prev: Form just opened doesn't see data entered in previous form
Next: How to display progress on slow opening form.
From: Noor ul Hassan Noor ul on 29 May 2010 03:32 I am Noor, Your great solution proves really helpful. I faced the same problem which is solved by your provided solution but there is a little problem still present. I have two forms on main form. I have to calculate the SUM of Quantity of each Item in both the subforms and subtract the total of second subform from the first subform in main form. Your provided solution help me to solve this problem but when the value in second form equal to Zero, then it show 0 as answar while it should show the original value of the first subform. Please help me. Thankx alot in advance. "Allen Browne" wrote: > 1. From the database window, open your subform in design view. > > 2. If you do not see a Form Footer section, click Form Header/Footer on the > View menu. You can set the section's Visible property to No if you do not > wish to see this section. > > 3. In the Form Footer section, add a text box, and give it these properties: > Control Source =Sum([Quantity] * [Price]) > Name txtSubTotal > Format Currency > > 4. Save and close. > > 5. In the main form, set the Control Source of the text box that should show > this total to: > =[Order Details Subform].[Form].[txtSubTotal] > > You can then multiply that value by your Discount Multiplier field to > display the calculated total in another text box. (Note that you should not > store the amount of discount nor the order total in the main Order 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. > > "Richard Horne" <RichardHorne(a)discussions.microsoft.com> wrote in message > news:A29F46C1-359F-4FB0-9BDB-B8259C1CD911(a)microsoft.com... > > Hi guys. > > > > I have a field in a sub form called Total Item Cost which multiplies the > > quantity and price of an item in my ordering system. This form is called > > Order Details Subform, then I have my main order form called Orders I want > > to > > create a text box in this main form which SUMs the field Total Item Cost, > > can > > this be done and if so how do I go about this? > > > > Can you apply calculations from fields in a sub form to fields in a main > > form? As I also have a discount multiplier in the mai form I would like to > > multiply the SUM total by also. But obviously the answer to the first part > > of > > my question should answer the second part. > > >
From: Allen Browne on 29 May 2010 05:50
Presumably you have two subforms (say Sub1 and Sub2), and you have the total in the Form Footer section of each subform working correctly. To subtract one from the other, place a text box on the main form, and set its Control Source to an expression like this: =Nz([Sub1].[Form].[txtTotal],0) - Nz([Sub2].[Form].[txtTotal],0) That should work, unless the entire Detail section of a subform goes blank due to this issue: http://allenbrowne.com/casu-20.html -- 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. "Noor ul Hassan" <Noor ul Hassan(a)discussions.microsoft.com> wrote in message news:7548ED90-8F7D-47EF-942F-9636768FE046(a)microsoft.com... > I am Noor, Your great solution proves really helpful. I faced the same > problem which is solved by your provided solution but there is a little > problem still present. I have two forms on main form. I have to calculate > the > SUM of Quantity of each Item in both the subforms and subtract the total > of > second subform from the first subform in main form. Your provided solution > help me to solve this problem but when the value in second form equal to > Zero, then it show 0 as answar while it should show the original value of > the > first subform. Please help me. Thankx alot in advance. > > "Allen Browne" wrote: > >> 1. From the database window, open your subform in design view. >> >> 2. If you do not see a Form Footer section, click Form Header/Footer on >> the >> View menu. You can set the section's Visible property to No if you do not >> wish to see this section. >> >> 3. In the Form Footer section, add a text box, and give it these >> properties: >> Control Source =Sum([Quantity] * [Price]) >> Name txtSubTotal >> Format Currency >> >> 4. Save and close. >> >> 5. In the main form, set the Control Source of the text box that should >> show >> this total to: >> =[Order Details Subform].[Form].[txtSubTotal] >> >> You can then multiply that value by your Discount Multiplier field to >> display the calculated total in another text box. (Note that you should >> not >> store the amount of discount nor the order total in the main Order >> 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. >> >> "Richard Horne" <RichardHorne(a)discussions.microsoft.com> wrote in message >> news:A29F46C1-359F-4FB0-9BDB-B8259C1CD911(a)microsoft.com... >> > Hi guys. >> > >> > I have a field in a sub form called Total Item Cost which multiplies >> > the >> > quantity and price of an item in my ordering system. This form is >> > called >> > Order Details Subform, then I have my main order form called Orders I >> > want >> > to >> > create a text box in this main form which SUMs the field Total Item >> > Cost, >> > can >> > this be done and if so how do I go about this? >> > >> > Can you apply calculations from fields in a sub form to fields in a >> > main >> > form? As I also have a discount multiplier in the mai form I would like >> > to >> > multiply the SUM total by also. But obviously the answer to the first >> > part >> > of >> > my question should answer the second part. >> >> >> |