Make a textbox formula result appear in the datasheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used three textboxes on a Form for "Nett", "Tax" and "Total".
"Nett" is input by the user and is written to the datasheet column because
it's Properties / Control Source is "Nett" which is the name of that column.
In the other two textboxes I have formulae for automatically working out the
tax ("Tax") and adding the for the total ("Total").
These formulas are written in the Properties / Control Source line which
overwrites the reference to their columns in the datasheet.
Is there some way that I can have the automatic results in Tax & Total and
stiil have those results appear in thier columns in Datasheet View ?
 
Use a query to perform the calculations. Then when you make the query the
source for your form, it shows up in the datasheet. The crucial thing is NOT
to store these values in the table.

Details of how to do that:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

BTW, if you have some items that are tax exempt, you might want to store the
TaxRate in the table. If the TaxRate is 0%, then the calculated tax comes to
$0, and so the total adds correctly as well.
 
Allen Browne said:
Use a query to perform the calculations. Then when you make the query the
source for your form, it shows up in the datasheet. The crucial thing is
NOT to store these values in the table.
Click to expand...

Unless the tax rate changes. If you only calculate the tax on the fly, then
when the tax rate changes and you go back to look at historical records, the
new rate will be used rather than the rate that was in effect when the
record was entered.

Tom Lake
 
Tom, the suggestion was that you may need to store the tax RATE - in case
the rate changes, and also to cope with tax-exempt items.

However, you d not store the tax AMOUNT, because that is dependent on the
dollar amount in the record. Typically your fields would be:
Quantity Number how many
PriceEach Currency price of each one
TaxRate Number the tax rate at the time of the transaction
(0% if exempt)
ProductID foreign key to the table of products.
 
Back
Top