Update table from form

  • Thread starter Thread starter mr_doles
  • Start date Start date
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.
 
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.
 
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.
 
Well if two people say not to put it in the table then out it goes.

Thanks for the replies.
 
Back
Top