Updating a table field with a calculated value on a form

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

Guest

I have a form textbox called age that calculates its value on a form based on
a table. The value is displayed on the form but it is not stored onto the
table. How do I enter the calculated value into the table that the form is
based on? The formula is listed below.

=IIf(IsNull([Comp Date]),(Date()-[Req Date]),([Comp Date]-[Req Date]))

The formula is placed in the Control Source value on the textbox property.
 
I have a form textbox called age that calculates its value on a form based on
a table. The value is displayed on the form but it is not stored onto the
table. How do I enter the calculated value into the table that the form is
based on? The formula is listed below.

=IIf(IsNull([Comp Date]),(Date()-[Req Date]),([Comp Date]-[Req Date]))

The formula is placed in the Control Source value on the textbox property.
Click to expand...

Why would you want to?
As long as you have the [Comp Date] and the [Req Date] stored in your
table, any time you need the resulting calculation, simply use the
above expression, in a form or report or query, and recalculate it.
That is the correct way to use Access.
 
As you've discovered, a single field on a form can either carry a calculated
value or it can be bound to a field in a table, but not both.

When I need to do this, my approach is to place a hidden textbox on the
form. The hidden textbox is bound to a field in the form's underlying
table/query. In the BeforeUpdate event for the form, I have a line of code
that copies the value in the calculated field to the hidden textbox so that
the result is stored in the table.

You should first ask yourself why you're storing the result of a calculation
in the table. In most cases, the calculation can be re-produced with the
data that's in the table and good normalization would say that you should
just store the data needed to re-produce the calculation.
 
Thanks.

JP said:
As you've discovered, a single field on a form can either carry a calculated
value or it can be bound to a field in a table, but not both.

When I need to do this, my approach is to place a hidden textbox on the
form. The hidden textbox is bound to a field in the form's underlying
table/query. In the BeforeUpdate event for the form, I have a line of code
that copies the value in the calculated field to the hidden textbox so that
the result is stored in the table.

You should first ask yourself why you're storing the result of a calculation
in the table. In most cases, the calculation can be re-produced with the
data that's in the table and good normalization would say that you should
just store the data needed to re-produce the calculation.

AmytDev said:
I have a form textbox called age that calculates its value on a form based on
a table. The value is displayed on the form but it is not stored onto the
table. How do I enter the calculated value into the table that the form is
based on? The formula is listed below.

=IIf(IsNull([Comp Date]),(Date()-[Req Date]),([Comp Date]-[Req Date]))

The formula is placed in the Control Source value on the textbox property.
Click to expand...
Click to expand...
 
JP,

What would you type in the "before update" field? I figured this was the
only way to do this, but I don't know what to type in so the data from one
text box will copy over to the hidden text box.

JP said:
As you've discovered, a single field on a form can either carry a calculated
value or it can be bound to a field in a table, but not both.

When I need to do this, my approach is to place a hidden textbox on the
form. The hidden textbox is bound to a field in the form's underlying
table/query. In the BeforeUpdate event for the form, I have a line of code
that copies the value in the calculated field to the hidden textbox so that
the result is stored in the table.

You should first ask yourself why you're storing the result of a calculation
in the table. In most cases, the calculation can be re-produced with the
data that's in the table and good normalization would say that you should
just store the data needed to re-produce the calculation.

AmytDev said:
I have a form textbox called age that calculates its value on a form based on
a table. The value is displayed on the form but it is not stored onto the
table. How do I enter the calculated value into the table that the form is
based on? The formula is listed below.

=IIf(IsNull([Comp Date]),(Date()-[Req Date]),([Comp Date]-[Req Date]))

The formula is placed in the Control Source value on the textbox property.
Click to expand...
Click to expand...
 
Back
Top