Values calculated in a Form entered in the Table?

[Ollie*99]

I created a simple table, one field is "Total Cost", there are a number of
other component costs fields that need to be calculated as a percentage of
the Total Costs.

I then made a form based on the table, and entered the formula ie.
=([Total_Cost]/100)*40 into the control source for the relevant field's text
box.

The form works. If I call up an existing record, it correctly shows the
percentage of the total cost for each component on the form.

However, it never puts the calculated value back into the table! It will
correctly enter all the other fields into the table, but leaves the costs
fields blank.

Of course, to put in the calculation (expression) I changed the control
source on the form field from the name of the field in the table, to the
expression.

How do I get it to enter the calculated value into the table?

 

[Al Campagna]

Ollie,
I'm assuming TotalCost is an entered value, not calculated?
Given that premise, it's really not necessary to save the TotalCostPct.
As a general rule, don't save a calculated value that can always be
re-calculated from existing values... in any subsequent form, query, or
report.

Ex. Given Price * Qty = Line Total
Since you capture Price and Qty, you wouldn't want to save LineTotal. It
can always be recalculated from Price and Qty (on the fly) in any subsequent
query, form, or report.

If you MUST save the TotalCostPct, create a field in your table called
TotalCostPct, add that bound field to your form, and use the AfterUpdate
event of TotalCost to update the TotalCostPct field.
Private Sub TotalCost_AfterUpdate()
TotalCostPct =([Total_Cost]/100)*40
End Sub
Whenever TotalCost changes TotalCostPct will be updated.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Douglas J. Steele]

In general, it's recommended that you do not store calculated values. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."

You can always put the calculation into a query, and use the query wherever
you would otherwise have used the table.

If you're insistent, you have two choices:

