storing calculated values from forms into a database file

[snimmuc]

I am new to Access. I have created a form with a calculated field. All other
data from the form stores OK back into the Database, BUT the calculated field
will not store in the database. The calculated field stores OK into the split
screen version of the form layout. Your help Please. I am using Access 2007

 

[Arvin Meyer [MVP]]

I am new to Access. I have created a form with a calculated field. All
other
data from the form stores OK back into the Database, BUT the calculated
field
will not store in the database. The calculated field stores OK into the
split
screen version of the form layout. Your help Please. I am using Access
2007

Access saved you from making a mistake in database design. Unless you need
history, and are not storing the elements of the calculation, you should
never store the results of a calculation. Forms do not store data, they
allow entry and display.

 

[KenSheridan via AccessMonster.com]

Whether you should store a derived value or not in a column in the table
depends on the scenario:

1. If the computed value can always be derived from other stored values, and
should consequently change if they change, then don't store it. For
example in an OrderDetails table you might compute a total price with
[UnitPrice] * [Quantity]. The total price should not be stored in this case
as it will always reflect the UnitPrice and Quantity values stored in other
columns. It should be shown in an unbound computed control on a from or
report, or in a computed column in a query.

2. In the same OrderDetails table you might look up the UnitPrice of the
selected product from a Products table. In this case if you did not store
the price in the OrderDetails table, as the price in products changes over
time the price in each order would be changed. You would not want that to
happen as the price in the order should remain static as that current when
the order was created. In this case you would not use an unbound control for
UnitPrice but a bound control and assign the value looked up from Products to
it. You'll find an example in the sample Northwind database's Orders Subform,
where code in the AfterUpdate event procedure of the ProductID control
assigns the value to the bound UnitPrice control as follows:

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Ken Sheridan
Stafford, England