Automatically populating a field.

[Guest]

Hi Everyone-

I have an Access 2003 table with several fields. One of fields stores the
List Cost of a piece of furniture and another field store a Discount
percent. There is a third field called Unit Cost which is calculated by
multiplying the List Cost and the Discount. What I am trying to do is to have
the table automatically update the Unit Cost whenever anyone alters the value
in either the Discount or the List Cost fields. The macro has to take into
account that sometimes the discount field may have a value of zero and on
some occations we may leave the List cost blank until we get a price from our
venders. If you would like to see a copy of the database please let me
know. Thanks in advance.

Manuel A. Ayala
CAD Concepts
(e-mail address removed)

 

[Andi Mayer]

On Thu, 6 Jan 2005 13:21:06 -0800, CAD Concepts <CAD

Hi Everyone-

I have an Access 2003 table with several fields. One of fields stores the
List Cost of a piece of furniture and another field store a Discount
percent. There is a third field called Unit Cost which is calculated by
multiplying the List Cost and the Discount. What I am trying to do is to have
the table automatically update the Unit Cost whenever anyone alters the value
in either the Discount or the List Cost fields. The macro has to take into
account that sometimes the discount field may have a value of zero and on
some occations we may leave the List cost blank until we get a price from our
venders. If you would like to see a copy of the database please let me
know. Thanks in advance.

Manuel A. Ayala
CAD Concepts
(e-mail address removed)

This is a very nice example for:

Never Never Never Never store a calculated field

if you need this calculation shown on a report or form or query ...

calculate it again.

BTW: the calculation is nearly evertime faster then reading from disk

 

[SirPoonga]

I agree, do the calculation when you need to display the value from the
calculation.

 

[SirPoonga]

errr, hit reply before I finished. There is no need to store a
calculated value unless the calculation take too long to do and isn't
done that often.

 

[margaret bartley]

Hi Everyone-

I have an Access 2003 table with several fields. One of fields stores the
List Cost of a piece of furniture and another field store a Discount
percent. There is a third field called Unit Cost which is calculated by
multiplying the List Cost and the Discount. What I am trying to do is to have
the table automatically update the Unit Cost whenever anyone alters the value
in either the Discount or the List Cost fields. The macro has to take into
account that sometimes the discount field may have a value of zero and on
some occations we may leave the List cost blank until we get a price from our
venders. If you would like to see a copy of the database please let me
know. Thanks in advance.

Manuel A. Ayala
CAD Concepts
cadc

(e-mail address removed)

If a ten percent discount is entered as 10, then use this formula in the
AfterUpdate
events of both the ListCost field, and the Discount field:
UnitCost = Nz(ListCost - Nz(ListCost * Discount * 0.01))

If a ten percent discount is entered as .1, then use this formula:
UnitCost = Nz(ListCost - Nz(ListCost * Discount)).

 

[Guest]

(e-mail address removed)

If a ten percent discount is entered as 10, then use this formula in the
AfterUpdate
events of both the ListCost field, and the Discount field:
UnitCost = Nz(ListCost - Nz(ListCost * Discount * 0.01))

If a ten percent discount is entered as .1, then use this formula:
UnitCost = Nz(ListCost - Nz(ListCost * Discount)).

Hi Margaret-

Thanks for your help. When I get into the design view of my Master table
and select the field, I cannot find the AfterUpdate option that you refer to
above. Please advise. Just to let you know, the reason I would like the
field to update is that the users that enter the data in the table want to
immedaitely see what the list cost is. Thanks.

Manuel

 

[margaret bartley]

Hi Margaret-

Thanks for your help. When I get into the design view of my Master table
and select the field, I cannot find the AfterUpdate option that you refer to
above. Please advise. Just to let you know, the reason I would like the
field to update is that the users that enter the data in the table want to
immedaitely see what the list cost is. Thanks.

The AfterUpdate event is on a field on a form.

You need to create a form to update the table. The table is for storing
data, only,
and cannot be used to do any programming or computations.