Calculate Using Fields on Different Forms?

[batowl]

Hi All,

I am building a database with a data entry (main) form that contains two subforms. My clients would like the data entry/main form to have a calculated field on it that does math across all three forms (i.e. takes value from field on main form, subtracts value from one subform, subtracts multiple values from another subform) and displays the calculated value.

Is it possible to have a text box on the data entry form perform this calculation across three forms? If so, how would I accomplish that? Thanks.

[Missinglinq]

This is usually done by placing an Unbound Textbox on a Subform, doing calculations for that SF as the Control Source this Textbox, and then referencing if from the Main Form. Here's a short tutorial on how to do this:

First off, when using Functions like Sum() you must use the Field name, not the Textbox name! If they're both the same (as Access names them when a Field is dragged onto a Form) it will work, but if the two names are different, you must use the name of the Control's underlying Field.

To Calculate a Total, in Form Design View, in the SubForm's Footer, place a Unbound Textbox.

To simply total a Single Field, the Control Source of this new Textbox would be

=Sum([Field1])

If the Field is a Calculated Value you must Sum() the Calculation, not the Field itself!

So, if

MySubTotal = Field1 + Field2

You cannot use

=Sum([MySubTotal])

Instead, you must use

= Sum([Field1[ + [Field2])

Now, to place the totals on the Main Form:

Create an Unbound Textbox, and use this in its Control Source

=SubformName.Form!SFTotalTextboxName

Note that SubformName has to be the name of the Subform Control, and not the name of the Form the Subform is based on, unless the two are the same.

SFTotalTextboxName is the name of the Textbox on the Subform where the calculation is done.

To modify this for your scenario, you'd do something like this in the ControlSource of the Textbox on your Main Form:

=Me.MainFormTextbox - (FirstSubformName.Form!FirstSubformTextboxName + SecondSubformName.Form!SecondSubformTextboxName)

Linq ;0)>

[batowl]

Thanks for your reply. I am trying to get your solution to work and I am having trouble.

The name of my main form where I would ultimately like this calculated field displayed is called: Contract_Grant_Information. The one field on this form that I want to use as part of the calculation is called: Total_Funding_Amount.

One of the subforms located on the main form is called: Initial_Invoice_Information. The field from this subform that I would like to use in the calculation on the main form is called: Inital_Invoice_Sum. This field is a calculation based on the another field in this subform called Actual_Invoice_Amount. The calculation is =Sum([Actual_Invoice_Amount]).

The other subform on the main form is called: Invoice_Information.The field from this subform that I would like to use in the calculation on the main form is called: Invoice_Sum. This field is a calculation based on another field in this subform called: Actual_Invoice_Amount. The calculation is =Sum([Actual_Invoice_Amount]).

Both of these calculations on the subforms are working just fine.

Based on these names and the guidance you provided, here is the equation in the unbound textbox on the main form where this calculation I am trying to achieve should be displayed:

=Contract_Grant_Information.Total_Funding_Amount-(Initial_Invoice_Information.Form!Initial_Invoice_ Sum+Invoice_Information.Form!Invoice_Sum)

For whatever reason this is not working. I simply get #Name? inside the text box. Any idea what I am doing wrong? (I really hope it is not the =Sum etc. etc. in the subform calculations - that is kind of important.)

[alcapps]

=[Contract_Grant_Information].[Total_Funding_Amount]-([Initial_Invoice_Information].Form![Initial_Invoice_ Sum]+[Invoice_Information].Form![Invoice_Sum])

not sure about your references as well.. form usually have me. in front or forms!formname!form!control format.

Hope this helps

[batowl]

Thanks - tried your suggestion and it is not working either. This is kind of driving me crazy, I feel like it shoudl be easier than it is.

I thought maybe I was putting the cart before the horse by doing all the calculations on the form, so I built them into a stand alone query and it all works fine. I was hoping I could add a text box to the form and have the control source be the value of this query, but that is not working either. Ugh.

[batowl]

Got it. Should not have been referencing the main form name in the equation, needed to called subforms Subform in equation. Thanks all!

=[Total_Funding_Amount]-([Initial_Invoice_Information Subform]!Actual_Invoice_Amount+[Invoice_Information Subform]!Invoice_Sum)