Calculated fields on subforms - writing records

[robtyketto]

Im sure this is a common problem, I had a nasty shock when I discovered that calcualted fields (from a query) in my subforms are NOT stored with the database though the control source is a table field they are not bound to a table.

What are the most popular ways to solve this?

Easier the better

 

[Rich]

The most popular way is to continue to use a query to retrieve the value

 

[robtyketto]

The most popular way is to continue to use a query to retrieve the value

Hi Rich, I thought VBA was the way forward to write the value of the form into the table field.

Can you expand on the use a query to retrieve the value?

In the query the calculation is not bound to a table just the form value.

Cheers
Rob

 

[Rich]

That's the point, most here would not recommend storing a calculated value at all

 

[robtyketto]

As the value is on the form cant I write it to the record uisng vba code (provide basic syntax/example)?

Obvioulsy complication of writing it when the navigation button is pressed to move to the next record.

 

[Rich]

If the calculation is performed in the underlying query there is no need to store it

 

[Mike375]

Forms!YourForm!FieldtoReceiveValue = Forms!YourForm!FieldWithValue

In a macro it is a SetValue action with Expression/Item

An Upate query will do the lot in one hit or you could open the query and do copy and paste.

But as Rich says you do not normally store calculated data. If you do you will finish with lots of extra fields in your table as your data base gets more complex. Another problem is that the stored data might not be current. A simple example would be storing a persons age.

One area where data is stored is if you are keeping some data and the current calculation would no longer be relevant because the price, commssion rate or whatever has changed for the product. If that is the case then if you have code or macro setting the value you need a condition that blocks the setting when there is already a value in the table field.

Perhaps you could post up part of your data base or explain what sort of calculated data you are wanting to store but it is not common to store data.