By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,899 Members | 1,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,899 IT Pros & Developers. It's quick & easy.

saving calculated values

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
----- 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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.