Forms and Calculated Fields

[buffman1]

I need some expert advice, please. I set up a database beginning in Access
2000. From there, I've worked in 2002 and 2007. The problem I now have is
in trying to set up a form and a subform whereby I can create totals from
fields in the subform to be shown on the form. I have read several posts in
here and tried Access Help, but am still a little lost. Specifics: I have a
Qty field and Unit Price field that i want summed in the subform. I have set
up a calc. field to get that sum and it will show up. In the sub form, I
will have several entries and want to total all the calculated line by line
item order totals. That is the problem...I have tried setting up another box
to get the total of all items ordered, based on the created calculated field.
I've put it in the footer of the subform but get errors, no matter how I set
it up. I read that you can't set up a calculation based on another
calculated field, which is what I have, but try as I may, can't make it work.
I probably need to work in version 2002, so perhaps I could get
recommendations based on that version. I keep thinking I am just missing one
step but have reached the end of my rope at this point. Am I making
sense--providing enough background to get this across? Any help would be
appreciated.

 

[John W. Vinson]

I need some expert advice, please. I set up a database beginning in Access
2000. From there, I've worked in 2002 and 2007. The problem I now have is
in trying to set up a form and a subform whereby I can create totals from
fields in the subform to be shown on the form. I have read several posts in
here and tried Access Help, but am still a little lost. Specifics: I have a
Qty field and Unit Price field that i want summed in the subform. I have set
up a calc. field to get that sum and it will show up. In the sub form, I
will have several entries and want to total all the calculated line by line
item order totals. That is the problem...I have tried setting up another box
to get the total of all items ordered, based on the created calculated field.
I've put it in the footer of the subform but get errors, no matter how I set
it up. I read that you can't set up a calculation based on another
calculated field, which is what I have, but try as I may, can't make it work.
I probably need to work in version 2002, so perhaps I could get
recommendations based on that version. I keep thinking I am just missing one
step but have reached the end of my rope at this point. Am I making
sense--providing enough background to get this across? Any help would be
appreciated.

You can't sum a *CONTROL* on a form - only a *FIELD* in the form's
recordsource.

A couple of choices:

1. Base the Subform on a Query with a calculated field, by typing

ExtPrice: [Qty] * [Unit Price]

and then in the subform's Footer put a textbox with

=Sum([ExtPrice])

2. Leave the query as it is and use

=Sum([Qty] * [Unit Price])

as the control source of a textbox on the subform footer.

In either case, relay the value in the subform control to the mainform by
using a textbox with a control source like

=Subformname.Form![txtTotal]