saving calculated values

[Jeremy Weiss]

I've got a temp table that contains the fields: amountowed, amountpaid, and
balanced. I've got a form that shows this information and I've set it up so
that when the amountpaid field is changed it subtracts the amountpaid from
the amountowed and display's it in the balanced field.

Unfortunately it doesn't save what is displayed back to the table. It does,
however, save the changes to the amount paid field. I'm assuming my problem
is that the balanced field in the form is showing an expression as it's
control source so it's technically unbound. But when I bind it to the right
field in the table I can't get it to update anymore.

Long explanation just to ask...
Anyone know of a way to update fields 'on the fly' and still have the
calculated value saved to the underlying table?

-jeremy

[Larry Linson]

"Jeremy Weiss" <jw*****@hotmail.com> wrote

Anyone know of a way to update fields
'on the fly' and still have the calculated
value saved to the underlying table?

If the calculated Control has the calculation expression in its Control
Source, then in the AfterUpdate event of each other Control that can be a
factor in the calculation, copy the content of the Calculated Control into a
Text Box control bound to the Field where you want it saved.

Alternatively, you could bind that Control to the Field that you want
updated, and perform the calculation in VBA code in the AfterUpdate event of
each other Control that can be a factor in the calculation.

Obligatory caution: if you are storing something that can be calculated from
other values when it is needed for display or use, then it is redundant
storage, and a violation of RDB design principles.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

bo*****@localhost.not (Larry Linson) wrote in
<6x**************@nwrddc03.gnilink.net>:

"Jeremy Weiss" <jw*****@hotmail.com> wrote

Anyone know of a way to update fields
'on the fly' and still have the calculated
value saved to the underlying table?

If the calculated Control has the calculation expression in its
Control Source, then in the AfterUpdate event of each other
Control that can be a factor in the calculation, copy the content
of the Calculated Control into a Text Box control bound to the
Field where you want it saved.

Alternatively, you could bind that Control to the Field that you
want updated, and perform the calculation in VBA code in the
AfterUpdate event of each other Control that can be a factor in
the calculation.

Obligatory caution: if you are storing something that can be
calculated from other values when it is needed for display or use,
then it is redundant storage, and a violation of RDB design
principles.

Er, not in the case of an apply payments temporary table, which is
what he's working on.

I do this with a checkbox field. You click the checkbox and the
AfterUpdate of the checkbox sets the balance and payment fields.
Because of that, you need an original balance field so you can
revert to it if you uncheck the invoice.

Say you have this:

InvTotal OrigBalance PmtAmt NewBalance
100.00 100.00 0.00 100.00

When you click the checkbox, the AfterUpdate changes it to:

InvTotal OrigBalance PmtAmt NewBalance
100.00 100.00 100.00 0.00

If you then uncheck, you zero out the PmtAmt field and reset
NewBalance to the same as OrigBalance.

OrigBalance is not displayed in the apply payments form. See:

http://www.bway.net/~dfassoc/Park/Ap...sCustomer.html

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jeremy Weiss]

----- Original Message -----
From: "David W. Fenton" <dX********@bway.net.invalid>
Newsgroups: comp.databases.ms-access
Sent: Wednesday, November 05, 2003 6:58 PM
Subject: Re: saving calculated values

Er, not in the case of an apply payments temporary table, which is
what he's working on.

I do this with a checkbox field. You click the checkbox and the
AfterUpdate of the checkbox sets the balance and payment fields.
Because of that, you need an original balance field so you can
revert to it if you uncheck the invoice.

Say you have this:

InvTotal OrigBalance PmtAmt NewBalance
100.00 100.00 0.00 100.00

When you click the checkbox, the AfterUpdate changes it to:

InvTotal OrigBalance PmtAmt NewBalance
100.00 100.00 100.00 0.00

If you then uncheck, you zero out the PmtAmt field and reset
NewBalance to the same as OrigBalance.

That's what I have in mind David. But I must ask, what happen's if the case
is something like:

InvTotal OrigBalance PmtAmt NewBalance
100.00 100.00 0.00 100.00

And the payment is only $80.

Does the clicking the checkbox activate something to check to see if
NewBalance <= PmtAmt ??

-jeremy