Data Transfer from Forms to Tables

[Guest]

Hi all,

I'm in the process of creating a new database to track change requests
regarding a major project at work. Part of this tracking includes cost
estimates, etc. These costs are broken down into 4 categories: Engineering,
Material, Fabrication, and Construction. I'm using a Query to sum these
values, thereby calculating the total cost of the change. However, this
calculated total cost isn't getting sent back to the tables in the database.
(That is, the TOTAL COST column remains filled with zeros.) The sum
calculation seems to be working, since it gives the correct value in the
form. Any ideas on why the values aren't being transferred back to the
tables?

 

[BruceM]

There's no need to store the calculated value. In general it is better for
Access to calculate the value on the fly, because if one of the variables
changes, so will the total. With a stored calculated value you would need
to run the calculation again., which means you would either need code to run
the calculation each time the record is modified, or something of that sort,
or else you would need active intervention on the user's part, which is
something on which you may not want to rely. You have the correct numbers.
There's almost certainly no need to do more.

 

[Guest]

I will need it for reporting purposes though...that's why I wanted to store
it. I know it's "better" to calculate on the fly...but, is there any way to
store it?

Sparky

 

[Rick Brandt]

I will need it for reporting purposes though...that's why I wanted to
store it. I know it's "better" to calculate on the fly...but, is
there any way to store it?

Put the calculation directly in your report or in a query and base your
report on the query instead of the table. You don't need to store it
(really).

 

[BruceM]

I have never stored a calculation, but if I had to do so for some reason I
would probably do something like use the form's Before Update event to write
the value from the unbound text box to a field in the record source table.
Then I would add code to be sure that if one of the numbers that went into
the calculation changed, the calculation will be performed again. Or
something like that.

Or, since I already have the number I need, I would just base the report on
the query containing the calculated field, and would do something else with
the time I saved.

As Rick pointed out, you really do not need to store the calculation. How
will your report differ if you do so?