saving calculated values

  • Thread starter Thread starter Jeremy Weiss
  • Start date Start date
J

Jeremy Weiss

I've got a 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 scorce
so it's technicely unbound. But when I bind it to the right field in the
table I can't get it to update anymore.

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

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

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
That's odd. I've re-read my posts several times and I'm just not seeing
where I asked for opinions on whether or not I should store a calculated
value. Maybe John's computer is running at a different resolution or
something.

-jeremy
 
That's odd. I've re-read my posts several times and I'm just not seeing
where I asked for opinions on whether or not I should store a calculated
value. Maybe John's computer is running at a different resolution or
something.
Click to expand...

<shrug> Ok, if you want to store data redundantly, aware of the
disadvantages of doing so but choosing not to explain why:

Use the Form's BeforeUpdate event. Have a calculated control txtCalc,
and a bound control txtBound; put in a line

Me!txtBound = Me!txtCalc

You can make txtBound invisible or leave it visible as you choose.

You can also use the AfterUpdate event of each control which goes into
the calculation - you will probably want to check all the source
controls to see if they're NULL before running the query.
 
John Vinson said:
<shrug> Ok, if you want to store data redundantly, aware of the
disadvantages of doing so but choosing not to explain why:
Click to expand...

Try an explain/defend my goofed up logic when dealing with an MVP? If I were
that capable I wouldn't be asking such questions in the first place. ;)
Instead of rational thought, I did what all humans do when faced with a
situation above their intelect; I did something stupid. Sorry for the
sarcasm. I'm just glad this didn't happen a century or two ago. I might have
replaced the sarcasm with a challenge to a dual or something _really_
stupid.
Use the Form's BeforeUpdate event. Have a calculated control txtCalc,
and a bound control txtBound; put in a line

Me!txtBound = Me!txtCalc

You can make txtBound invisible or leave it visible as you choose.
Click to expand...

That's pretty much the same solution I ended up with. Thanks.

Ever Apologetic,
-jeremy
 
Back
Top