Re: Forms and Calculated Fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Wed, 22 Oct 2008 20:09:01 -0700, buffman1
<buffman1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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]
--

John W. Vinson [MVP]
.

Relevant Pages

  • Check box changes formula used in subform
    ... I have a subform for listing parts. ... NET PRICE, LIST PRICE, Quantity, Supplier, and a calculated field called Part ... hidden text box on my subform that calculates the sum of all the Part Totals ...
    (comp.databases.ms-access)
  • Need check box to change formula used in subform source
    ... I have a subform for listing parts. ... NET PRICE, LIST PRICE, Quantity, Supplier, and a calculated field called Part ... hidden text box on my subform that calculates the sum of all the Part Totals ...
    (comp.databases.ms-access)
  • Getting #NAME? in Sunform field
    ... I've tried several ways of doing a calculated field in a subform. ... Second by writing a query and referencing that in the control source. ...
    (microsoft.public.access.forms)
  • RE: Totals Row Not Summing
    ... to SUM, but it doesn't calculate a sum. ... Let's say your subform text box control is txtCost and it's ControlSource is ... I enabled the totals row on the two datasheet subforms, ...
    (microsoft.public.access.forms)
  • Re: Refresh main form from sub form
    ... And when you need to force Access to update the calculated controls on a ... > meals on the subform the Dlookup wasn't refreshed. ... > Refresh on the main form I realised I was probably going the wrong way. ... then totalled them and used the totals as datasource for total boxes ...
    (microsoft.public.access.formscoding)