Storing results of calculated fields

[mpmoore]

I have several calculations on my form. I am looking for a way to store the results of a calculation in the underlying table. If the calculation formula is in the Control Source property, where do I put the real control source. Or can I do this? Thanks for any input.

 

[Databaseguy]

You probably shouldn't, and really don't need to. This violates the 3rd Normal Form (3NF) of normalization. As long as you have the data needed for the calculation, you can perform the calculatin in any query, report, form, etc.

 

[jgoodman00]

I agree with this, but under certain circumstances it is beneficial to store this value. When this is the case, I leave the fields control source to its original, but write a small procedure (in the form current area) which performs the calculation & then returns the result into the field, hence automatically inserting the value. Something along the lines of the following should do it:

Dim x as integer, y as integer, result as integer

x = me.xfieldname
y = me.yfieldname

result = (x * y) 'Brackets not necessary but can avoid confusion.
me.fieldname = result

It could also be written much more simply as:

(me.xfieldname * me.yfieldname) = me.resultfieldname

Either of these methods will work, but the first will not take into account null values.


James Goodman
j.goodman00@btinternet.com

 

[mpmoore]

Jgoodman,
What do you mean by "the form current area" ?

Dbaseguy,
Thanks for the tip, but I am getting tired of trekking all of these formulas around.

Mike

 

[BenSC]

The best way that I have found to do this is to set the field control source to the table field you want it to update. Open up the Properties of the form which the calculated field is on. Select the Timer event property. You can then code the calculation you want. I find a Timer Interval of 1000 to be fine as it quickly recalculates any changes to the field.

All the best
Ben Cooper

 

[jgoodman00]

If you open a form in design view, within Properties-Event-On Current. Select Event procedure, & then enter something along the lines of the above code to append a formula result to a field.... James Goodman
j.goodman00@btinternet.com