Update table from Form

  • Thread starter Thread starter Mat
  • Start date Start date
M

Mat

Can anyone tell me how I can update a table with a value I
have calculated in a form. I have an 'Exc_VAT' value,
a 'VAT_Rate', and I then have a text box 'Total'
I have an expression for the 'Total' text box '=[Exc_VAT]*
[VAT_Rate]
I want to insert this calcualted value back into
the 'TOTAL' field in the table.

I expect it is extremely simple, but can anyone help ?
 
The reason that you are having problems is because it is
not good practice to store calculated values on tables.
There are two reasons primarily; firstly it takes up space
and secondly it makes maintenance difficult. The best
practice for database design is to store the base
information only and include controls like Total on forms
and reports to show such calculations as and when they are
required.

Hope This Helps
Gerald Stanley MCSD
 
Gerald,

Thanks for the good advise. However, there are times when
I may need to enter a value that differs from the
calculated field. For example a surcharge of 10% might be
levied, BUT on certain circumstances I may waive this
charge to get an order. That is when I need to record it
as a precise amount in the TOTAL field in the table.

Any suggestions ?
Mat

-----Original Message-----
The reason that you are having problems is because it is
not good practice to store calculated values on tables.
There are two reasons primarily; firstly it takes up space
and secondly it makes maintenance difficult. The best
practice for database design is to store the base
information only and include controls like Total on forms
and reports to show such calculations as and when they are
required.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can anyone tell me how I can update a table with a value I
have calculated in a form. I have an 'Exc_VAT' value,
a 'VAT_Rate', and I then have a text box 'Total'
I have an expression for the 'Total' text box '=[Exc_VAT] *
[VAT_Rate]
I want to insert this calcualted value back into
the 'TOTAL' field in the table.

I expect it is extremely simple, but can anyone help ?
.
Click to expand...
.
Click to expand...
 
In such situations, the Total text box's ControlSource
should be the column in the Form's RecordSource and not a
calculation. Instead, you need to code the calculation
into the AfterUpdate eventHandlers of the Excl_VAT and
VAT_Rate text boxes something like
[Total] = [Excl_VAT] *(1 + ([VAT_Rate] / 100)). - assuming
that the VAT Rate is entered as 17.5

On a practical note, the example you quote below for
holding the total field does not ring true in the business
world. Whilst you may waive a surcharge in order to gain
business, that waiving would normally get reflected in the
[Excl_VAT] amount. But you know your business better than
I do.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald,

Thanks for the good advise. However, there are times when
I may need to enter a value that differs from the
calculated field. For example a surcharge of 10% might be
levied, BUT on certain circumstances I may waive this
charge to get an order. That is when I need to record it
as a precise amount in the TOTAL field in the table.

Any suggestions ?
Mat

-----Original Message-----
The reason that you are having problems is because it is
not good practice to store calculated values on tables.
There are two reasons primarily; firstly it takes up space
and secondly it makes maintenance difficult. The best
practice for database design is to store the base
information only and include controls like Total on forms
and reports to show such calculations as and when they are
required.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can anyone tell me how I can update a table with a value I
have calculated in a form. I have an 'Exc_VAT' value,
a 'VAT_Rate', and I then have a text box 'Total'
I have an expression for the 'Total' text box '=[Exc_VAT] *
[VAT_Rate]
I want to insert this calcualted value back into
the 'TOTAL' field in the table.

I expect it is extremely simple, but can anyone help ?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Hi Gerald!

You could save the configuration of each order where you set the different
primary key´s.
If you have an order where you need to use a surcharge of 10% you could
create a lookup table where you store the different surcharge rates like
this:

tblSurchargeRate
SurChargeID (Primary key)
SurchargeRate (ex. 10%)

Then have a table in where you configurate the order and add the valid
SurChargeID (preferable named as fkSurChargeID). In this table you store
other types of data which configurates an order.

In this example you don´t have to store a calculation but you can still
calculate it for each order again later on.

I hope I made myself understod!?

// Niklas



Mat said:
Gerald,

Thanks for the good advise. However, there are times when
I may need to enter a value that differs from the
calculated field. For example a surcharge of 10% might be
levied, BUT on certain circumstances I may waive this
charge to get an order. That is when I need to record it
as a precise amount in the TOTAL field in the table.

Any suggestions ?
Mat

-----Original Message-----
The reason that you are having problems is because it is
not good practice to store calculated values on tables.
There are two reasons primarily; firstly it takes up space
and secondly it makes maintenance difficult. The best
practice for database design is to store the base
information only and include controls like Total on forms
and reports to show such calculations as and when they are
required.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can anyone tell me how I can update a table with a value I
have calculated in a form. I have an 'Exc_VAT' value,
a 'VAT_Rate', and I then have a text box 'Total'
I have an expression for the 'Total' text box '=[Exc_VAT] *
[VAT_Rate]
I want to insert this calcualted value back into
the 'TOTAL' field in the table.

I expect it is extremely simple, but can anyone help ?
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top