Storing Calculated field...I know..I know...

[Annemarie]

I know it is completely frowned upon to store a calculated field in a
table...BUT I think I need to...

I have a form that is used to calculate the value based on a formula.
This form is pulled up based on a value selected in a field on a
previous form. There will be several instances like this where I need
to store the calculated value. The field I need to store is a
calculation of several unbound fields, so the calculation formula is in
the control source of the final field. I need this field to store that
value in a table, but the calculation occupies the control source so it
cannot. The value stored in the table is used for later calculations.
The form is generic, and will be used on multiple occasions, each
producing a different calculated number. How can I get the calculation
to store in the control source field and still perform the calculation?
The field in the table the calculation will be stored in has many
different sources of data. In some cases a number is simply typed and
inputted in. In some cases a value is chosen based on a toggle group
which sends the correct number to the field. In some cases a
calculated number needs to be sent to the field based on a particular
form (there will be 15-20 different forms producing calculations for
this purpose).

Does this make any sense??

Say the field I need to store info in is Reqt Alg.

In the form, the control source is
=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

All of the fields the calculation is based on are unbound...that data
is not important and there are so many different forms I need for this
purpose it would create an additional 15-20 tables to store unnecessary
data. I only need the calculated value.

Please...any help I can get is welcomed!

 

[Guest]

I believe you have justified storing a calculated field in this instance.
Remember, the exception to the rule is that "If you have all the other data
necessary to recreate the calculation, then...." Since you will not have
those data available, storing is okay. Rather than the Control Source, have
you considered or tried using the Defalut Value?

 

[Annemarie]

I tried puting the formula in the default value, and the calculation
will pull up. It for some reason is not storing in the table, and if I
need to change any part of the fields that feed into the calculation,
the value does not pull up. Any suggestions??

I believe you have justified storing a calculated field in this instance.
Remember, the exception to the rule is that "If you have all the other data
necessary to recreate the calculation, then...." Since you will not have
those data available, storing is okay. Rather than the Control Source, have
you considered or tried using the Defalut Value?

I know it is completely frowned upon to store a calculated field in a
table...BUT I think I need to...

I have a form that is used to calculate the value based on a formula.
This form is pulled up based on a value selected in a field on a
previous form. There will be several instances like this where I need
to store the calculated value. The field I need to store is a
calculation of several unbound fields, so the calculation formula is in
the control source of the final field. I need this field to store that
value in a table, but the calculation occupies the control source so it
cannot. The value stored in the table is used for later calculations.
The form is generic, and will be used on multiple occasions, each
producing a different calculated number. How can I get the calculation
to store in the control source field and still perform the calculation?
The field in the table the calculation will be stored in has many
different sources of data. In some cases a number is simply typed and
inputted in. In some cases a value is chosen based on a toggle group
which sends the correct number to the field. In some cases a
calculated number needs to be sent to the field based on a particular
form (there will be 15-20 different forms producing calculations for
this purpose).

Does this make any sense??

Say the field I need to store info in is Reqt Alg.

In the form, the control source is
=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

All of the fields the calculation is based on are unbound...that data
is not important and there are so many different forms I need for this
purpose it would create an additional 15-20 tables to store unnecessary
data. I only need the calculated value.

Please...any help I can get is welcomed!

 

[John Vinson]

Say the field I need to store info in is Reqt Alg.

In the form, the control source is
=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

Have TWO textboxes on the Form - one with this expression as its
Control Source, and the other with [Reqt Alg] as its control source.
(I'm assuming that [Reqt Alg] is a field in the Form's Recordsource).

In the Form's BeforeUpdate event, copy the first control's value into
this second control.

John W. Vinson[MVP]

 

[Guest]

I suggest you try John's solution.

I tried puting the formula in the default value, and the calculation
will pull up. It for some reason is not storing in the table, and if I
need to change any part of the fields that feed into the calculation,
the value does not pull up. Any suggestions??

I believe you have justified storing a calculated field in this instance.
Remember, the exception to the rule is that "If you have all the other data
necessary to recreate the calculation, then...." Since you will not have
those data available, storing is okay. Rather than the Control Source, have
you considered or tried using the Defalut Value?

I know it is completely frowned upon to store a calculated field in a
table...BUT I think I need to...

