Storing a calculated field into a table 1

[Mack2]

On my form, I have several calculated fields
([Field3] = [field1] * [field2]). I want to store the results into a table. What is the best way to store the value into a table without clicking a save record button?

Right now I have the following code behind a save record button Me.ProcessCost = [Total Part Process Cost].Value. But I do not want them to have to click the save button.
THANKS!!!!!!!!

 

[ItIsHardToProgram]

Have an update query would be the best procedure... .. or into query...

 

[Mack2]

Have the form based on an update query?

 

[fneily]

You can use recordsets to store the calculations. BUT storing calculations violates the protocols of Access. Why do you need to store them? AND IT BETTER BE AN INCREDIBLE EXCUSE.

 

[Mack2]

I need to store that information in a table so I can use it in a report. I want to store it into a table and not a query. The reason being so when I need that field, I can access the table and not the query.

 

[ItIsHardToProgram]

Sorry I did not look at your post correctly, you are already calculating on the form...

Why don't you bound the field with the anser?

 

[randy700]

Storing calculated data is rarely a good idea. Since you all ready have the information that the calculations were based on, it's a simple matter to design your report to perform the calculations every time it is opened. By doing so, you can make changes to the raw data when necessary and not worry about updating the calculated field.

This has been addressed numerous times in this forum.

Randy

 

[MajP]

You could have a calculated control on the report as well as the form but this becomes a pain if you want to use this in multiple locations. So this is what I usually do if I am showing a calculated value in multiple places:

Instead of doing the calculation in an unbound control, do the calculation in the query that the form is bound to. Then bind the value to a control. Slightly different, and not as efficient if a very large recordset because it has to calculate for each record instead of just the rendered record

Now you can use the same query or just copy the calculated field for use in the reports query.

I disagree with those who say that storing calculated values violates database normalization. However, I agree that you need to consider size, speed, and complexity issues and have a valid reason. For the average user doing simple math on relatively small recordsets, they should not need to store values.

 

[Mack2]

Thanks to everyone for your help! I will most likely store the calculated field in a query. I really did not want to do that originally because every other field on the form is part of the table. So now I will have to base that form on a query just for that one calculated field.

Also...the calculated field is summing, so I will have to sum in the query which will add a twist to things.

Thanks again everyone...cheers!

 

[MajP]

I would recommend that you always build a form or report off of a query, even if it is a simple select. You can sort, add calculated fields, and return only the fields and records you need. Adds a tremendous amount of flexibilty

Also...the calculated field is summing

If the calculation is complex, I always build a custom function instead of trying to do it right in the query. Then the calculated field calls the function.

Example
Some function using two field values

public function doSomething(fldOneValue as double, fldTwoValue as double) as double
Do some complex calculation
doSomething = result
end function

In the query builder in a calculated field
MyNewValue: doSomething([fldOne],[fldTwo])

 

[Mack2]

Thanks again for the advice majp. That is some very good information. I think that I will base the form on the query, which will solve all of my issues.