Saving calculated values

[SJH]

I have been given an older database and asked to make upgrades and what not.
One interesting thing I have come across with the database is that it was at
one time set up so one of the tables would hold values that were calcuated
using a form. I have looked at the code for all the text boxes on the
assocatied forms and have yet to figure out how this was done. There were
two values set up this way, one still works and the other one doesn't,
needless to say this has turned the reports into a mess.

I know all about manipulating data using queries, but these values are
stored values that will not change once they have been calculated.

Can someone point me in the right direction as to how calcualted values can
be stored in a table? I am not all that familiar with writing code, but I
can usually figure it out once I see it.

Thanks for any help anyone can offer.

SJH

[Pieter Linden]

Re: Saving calculated values

"SJH" <12myraz@wwt.ne t(remove first two)> wrote in message news:<7IT6b.244 7$zu1.34407@reg gie.win.bright. net>...[color=blue]
> I have been given an older database and asked to make upgrades and what not.
> One interesting thing I have come across with the database is that it was at
> one time set up so one of the tables would hold values that were calcuated
> using a form. I have looked at the code for all the text boxes on the
> assocatied forms and have yet to figure out how this was done. There were
> two values set up this way, one still works and the other one doesn't,
> needless to say this has turned the reports into a mess.
>
> I know all about manipulating data using queries, but these values are
> stored values that will not change once they have been calculated.
>
> Can someone point me in the right direction as to how calcualted values can
> be stored in a table? I am not all that familiar with writing code, but I
> can usually figure it out once I see it.
>
> Thanks for any help anyone can offer.
>
> SJH[/color]
first let me say that I'm not sure storing the calculated values is a
great idea. Just base your report on a query that does the
calculations at runtime.

Okay, that said - there's one way to do it *BEFORE* the record is
saved - just put the code to update the values in the BeforeInsert
event of your form. You could have the calculated fields be invisible
and then do something like:

Me.CalcField1=E xpression1(Fiel d1,Field2,Field 3)
Me.CalcField2=E xpression2(Fiel d3,Field4,Field 5)

I don't like this approach at all... what if someone changes any of
the values in fields 1-5? Your calculations go up in smoke. That's
what I really don't like about the scenario you describe. So it takes
a few extra seconds for the report to open... big deal. At least the
data is ALWAYS current if you just base it off a query (or several)
and just calculate everything at runtime.

There. I said it. But I wouldn't do what you're proposing unless you
are absolutely sure you want just "historical "/ReadOnly data after
that. If I were going to do that, then I'd skip the complexity and
just use update/append queries to write the calculated values to a
temporary table, then run my reports and then drop the temp tables.
See Tony Toews site for info on temp tables.