Default value

[lee]

Hi,

I'm creating a form for capturing my stock trade data. In each transaction,
there are a few expenses that is based on the amount of the transaction.
For example, commission, government levies, stamp duty, etc. All of those
are charged based on a certain percentage of the trade and they happened in
every transaction.

The twist is the formula will look different when it's a buy vs it's a sell.
ie. Buy = Price x Qty + commission + stamp duty ....; Sell = Price x Qty -
commission - stamp duty ...

What I wanted to acheive is:
1. Set the default value of each type of charges by using formula. ie.
Commission = Price x Qty x N %;
2. The default value hopefully can take care of minimum charge. For
example: If Price x Qty is less than $1000, then Commission is $20;
3. The commission rate is different for different brokers, so I need to
take care of that too;
4. The transaction be calculated based on the type of the trade using the
formula mentioned earlier;

My problem is that the Default Value property does not take field names in
the formula. I looked at Northwind and it's handled by setting the formula
in the Contrl Source property that only show the numbers on screen. But I
want to keep those numbers in a field because I want to keep historical data
of all those charges and expenses.

Could someone help?

Thanks,
Es

 

[Allen Browne]

You cannot use Default Value for this. Access applies the Default Value
*before* any entry is begin. The values in the other fields are not
available at that time, so Default Value cannot work.

Instead, use the AfterUpdate event of each of the controls that the
calculation depends on. Using VBA code, you can make it as involved as
needed, handling buys and sells differently, specifying a minimum, and so
on.

Example in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Guest]

You need to use a lookup table for the broker commission.