Adding fields from a subform - simple

[Guest]

I have a form with different tabs. On one of the tabs there are two
subforms. One subform pulls all the invoices related to that customer in the
database (there is an invoice total field on each invoice record). The other
form pulls all the payments associated with that customer in the database.
What I want to do is simply to have three boxes on that tab: one showing the
total of all invoices, a second one showing the total of all payments, and a
third one showing the difference between the two.

Can anyone help? =)

 

[Al Camp]

Wael,
In each subform, in the footer, place a calculated field that adds up the
Debits or the Credits accordingly.
ex. [SumDebits] and [SumCredits]
Since you'll be referring to these fields from the Tab on the Main form,
you would address the sumDebits as...
(ex. name = [TotalDebits])
=Forms!frmYourMain!frmYourSub.Form!sumDebits
Do the same for sumCredits...
(ex. name = [TotalCredits])
Now you can just create a third calculated field with
=TotalDebits-TotalCredits
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[tina]

in each subform's Form Footer section, add an unbound textbox control. name
them txtInvoicesTotal and txtPaymentsTotal, respectively. set the textbox's
ControlSource property to

=Sum(Nz([MyInvoiceAmount], 0))

and

=Sum(Nz([MyPaymentAmount], 0))

respectively. substitute the correct names of the fields in the Invoices and
Payments tables, again respectively. note: if your subforms are in
Datasheet view, you won't be able to see the Form Footer section, but it's
still there.

in your main form, on the tab page, create three unbound textbox controls.
set each textbox's ControlSource property to

=[MySubformControlName].[Form]![txtInvoicesTotal]
=[MySubformControlName].[Form]![txtPaymentsTotal]
=[MySubformControlName].[Form]![txtInvoicesTotal] -
[MySubformControlName].[Form]![txtPaymentsTotal]

make sure you use the name of the subform control in the main form, *not*
the name of the subform object in the database window. sometimes the names
are the same, sometimes they're different. to get the correct name:
1. open the main form in design view
2. click once on the subform control (within the main form) to select it.
3. in the Properties box, click the Other tab.
4. look at the Name property. that's the name of the subform control.

hth