Update table from form

M

mr_doles

I am very new to access so this will probably be a very easy question
to answer:

I have a table with a profit field. On my form I have five fields
(purchase_price, sold_price, allowed_shipping, actual_shipping, and
commision) and the total profit field. I changed the Control Source
for the profit field to be
=([sold_price]+[allowed_shipping])-([purchase_price]+[actual_shipping]+[commision]).

The problem is since the control source is different when I save the
record it does not update the profit field in the table.
 
R

Rick Brandt

mr_doles said:
I am very new to access so this will probably be a very easy question
to answer:

I have a table with a profit field. On my form I have five fields
(purchase_price, sold_price, allowed_shipping, actual_shipping, and
commision) and the total profit field. I changed the Control Source
for the profit field to be
=([sold_price]+[allowed_shipping])-([purchase_price]+[actual_shipping]+[commision]).

The problem is since the control source is different when I save the
record it does not update the profit field in the table.
Click to expand...

Exactly right. The good news is that calculation results don't belong in
your tables anyway. Just eliminate that field from your table and use the
expression you have to calculate the value whenever you need it.

If you want to eliminate having to write that expression over and over
create a SELECT query based on your table and add a calculated field to the
query using your expression. Then simply use that query instead of your
table anyplace you need to have access to that value.
 
K

Klatuu

First, a calculated value should not be stored in a table. There are too
many ways it can get out of sync and create inaccurate results. Also, it
takes up disk space unncessarily. Any time you need to present that value in
a report, a form, or a query, you should do the calculation there.

Now, since you will probably say you really have to have it in the table
(you would still be incorrect), here is how you do it.

Make the control source for your contorl the profit field. Do you
calculation in the Before Update event of the form.
 
M

mr_doles

Well if two people say not to put it in the table then out it goes.

Thanks for the replies.
 
Top