Re: Problem with totals on subforms


"Salad" <oil@xxxxxxxxxxx> wrote in message
news:13dbl8makf1vc72@xxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: HELP PLEASE! Question about Expression Building and data entry.
    ... the reason it is not storing is because you changed the control ... In it, she wants payments to ... I can see the proper totals and values in the "Total Payments" ... I suppose that the underlying problem is that I changed the "control source" ...
    (microsoft.public.access.setupconfig)
  • Re: HELP PLEASE! Question about Expression Building and data entry.
    ... the reason it is not storing is because you changed the control ... In it, she wants payments to ... I can see the proper totals and values in the "Total Payments" ... I suppose that the underlying problem is that I changed the "control source" ...
    (microsoft.public.access.queries)
  • Problem with totals on subforms
    ... I've got a main form (frmMain) with 5 subforms on it. ... Next subform is a list of charges (from the regular ... Payments record into a table called tblPayAdjTransactions and then ... of Payments" control on frmMain changes to $827.52. ...
    (comp.databases.ms-access)
  • Re: Problem with totals on subforms
    ... All subforms are linked to different hidden controls on frmMain. ... Payments record into a table called tblPayAdjTransactions and then will later post into the regular transactions file. ... It's linked to frmMain by the BatchPaymentID and totals up all the payments, regardless of client from tblPayAdjTransactions. ... 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. ...
    (comp.databases.ms-access)
  • Re: sum sub levels.
    ... Level 2 totals from level 3 totals, perhaps with color, but are there more levels ... 6350623 prem on debt protfolio restruct prem on debt protfolio restruct ... 6340623 retail bonds retail bonds ... You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES, ...
    (microsoft.public.excel.misc)