A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using the SUM function in a main form -referring to a sub form



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 08:32 AM posted to microsoft.public.access.forms
Noor ul Hassan
external usenet poster
 
Posts: 1
Default Using the SUM function in a main form -referring to a sub form

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" wrote in message
...
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.




  #2  
Old May 29th, 2010, 10:50 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Using the SUM function in a main form -referring to a sub form

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 wrote in message
...
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" wrote in message
...
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.