Saving the calculated field to the control source table

[dylan03]

I have a sales form that a salesperson completes at the time of the sale. The control is the [Sales] table. How do I calculate the fields so that they save to the (control source) table?

A shortened version of my Sales table context is:

TransactionID Number
CustomerName Text
SalePrice Currency
UsedCarAllowance Currency
TaxableAmount Currency

For example, when the rep enters the [saleprice] of $6000.00 and a [usedcarallowance] of $1000.00, I need the form to calculate (after requerying?) the [taxableamount] with the calculation of [saleprice]-[usedcarallowance].

I know I can enter this calculation into the controlsource directly, but then the value does not save to the [Sales] table.

Can anyone help? Let me know if you need further clarification to help me resolve this simple question.

Thanks in advance!

 

[SemperFiDownUnda]

TaxableAmount isn't a computed column in sense of database

a computed column is like
SELECT CustomerName,
SalePrice,
UsedCarAllowance,
(SalePrice - UsedCarAllowance) AS TaxableAmount
FROM Sales

you actually have a column there so you just read and write to it normally and it is your apps responciblity to use the correct calculation.

for more help show us the code you are using to populate the controls, tell us if you are using databinding, Show use the query you are using to get the data.

 

[dylan03]

Actually, the form isn't tied to a query, but rather a table. This is because as the rep is entering the miscellaneous sales amounts (eg SalePrice, UsedCarAllowance, etc), the form is calculating the TaxableAmount. This is done by having the calculation =[SalePrice]-[UsedCarAllowance] as the controlsource for the [TaxableAmount] field.

The problem is that I need to somehow save this calculation to the Sales table. (When a form is tied to a table, Access cannot save a field to the table if the controlsource is set to anything but the table field name.)

If there is a better way of doing this, I'd love to learn. I just don't know coding well enough to know how to set an OnClick or OnUpdate property to perform a calculation on another field.

Could you give me an example of that coding?

 

[SemperFiDownUnda]

This is a access question perhaps the access forum is better place for this.

In VB I'd, if i did databinding at all, wouldn't set the control source to a calculation but the actual field and just have code overwrite that fields after a event indicating the data is loaded and every time one of the other fields change.

Personally I wouldn't ever have the field in the database. It will always be equal to the first field minus the second field so why store it? It is what we call "Derived information" and generally you don't perm. store derived information unless you need to do so for speed reasons and this calculation would not be enough for that.