Store Calculated Values on form in Underline table

[John 5000]

Microsoft Article ID 209172 - Last Review: July 15, 2004 - Revision:1.2
I have followed the instructions on this example on a copy of Northwind.mdb in Access 2007.
When I open the form the following message appears :-
The object doesn't contain Automation object 'Order Details'
You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.
I am trying to get this to work as I want to store a calculade value in my databace. any help would be great.
Is there any other way to store a calculated value in a table?

 

[JamesMcS]

If the calculated field you want to store is on a form which is bound to the table you want to store the value in, just use

tablefieldname = calculatedcontrolname.value

in an event on your form - for instance in the on click event of a button.

 

[boblarson]

Why are you attempting to store a calculated value? It is only RARE occasions when calculated values should be stored. Most of the time they should not. Please elaborate.

 

[John 5000]

I have two tables "Show" & "Bookings" linked via a query. Profit/Loss is a field in the bookings table. In the query "Profit/Loss:[Door Take]-[Show Cost].
Run the query and it produces the results ok.
I put these three fields on a form and type in "Door Take" & "Show Cost" and it produces a figure in "Profit/Loss, but when I close the form the value in "Profit/Loss" is not stored in the Bookings Table. Why when the other two are?.
I was going to try and use the SetValue function, but it did not work in my database or in the Northwind.mdb following the Article 209172.

Regard John

 

[DCrake]

You would create a query that would contain the calculation.

Tables are for storing data only
Queries are for manipulating and displaying data

The main reason for not storing calculations in tables is that once any element of a calculation changes not only do you have to save this element but you have to perform the calculation again and store the value in the field. By using calculations in queries this dynamically updates it on the fly.

Consider trying to store a persons age in a table. You would have to run an update every single day as a persons age can change rom one day to another.

 

[John 5000]

Hi Dave, Thanks for the reply.
I know you are right but in this case the value should not change as it becomes historic and I am trying to find a work round for a problem I am experiencing in trying to total columns in a report. I thought if I saved these Amounts I could total them and then add them to the report as its only the totals that are incorrect.

 

[QMDirk]

Can you show the Sum of the values in a calculated field?

 

[pbaldy]

This thread is over 9 years old. You can sum a calculated table field in a query if that's what you're asking.