1. Bind the text box to the field in the recordset and set its value through
VBA
2. Use the calculation as the control source, and set the field in the
recordset through VBA (usually done in the form's BeforeUpdate event.

 

[Ollie*99]

Thank you both for your replies. I have been working on this the last hour
or two, and have taken a query route at the first try...

I have created an Update Query called "Cost Calculations". This has the
'Update To:' row with the formulas for each cost field. It is updating in the
table, and i have changed the 'Control Source' in the Form to link it to the
correct values in the table, instead of the calculation here previously.

I understand this and am perfectly happy, but, i do wonder how often and
under what circumstances the query will update? Will it update the values
everytime the Total Cost value is changed?

Thanks!

Ollie

In general, it's recommended that you do not store calculated values. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."

You can always put the calculation into a query, and use the query wherever
you would otherwise have used the table.

If you're insistent, you have two choices:

1. Bind the text box to the field in the recordset and set its value through
VBA
2. Use the calculation as the control source, and set the field in the
recordset through VBA (usually done in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I created a simple table, one field is "Total Cost", there are a number of
other component costs fields that need to be calculated as a percentage of
the Total Costs.

I then made a form based on the table, and entered the formula ie.
=([Total_Cost]/100)*40 into the control source for the relevant field's
text
box.

The form works. If I call up an existing record, it correctly shows the
percentage of the total cost for each component on the form.

However, it never puts the calculated value back into the table! It will
correctly enter all the other fields into the table, but leaves the costs
fields blank.

Of course, to put in the calculation (expression) I changed the control
source on the form field from the name of the field in the table, to the
expression.

How do I get it to enter the calculated value into the table?

 

[Douglas J. Steele]

You missed my point. You shouldn't be using an Update query. The calculated
value shouldn't be stored in the table. Instead, you should be using a
Select query that returns the calculated value, as well as the other values
in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you both for your replies. I have been working on this the last
hour
or two, and have taken a query route at the first try...

I have created an Update Query called "Cost Calculations". This has the
'Update To:' row with the formulas for each cost field. It is updating in
the
table, and i have changed the 'Control Source' in the Form to link it to
the
correct values in the table, instead of the calculation here previously.

I understand this and am perfectly happy, but, i do wonder how often and
under what circumstances the query will update? Will it update the values
everytime the Total Cost value is changed?

Thanks!

Ollie

In general, it's recommended that you do not store calculated values. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a
disk
fetch is much slower than almost any reasonable calculation), and it
risks
data validity, since once it's stored in a table either the Total or one
of
the fields that goes into the total may be changed, making the value
WRONG."

You can always put the calculation into a query, and use the query
wherever
you would otherwise have used the table.

If you're insistent, you have two choices:

1. Bind the text box to the field in the recordset and set its value
through
VBA
2. Use the calculation as the control source, and set the field in the
recordset through VBA (usually done in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I created a simple table, one field is "Total Cost", there are a number
of
other component costs fields that need to be calculated as a percentage
of
the Total Costs.

I then made a form based on the table, and entered the formula ie.
=([Total_Cost]/100)*40 into the control source for the relevant
field's
text
box.

The form works. If I call up an existing record, it correctly shows the
percentage of the total cost for each component on the form.

However, it never puts the calculated value back into the table! It
will
correctly enter all the other fields into the table, but leaves the
costs
fields blank.

Of course, to put in the calculation (expression) I changed the control
source on the form field from the name of the field in the table, to
the
expression.

How do I get it to enter the calculated value into the table?

 

[Ollie*99]

The net result of all of this, is that i have a tab on my main form labelled
"Financials" which displays our estimated component cost (the calculated
value we are discussing) alongside the known/found value for that component
cost.

Is it possible to show the value from the select query you suggest in this
way? I would also need to extract the values together for colleagues to use
in further analysis using Excel and other software.

Ollie

You missed my point. You shouldn't be using an Update query. The calculated
value shouldn't be stored in the table. Instead, you should be using a
Select query that returns the calculated value, as well as the other values
in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you both for your replies. I have been working on this the last
hour
or two, and have taken a query route at the first try...

I have created an Update Query called "Cost Calculations". This has the
'Update To:' row with the formulas for each cost field. It is updating in
the
table, and i have changed the 'Control Source' in the Form to link it to
the
correct values in the table, instead of the calculation here previously.

I understand this and am perfectly happy, but, i do wonder how often and
under what circumstances the query will update? Will it update the values
everytime the Total Cost value is changed?

Thanks!

Ollie

In general, it's recommended that you do not store calculated values. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a
disk
fetch is much slower than almost any reasonable calculation), and it
risks
data validity, since once it's stored in a table either the Total or one
of
the fields that goes into the total may be changed, making the value
WRONG."

You can always put the calculation into a query, and use the query
wherever
you would otherwise have used the table.

If you're insistent, you have two choices:

1. Bind the text box to the field in the recordset and set its value
through
VBA
2. Use the calculation as the control source, and set the field in the
recordset through VBA (usually done in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I created a simple table, one field is "Total Cost", there are a number
of
other component costs fields that need to be calculated as a percentage
of
the Total Costs.

I then made a form based on the table, and entered the formula ie.
=([Total_Cost]/100)*40 into the control source for the relevant
field's
text
box.

The form works. If I call up an existing record, it correctly shows the
percentage of the total cost for each component on the form.

However, it never puts the calculated value back into the table! It
will
correctly enter all the other fields into the table, but leaves the
costs
fields blank.

Of course, to put in the calculation (expression) I changed the control
source on the form field from the name of the field in the table, to
the
expression.

How do I get it to enter the calculated value into the table?

 

[Douglas J. Steele]

Of course. Create the query that has the computed fields include, and use
that query as the RecordSource for the form.

As I implied earlier, you can use a query anywhere you would otherwise have
used a table (such as in ExportSpreadsheet...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The net result of all of this, is that i have a tab on my main form
labelled
"Financials" which displays our estimated component cost (the calculated
value we are discussing) alongside the known/found value for that
component
cost.

Is it possible to show the value from the select query you suggest in this
way? I would also need to extract the values together for colleagues to
use
in further analysis using Excel and other software.

Ollie

You missed my point. You shouldn't be using an Update query. The
calculated
value shouldn't be stored in the table. Instead, you should be using a
Select query that returns the calculated value, as well as the other
values
in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you both for your replies. I have been working on this the last
hour
or two, and have taken a query route at the first try...

I have created an Update Query called "Cost Calculations". This has the
'Update To:' row with the formulas for each cost field. It is updating
in
the
table, and i have changed the 'Control Source' in the Form to link it
to
the
correct values in the table, instead of the calculation here
previously.

I understand this and am perfectly happy, but, i do wonder how often
and
under what circumstances the query will update? Will it update the
values
everytime the Total Cost value is changed?

Thanks!

Ollie




:

In general, it's recommended that you do not store calculated values.
As
fellow MVP John Vinson likes to say, "Storing calculated data
generally
accomplishes only three things: it wastes disk space, it wastes time
(a
disk
fetch is much slower than almost any reasonable calculation), and it
risks
data validity, since once it's stored in a table either the Total or
one
of
the fields that goes into the total may be changed, making the value
WRONG."

You can always put the calculation into a query, and use the query
wherever
you would otherwise have used the table.

If you're insistent, you have two choices:

1. Bind the text box to the field in the recordset and set its value
through
VBA
2. Use the calculation as the control source, and set the field in the
recordset through VBA (usually done in the form's BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I created a simple table, one field is "Total Cost", there are a
number
of
other component costs fields that need to be calculated as a
percentage
of
the Total Costs.

I then made a form based on the table, and entered the formula ie.
=([Total_Cost]/100)*40 into the control source for the relevant
field's
text
box.

The form works. If I call up an existing record, it correctly shows
the
percentage of the total cost for each component on the form.

However, it never puts the calculated value back into the table! It
will
correctly enter all the other fields into the table, but leaves the
costs
fields blank.

Of course, to put in the calculation (expression) I changed the
control
source on the form field from the name of the field in the table, to
the
expression.

How do I get it to enter the calculated value into the table?