Problem with totals on subforms

[Ron]

Hi All,

I've got a main form (frmMain) with 5 subforms on it. All subforms are
linked to different hidden controls on frmMain. The purpose of this form is
to record payments by 'source' (batches) so frmMain's source is a query on
BatchPaymentID. New 'source' for payment, new BatchPaymentID. 1st subform
(sfrmOne) is a client list of just the client's that would have that source
of payment. Next subform (sfrmTwo) is a list of charges (from the regular
transactions file) for the selected ClientID from sfrmOne. Select a charge
and it drops down to the payment subform...(sfrmThree) where the operator
can enter a payment and an adjustment should the payment not pay the charge
in full. Payments record into a table called tblPayAdjTransactions and then
will later (when all the payments entered equals the total for the check and
some other error trappings are done) post into the regular transactions file
(not there yet though).

So, all this works so far. No problems. Now we get to what doesn't work.

I have another subform, hidden, that I want to use as a "Total Payments"
kinda form (frmFour). It's linked to frmMain by the BatchPaymentID and
totals up all the payments, regardless of client from tblPayAdjTransactions
(on a query, of course). That way, I could have a running total of all
payments for the current batch, and display that on the main form. It
works, when the form initially loads (so, if all the payments equals $837.52
when I first open frmMain, I get that in the "Total of Payments" control on
the frmMain. And it works when one of the payments already recorded is
changed to a different amount. Change a $40 payment to $30 and the "Total
of Payments" control on frmMain changes to $827.52. But it DOES NOT update
when I add a new payment into tblPayAdjTransactions via sfrmThree, nor if I
change that just entered payment. Before the "Total Payments" control on
frmMain updates, I have to close frmMain and reopen it.

With this info, can someone suggest where I need to put whatever so that the
hidden form will update when records are added? I have another hidden form
that does the same thing, except it links by BatchPaymentID;ClientID and it
works the same way. Works fine when frmMain is loaded. Works fine if an
existing payment is changed. But DOES NOT update when a payment is added.

I've tried requerying the hidden forms, but nothing seems to work. I've
tried all the usual places, like Form_AfterUpdate, Form_AfterInsert all in
the sfrmThree form, etc--all still not working. In trying to get this done,
I've tried:
Forms!frmMain!sfrmFour!TotalOfPayments.Requery

Suggestions? Is there an easier way to do running subtotals for different
purposes as I've indicated? Need more info to be able to help?

TIA
ron

[Salad]

Ron wrote:

Hi All,

I've got a main form (frmMain) with 5 subforms on it. All subforms are
linked to different hidden controls on frmMain. The purpose of this form is
to record payments by 'source' (batches) so frmMain's source is a query on
BatchPaymentID. New 'source' for payment, new BatchPaymentID. 1st subform
(sfrmOne) is a client list of just the client's that would have that source
of payment. Next subform (sfrmTwo) is a list of charges (from the regular
transactions file) for the selected ClientID from sfrmOne. Select a charge
and it drops down to the payment subform...(sfrmThree) where the operator
can enter a payment and an adjustment should the payment not pay the charge
in full. Payments record into a table called tblPayAdjTransactions and then
will later (when all the payments entered equals the total for the check and
some other error trappings are done) post into the regular transactions file
(not there yet though).

So, all this works so far. No problems. Now we get to what doesn't work.

I have another subform, hidden, that I want to use as a "Total Payments"
kinda form (frmFour). It's linked to frmMain by the BatchPaymentID and
totals up all the payments, regardless of client from tblPayAdjTransactions
(on a query, of course). That way, I could have a running total of all
payments for the current batch, and display that on the main form. It
works, when the form initially loads (so, if all the payments equals $837.52
when I first open frmMain, I get that in the "Total of Payments" control on
the frmMain. And it works when one of the payments already recorded is
changed to a different amount. Change a $40 payment to $30 and the "Total
of Payments" control on frmMain changes to $827.52. But it DOES NOT update
when I add a new payment into tblPayAdjTransactions via sfrmThree, nor if I
change that just entered payment. Before the "Total Payments" control on
frmMain updates, I have to close frmMain and reopen it.

With this info, can someone suggest where I need to put whatever so that the
hidden form will update when records are added? I have another hidden form
that does the same thing, except it links by BatchPaymentID;ClientID and it
works the same way. Works fine when frmMain is loaded. Works fine if an
existing payment is changed. But DOES NOT update when a payment is added.

I've tried requerying the hidden forms, but nothing seems to work. I've
tried all the usual places, like Form_AfterUpdate, Form_AfterInsert all in
the sfrmThree form, etc--all still not working. In trying to get this done,
I've tried:
Forms!frmMain!sfrmFour!TotalOfPayments.Requery

Suggestions? Is there an easier way to do running subtotals for different
purposes as I've indicated? Need more info to be able to help?

TIA
ron

In your form's afterUpdate event of subf3 maybe you need to requery
sfrmFour. I have no idea on how the value is being updated into the
Main form. Maybe
Forms!mainform!subform4.Requery
Forms!Mainform!TotalPays = Forms!mainform!subform4!TotalPays

Do you really need sf4? Probably not. You could have the control
source be something like (air code)
=NZ(Dsum("PayAmt","Transactions","BatchID = " & [BatchID]),0)
or write a function to total the transactions called SumTXs and in the
control that displays to totals enter
=SumTxs()
so that when you add something in SF3, in the afterupdate event you enter
Forms!MainForm!TotalsField.Requery

[Ron]

"Salad" <oi*@vinegar.comwrote in message
news:13*************@corp.supernews.com...

Ron wrote:

>Hi All,

I've got a main form (frmMain) with 5 subforms on it. All subforms are
linked to different hidden controls on frmMain. The purpose of this form
is to record payments by 'source' (batches) so frmMain's source is a
query on BatchPaymentID. New 'source' for payment, new BatchPaymentID.
1st subform (sfrmOne) is a client list of just the client's that would
have that source of payment. Next subform (sfrmTwo) is a list of charges
(from the regular transactions file) for the selected ClientID from
sfrmOne. Select a charge and it drops down to the payment
subform...(sfrmThree) where the operator can enter a payment and an
adjustment should the payment not pay the charge in full. Payments
record into a table called tblPayAdjTransactions and then will later
(when all the payments entered equals the total for the check and some
other error trappings are done) post into the regular transactions file
(not there yet though).

So, all this works so far. No problems. Now we get to what doesn't
work.

I have another subform, hidden, that I want to use as a "Total Payments"
kinda form (frmFour). It's linked to frmMain by the BatchPaymentID and
totals up all the payments, regardless of client from
tblPayAdjTransactions (on a query, of course). That way, I could have a
running total of all payments for the current batch, and display that on
the main form. It works, when the form initially loads (so, if all the
payments equals $837.52 when I first open frmMain, I get that in the
"Total of Payments" control on the frmMain. And it works when one of the
payments already recorded is changed to a different amount. Change a $40
payment to $30 and the "Total of Payments" control on frmMain changes to
$827.52. But it DOES NOT update when I add a new payment into
tblPayAdjTransactions via sfrmThree, nor if I change that just entered
payment. Before the "Total Payments" control on frmMain updates, I have
to close frmMain and reopen it.

With this info, can someone suggest where I need to put whatever so that
the hidden form will update when records are added? I have another
hidden form that does the same thing, except it links by
BatchPaymentID;ClientID and it works the same way. Works fine when
frmMain is loaded. Works fine if an existing payment is changed. But
DOES NOT update when a payment is added.

I've tried requerying the hidden forms, but nothing seems to work. I've
tried all the usual places, like Form_AfterUpdate, Form_AfterInsert all
in the sfrmThree form, etc--all still not working. In trying to get this
done, I've tried:
Forms!frmMain!sfrmFour!TotalOfPayments.Requery

Suggestions? Is there an easier way to do running subtotals for
different purposes as I've indicated? Need more info to be able to help?

TIA
ron

In your form's afterUpdate event of subf3 maybe you need to requery
sfrmFour. I have no idea on how the value is being updated into the Main
form. Maybe
Forms!mainform!subform4.Requery
Forms!Mainform!TotalPays = Forms!mainform!subform4!TotalPays

Do you really need sf4? Probably not. You could have the control source
be something like (air code)
=NZ(Dsum("PayAmt","Transactions","BatchID = " & [BatchID]),0)
or write a function to total the transactions called SumTXs and in the
control that displays to totals enter
=SumTxs()
so that when you add something in SF3, in the afterupdate event you enter
Forms!MainForm!TotalsField.Requery

Hi Salad,

Thanks for the quick reply.

As you'll note from the original post, I did try the requery on sfrmFour but
slightly differently (with the control I'm looking for). Just tried your
way (without the control) and it works fine now...sheezzz... hours I've been
working on this! I'll put that same requery in a couple more places and
it'll do exactly what I want for what I've got so far. But I think I'll
play around with what else you've suggested to see if it'll work any
easier/faster/better.

Thanks again!
ron