Calculate values in subforms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing a database that currently has one main form and two subforms.
I am trying to figure out how to calculate a value on the second subform from
data in the first subform and the second subform. Hopefully this makes some
sense.

Here's the bext example I can do:

I want to calculate the difference between a quantity listed in Subform1 and
a quantity listed in Subform 2...I want the result to show up in a field in
Subform 2.

I have tried everything I know how to do with expressions and I am getting
the "Enter Perimeter Value" dialog box every time I open the form. I am sure
I am just doing something wrong with the syntax but I can't figure it out!

Help!
 
How are you trying to calculate the text box? Is the text box located
in the detail section of the form or the footer? Try posting the code
you are using.
 
All I can come up with, using the Expression Builder - is: =[Blanket PO
Subform]![Blanket PO Quantity]-[Bard Subform]![Release Qty]

I am writing this code in the query design of the Bard Subform (i have the
subform based on this query)

Thanks for trying to help me. I did get a couple of books tonight from the
library - so hopefully I can figure this out with your help and the books!

Michelle
 
This example assumes:
- The subform controls are named "sub1" and "sub2".
- The field you want to sum is called "Qty".

1. Open sub1 in design view.

2. If you don't see a Form Footer section, click Form Header/Footer on the
View menu. (Set the Visible property of this section to No if you want.)

3. Add a text box to the Form Footer section, and give it these properties:
Control Source =Sum([Qty])
Name txtQtyTotal
Format Currency (or General Number, as
appropriate).

4. Save and close this subform.

5. Open sub2 in design view. In the Form Footer section, add a text box with
this Control Source:
=[Form].[Parent].[sub1].[Form].[txtQtyTotal]

Once you get that working, you can then subtract the Qty field from this
also. It will end up something like this:
=Nz([Form].[Parent].[sub1].[Form].[txtQtyTotal], 0) -
Nz(Sum([Qty]),0)

The Nz() it to handle the situation where one subform has no quantity to
report. That should work unless one of the subforms has no records and no
new records can be added (in which case it will display #Error).

Note that the "sub1" must the be name of the subform control, and that is
not necessarily the same as the name of the form loaded into the control
(i.e. its Source Object).
 
Back
Top