Inserting totals from a form to a table

[Steve]

Hi...I am trying to total up hours in a form, and have created an expression
that is successfully doing that, and displaying it on the form header.
However, I want that value to be insert into the corresponding table, but the
control source is being used for the actual formula. Is there anyway I can
put the totals per record into the table?

 

[Steve]

It is displaying the total hours worked that day on the form, my thoughts of
putting it in the table would be for using it in different reports and such
as I work on dissecting all of this data 20 different ways. I thought If I
could put that total on the table, then I wouldn't have to run those same
formulas on all of the different reports that are going to be created.

 

[Jeff Boyce]

If your form is displaying the totals correctly, what will having that total
stored on the table allow you to do that you can't do now?

(it is rarely necessary to store a calculated value like this ... not
"never", only rare)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[BruceM]

You could use the After Update event of the controls containing values used
in the calculation to perform the math operation and insert that value into
the CombinedValue field. If values are being inserted programatically you
will probably need to include the calculation in that code as well. If you
make changes to the form you will need to be sure the changes to not affect
the calculated number. If you decide to proceed along this path, this
approach would consist of something like:
Me.CombinedValue = Me.Field1 + Me. Field2 + Me.Field3
I need to repeat that you will likely have on your hands a maintenance
hassle that could become rather finicky and time-consuming.
You could add the expression to a query, which could presumably be used for
several different forms or reports. Another option, depending on the
details of how your database is structured and how the data are orgnanized,
is that you may be able to create a function to do the math. The function
could be used in code, queries, or as the Control Source.
Are you adding hours contained in multiple fields within one record? If so,
there is a good chance the design could be improved in such a way that the
sum is across several records rather than within a single record.

 

[fredg]

It is displaying the total hours worked that day on the form, my thoughts of
putting it in the table would be for using it in different reports and such
as I work on dissecting all of this data 20 different ways. I thought If I
could put that total on the table, then I wouldn't have to run those same
formulas on all of the different reports that are going to be created.

Regarding >>> I wouldn't have to run those same formulas on all of the
different reports that are going to be created <<<

Which is precisely the reason why you should not store the total in
any table...it will force you to run the calculation again... which
will assure that the total displayed in the report is the correct
value. What will be the effect on any stored total if, after storing
it, one of the values in the total calculation has been changed?

This has been discussed in these newsgroups thousands of times. The
response has been ... do not store calculated data unless there is a
compelling reason to do so. There is nothing compelling about storing
the calculated total simply because you don't want to run the
calculation again.