Using the SUM function in a main form -referring to a sub form? in Access Database Forms  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Richard Horne 9/29/2004 2:31 AM PST
  Question
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Allen Browne 9/29/2004 2:57 AM PST
  Answer
  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@discussions.microsoft.com> wrote in message
news:A29F46C1-359F-4FB0-9BDB-B8259C1CD911@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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
DAW 12/10/2004 8:51 AM PST
   
  Allen,

Are there any rules or restrictions on the ordering of your calculations
(the ordering of your controls) in a continuous form used as a subform? The
reason for the question is due to receiving #Error messages when attempting
to do a sum of a control which is a calculated expression. For example, the
control is called Estimate and the value is calculated as [Est1] + [Est2] +
[Est3] + [Adj]. Summing the Estimate as =sum([Est1] + [Est2] + [Est3] +
[Adj]) produces #Error messages for this new control in addition to #Error
messages throughout the subform (even though it does not appear that other
controls are related in any way). Made me think maybe calcs need to occur in
a certain order.

Thank you,
Don

"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@discussions.microsoft.com> wrote in message
> news:A29F46C1-359F-4FB0-9BDB-B8259C1CD911@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.
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies