Problem with totals on subforms

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

Ron wrote:

Quote:

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

"Salad" <oil@vinegar.comwrote in message
news:13dbl8makf1vc72@corp.supernews.com...

Quote:

Ron wrote:

Quote:

>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

Problem with totals on subforms

re: Problem with totals on subforms

re: Problem with totals on subforms

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about