I have a form that is used to calculate the value based on a formula.
This form is pulled up based on a value selected in a field on a
previous form. There will be several instances like this where I need
to store the calculated value. The field I need to store is a
calculation of several unbound fields, so the calculation formula is in
the control source of the final field. I need this field to store that
value in a table, but the calculation occupies the control source so it
cannot. The value stored in the table is used for later calculations.
The form is generic, and will be used on multiple occasions, each
producing a different calculated number. How can I get the calculation
to store in the control source field and still perform the calculation?
The field in the table the calculation will be stored in has many
different sources of data. In some cases a number is simply typed and
inputted in. In some cases a value is chosen based on a toggle group
which sends the correct number to the field. In some cases a
calculated number needs to be sent to the field based on a particular
form (there will be 15-20 different forms producing calculations for
this purpose).

Does this make any sense??

Say the field I need to store info in is Reqt Alg.

In the form, the control source is
=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

All of the fields the calculation is based on are unbound...that data
is not important and there are so many different forms I need for this
purpose it would create an additional 15-20 tables to store unnecessary
data. I only need the calculated value.

Please...any help I can get is welcomed!

 

[Annemarie]

John,

As I am not very code savvy..how would I go about doing this..."In the
Form's BeforeUpdate event, copy the first control's value into this
second control. " ??? Please help!

Annemarie

I suggest you try John's solution.

I tried puting the formula in the default value, and the calculation
will pull up. It for some reason is not storing in the table, and if I
need to change any part of the fields that feed into the calculation,
the value does not pull up. Any suggestions??

I believe you have justified storing a calculated field in this instance.
Remember, the exception to the rule is that "If you have all the other data
necessary to recreate the calculation, then...." Since you will not have
those data available, storing is okay. Rather than the Control Source, have
you considered or tried using the Defalut Value?

:

I know it is completely frowned upon to store a calculated field in a
table...BUT I think I need to...

I have a form that is used to calculate the value based on a formula.
This form is pulled up based on a value selected in a field on a
previous form. There will be several instances like this where I need
to store the calculated value. The field I need to store is a
calculation of several unbound fields, so the calculation formula is in
the control source of the final field. I need this field to store that
value in a table, but the calculation occupies the control source so it
cannot. The value stored in the table is used for later calculations.
The form is generic, and will be used on multiple occasions, each
producing a different calculated number. How can I get the calculation
to store in the control source field and still perform the calculation?
The field in the table the calculation will be stored in has many
different sources of data. In some cases a number is simply typed and
inputted in. In some cases a value is chosen based on a toggle group
which sends the correct number to the field. In some cases a
calculated number needs to be sent to the field based on a particular
form (there will be 15-20 different forms producing calculations for
this purpose).

Does this make any sense??

Say the field I need to store info in is Reqt Alg.

In the form, the control source is
=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

All of the fields the calculation is based on are unbound...that data
is not important and there are so many different forms I need for this
purpose it would create an additional 15-20 tables to store unnecessary
data. I only need the calculated value.

Please...any help I can get is welcomed!

 

[John Vinson]

John,

As I am not very code savvy..how would I go about doing this..."In the
Form's BeforeUpdate event, copy the first control's value into this
second control. " ??? Please help!

Please post the Recordsource query of your form (post the SQL, or the
table fieldnames and datatypes if you have the form based on a table),
and the names of the relevant controls on the form.

John W. Vinson[MVP]

 

[Annemarie]

The form is based on a query named "Subfunctions Algorithm Form Query"
that includes 14 fields and is based on one table...mostly text fields
with some number fields. The only bound field on the table needs to go
to field subfunctions.[reqt alg] (which is a number field). The
formula in the control source of the field right now is

=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

I have another box next to it with [reqt alg] as the control source.
Is this everything you need?

Annemarie

 

[John Vinson]

The
formula in the control source of the field right now is

=((([TypeIMod]*[TypeIHDW])+([TypeIIMod]*[TypeIIHDW]))*([StdDepth]/[ConvtoSY]))

I have another box next to it with [reqt alg] as the control source.
Is this everything you need?

well, you're leaving me to guess a bit. You did not say and I have no
idea what the NAME properties of these two textboxes might be. I'll
call them txtCalc and txtBound respectively, just change these to
whatever you're using for the field.

Possibly uneeded detail included for completeness:

Open the form in design view. View its Properties. On the Events tab
find the BeforeUpdate event; click the ... icon by it; select Code
Builder. Access will give you the SUb and End Sub lines. Edit it to

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBound = Me!txtCalc
End Sub


You can (and probably should) add some validation code before this
line, say to see if all of the fields that go into the calculation
have reasonable values (or for that matter any value at all).

John W. Vinson[MVP]