Calculated Field based on Several Tables or Forms

[dkriese]

I have a form as follows

Main Form (Client Main)
Subform 1 (Client Info)
Subform 2 (Payments/Deposits)

Main Form is the Client form

subform 1 are the status and details of clients & info



subform 2 is/are the payment clients have paid to date (deposits)

subform 2 gets its data from a payment table/form which lists all payments from all clients - their account numbers posts the payments to their respective subform client info via their account number and accessID number (this works perfectly)

Now I need to add a total field to show the cumulative payments (totals) for each account number which has been derived from subform 2 and needs to appear in Main form.

A user selects the client, then clicks on the subform status by year, and then sees all info related to work for that year, I need to calculate the cumulative monies received (could be paid by 5 separate checks)

Gee I hope I explained this correctly

[Bob Fitz]

You want to show (on the main form) the total of payments in subform2.
Is this correct?

[dkriese]

Yes - to explain more, one form/table named PAYMENTS accumulates the payments in a field named AMOUNTS (one per row), I would like it to calculate all the "amounts" and post it to a field named PAR1 in a different table/form.

I really hope i'm explaining this right.

There is one table that has "All Payments" which thru a query I get it to post to each client via ClientID and AccountNo.

so this main table having all the payments, breaks out the individual payments to each client (i'm sure i'm now over explaining)

So under each client only their respective payments show in rows, giving the check no,.date, amount etc...

[Bob Fitz]

This is what I think you need to do:

Create a new text box in the Footer Section of the subform. Let's name this txtTotalAmounts. Its Control Source property should be set as:

=Sum([NameOfTextbox])
where "NameOfTextbox" is the name of the textbox that you want to total.

Set the Control Source property of a text box on the main form to:

=FormName.Form!TextBox
where "FormName" is the name of your subform control and "TextBox" is the name of the text box in the Footer Section.

[dkriese]

That worked thank you!

[Bob Fitz]

Glad to be of help.

If this helped, please click the scales at the top right of this posting. Many thanks.

Bob

[dkriese]

Again thank you. Now how do I get this total to appear on ther forms. I tried via access 2007 expression builder to reference the forms with the total and get either a blank and error or name?

On the bottom of the payment form I added your code and it did total payments correctly for each client (whether it be 1 or more payments)

Now I need that same total to appear on the header of the client info form and on the client main form so that it shows users that the client is paid in full or still has a balance (25 users on a painfully slow access 2007 DB-another separate issue)