saving calculated values

[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

 

[John Vinson]

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

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.

 

[Jeremy Weiss]

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

 

[John Vinson]

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.

<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.

 

[Jeremy Weiss]

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

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.

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

Ever Apologetic,
-jeremy