Saving a calculated field

[Dave Couch]

First, yes I have read the threads on storing a calculated field and that it
is bad mojo to do that. However, I have pay data that I calculate and input
to a database and it must be able to be reconciled with our ADP data. So I
need the ability to change and fix the data so it does not change as a result
of recalculations. I have a form with a field that calculates the pay based
on hours and pay rate. I have another field (the "copy" field) next to that
one that has the control source set to the database field. I have set the
default value of that field to be equal to the calculated field. When I
first go into the form, the "copy" field is updated with the calculated info.
However, if I change the input data and the calculated field updates, the
"copy" field does not. How can I get the "copy" field to update. By the
way, Access 2003 on WinXP.

 

[Jeanette Cunningham]

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

 

[John W. Vinson]

First, yes I have read the threads on storing a calculated field and that it
is bad mojo to do that. However, I have pay data that I calculate and input
to a database and it must be able to be reconciled with our ADP data. So I
need the ability to change and fix the data so it does not change as a result
of recalculations. I have a form with a field that calculates the pay based
on hours and pay rate. I have another field (the "copy" field) next to that
one that has the control source set to the database field. I have set the
default value of that field to be equal to the calculated field. When I
first go into the form, the "copy" field is updated with the calculated info.
However, if I change the input data and the calculated field updates, the
"copy" field does not. How can I get the "copy" field to update. By the
way, Access 2003 on WinXP.

Use either the AfterUpdate of the control which triggers the calculation (or
it might be multiple controls, I don't know); or the Form,'s BeforeUpdate
event to "push" the calculated value into the bound control:

Me![copy] = Me!txtPay

 

[Dave Couch]

Jeanette, I tried that, but I don't think I am having trouble getting the
form to calculate. The calculating field updates just fine. Here' what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to store in
the database and be able to change if necessary. Its control source is the
database field "DollarsReg". The default value is =[CalcDollarsReg]. I have
tried adding Before and After Update to the field with no luck. The field
"DollarsReg" updates when I enter the form. But when I change HoursReg to a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I am not
using it right.

 

[Jeanette Cunningham]

Dave,
If I am understanding correctly, the field RegDollars is a calculated
field - its value is HoursReg*PayRate
Maybe you need to run an update query on the after update event of hoursreg
and payrate, whenever one of them changes, or maybe on the after update
event for each record on your form.

Dim db as DAO.Database
Dim strSQL as String

Set db = CurrentDb()

strSQL = "UPDATE tblTheTable " _
"SET tblTheTable.RegDollars = " & me.txtTheControlCalcDollarsReg & ""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing 'Place this line in the on exit part of the sub.

Jeanette Cunningham

Jeanette, I tried that, but I don't think I am having trouble getting the
form to calculate. The calculating field updates just fine. Here' what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to store in
the database and be able to change if necessary. Its control source is
the
database field "DollarsReg". The default value is =[CalcDollarsReg]. I
have
tried adding Before and After Update to the field with no luck. The field
"DollarsReg" updates when I enter the form. But when I change HoursReg to
a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I am
not
using it right.

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

 

[Dave Couch]

Jeanette, I guess I'm not clear in explaining myself. The form field
[calcdollarsreg] = payrate*hoursreg. This field calculates as expected each
time I change the hoursreg. The form field [regdollars] is tied to the table
field "dollarsreg". The form field has as its default value
[calcdollarsreg]. The form field of [hoursreg] defaults to 40. The
[payrate] field is based on a previous form that selects the persopn and
rate. When I go from the selection form to the "payinfo" form, the 2 fields
[calcdollarsreg] and [regdollars] both update to the correct value based on
the payrate and hours. However, when I change the hours, [calcdollarsreg]
updates, but [regdollars] does not. [regdollars] is supposed to default to
the value of [calcdollarsreg] but does not update when [calcdollarsreg]
updates. I have tried adding "afterupdate" to the default field with no
help. Is this any clearer??

Dave,
If I am understanding correctly, the field RegDollars is a calculated
field - its value is HoursReg*PayRate
Maybe you need to run an update query on the after update event of hoursreg
and payrate, whenever one of them changes, or maybe on the after update
event for each record on your form.

Dim db as DAO.Database
Dim strSQL as String

Set db = CurrentDb()

strSQL = "UPDATE tblTheTable " _
"SET tblTheTable.RegDollars = " & me.txtTheControlCalcDollarsReg & ""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing 'Place this line in the on exit part of the sub.

Jeanette Cunningham

Jeanette, I tried that, but I don't think I am having trouble getting the
form to calculate. The calculating field updates just fine. Here' what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to store in
the database and be able to change if necessary. Its control source is
the
database field "DollarsReg". The default value is =[CalcDollarsReg]. I
have
tried adding Before and After Update to the field with no luck. The field
"DollarsReg" updates when I enter the form. But when I change HoursReg to
a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I am
not
using it right.

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

First, yes I have read the threads on storing a calculated field and
that
it
is bad mojo to do that. However, I have pay data that I calculate and
input
to a database and it must be able to be reconciled with our ADP data.
So
I
need the ability to change and fix the data so it does not change as a
result
of recalculations. I have a form with a field that calculates the pay
based
on hours and pay rate. I have another field (the "copy" field) next to
that
one that has the control source set to the database field. I have set
the
default value of that field to be equal to the calculated field. When
I
first go into the form, the "copy" field is updated with the calculated
info.
However, if I change the input data and the calculated field updates,
the
"copy" field does not. How can I get the "copy" field to update. By
the
way, Access 2003 on WinXP.

 

[Jeanette Cunningham]

Dave,
The form field [regdollars] is tied to the table field "dollarsreg"?
Please clarify the above - I expect that the name of the field in the table
is the same as the name of the field on the form (the name of the textbox
can be different)
A calculated field has its datasource set to an expression, eg =
payrate*hoursreg
If you want [regdollars] to be a calculated field it needs its datasource
set to an expression.
If you want [regdollars] to be tied to a table field, it must have its
datsource set to the field in the table it is bound to.
I'm still not sure if I understand your setup, but I don't see how
[regdollars] can be a calculated control at the same time as it is bound to
a field in the table.
It looks as if [regdollars] should have its datasource expression set to
something that includes the hours, but I'm not sure what else.
It would help if you would post the datasource for [regdollars].

Jeanette Cunningham

Jeanette, I guess I'm not clear in explaining myself. The form field
[calcdollarsreg] = payrate*hoursreg. This field calculates as expected
each
time I change the hoursreg. The form field [regdollars] is tied to the
table
field "dollarsreg". The form field has as its default value
[calcdollarsreg]. The form field of [hoursreg] defaults to 40. The
[payrate] field is based on a previous form that selects the persopn and
rate. When I go from the selection form to the "payinfo" form, the 2
fields
[calcdollarsreg] and [regdollars] both update to the correct value based
on
the payrate and hours. However, when I change the hours, [calcdollarsreg]
updates, but [regdollars] does not. [regdollars] is supposed to default
to
the value of [calcdollarsreg] but does not update when [calcdollarsreg]
updates. I have tried adding "afterupdate" to the default field with no
help. Is this any clearer??

Dave,
If I am understanding correctly, the field RegDollars is a calculated
field - its value is HoursReg*PayRate
Maybe you need to run an update query on the after update event of
hoursreg
and payrate, whenever one of them changes, or maybe on the after update
event for each record on your form.

Dim db as DAO.Database
Dim strSQL as String

Set db = CurrentDb()

strSQL = "UPDATE tblTheTable " _
"SET tblTheTable.RegDollars = " & me.txtTheControlCalcDollarsReg & ""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing 'Place this line in the on exit part of the sub.

Jeanette Cunningham

Jeanette, I tried that, but I don't think I am having trouble getting
the
form to calculate. The calculating field updates just fine. Here'
what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to store
in
the database and be able to change if necessary. Its control source is
the
database field "DollarsReg". The default value is =[CalcDollarsReg].
I
have
tried adding Before and After Update to the field with no luck. The
field
"DollarsReg" updates when I enter the form. But when I change HoursReg
to
a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I am
not
using it right.

:

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

First, yes I have read the threads on storing a calculated field and
that
it
is bad mojo to do that. However, I have pay data that I calculate
and
input
to a database and it must be able to be reconciled with our ADP
data.
So
I
need the ability to change and fix the data so it does not change as
a
result
of recalculations. I have a form with a field that calculates the
pay
based
on hours and pay rate. I have another field (the "copy" field) next
to
that
one that has the control source set to the database field. I have
set
the
default value of that field to be equal to the calculated field.
When
I
first go into the form, the "copy" field is updated with the
calculated
info.
However, if I change the input data and the calculated field
updates,
the
"copy" field does not. How can I get the "copy" field to update.
By
the
way, Access 2003 on WinXP.

 

[Dave Couch]

Jeanette, yes [regdollars] is tied to the table field "dollarsreg". However,
it is not the calculated field. I want it to equal the value of the
calculated field [calcdollarsreg]. How do I post the data source for
[regdollars]??

Dave,
The form field [regdollars] is tied to the table field "dollarsreg"?
Please clarify the above - I expect that the name of the field in the table
is the same as the name of the field on the form (the name of the textbox
can be different)
A calculated field has its datasource set to an expression, eg =
payrate*hoursreg
If you want [regdollars] to be a calculated field it needs its datasource
set to an expression.
If you want [regdollars] to be tied to a table field, it must have its
datsource set to the field in the table it is bound to.
I'm still not sure if I understand your setup, but I don't see how
[regdollars] can be a calculated control at the same time as it is bound to
a field in the table.
It looks as if [regdollars] should have its datasource expression set to
something that includes the hours, but I'm not sure what else.
It would help if you would post the datasource for [regdollars].

Jeanette Cunningham

Jeanette, I guess I'm not clear in explaining myself. The form field
[calcdollarsreg] = payrate*hoursreg. This field calculates as expected
each
time I change the hoursreg. The form field [regdollars] is tied to the
table
field "dollarsreg". The form field has as its default value
[calcdollarsreg]. The form field of [hoursreg] defaults to 40. The
[payrate] field is based on a previous form that selects the persopn and
rate. When I go from the selection form to the "payinfo" form, the 2
fields
[calcdollarsreg] and [regdollars] both update to the correct value based
on
the payrate and hours. However, when I change the hours, [calcdollarsreg]
updates, but [regdollars] does not. [regdollars] is supposed to default
to
the value of [calcdollarsreg] but does not update when [calcdollarsreg]
updates. I have tried adding "afterupdate" to the default field with no
help. Is this any clearer??

Dave,
If I am understanding correctly, the field RegDollars is a calculated
field - its value is HoursReg*PayRate
Maybe you need to run an update query on the after update event of
hoursreg
and payrate, whenever one of them changes, or maybe on the after update
event for each record on your form.

Dim db as DAO.Database
Dim strSQL as String

Set db = CurrentDb()

strSQL = "UPDATE tblTheTable " _
"SET tblTheTable.RegDollars = " & me.txtTheControlCalcDollarsReg & ""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing 'Place this line in the on exit part of the sub.

Jeanette Cunningham

Jeanette, I tried that, but I don't think I am having trouble getting
the
form to calculate. The calculating field updates just fine. Here'
what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to store
in
the database and be able to change if necessary. Its control source is
the
database field "DollarsReg". The default value is =[CalcDollarsReg].
I
have
tried adding Before and After Update to the field with no luck. The
field
"DollarsReg" updates when I enter the form. But when I change HoursReg
to
a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I am
not
using it right.

:

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

First, yes I have read the threads on storing a calculated field and
that
it
is bad mojo to do that. However, I have pay data that I calculate
and
input
to a database and it must be able to be reconciled with our ADP
data.
So
I
need the ability to change and fix the data so it does not change as
a
result
of recalculations. I have a form with a field that calculates the
pay
based
on hours and pay rate. I have another field (the "copy" field) next
to
that
one that has the control source set to the database field. I have
set
the
default value of that field to be equal to the calculated field.
When
I
first go into the form, the "copy" field is updated with the
calculated
info.
However, if I change the input data and the calculated field
updates,
the
"copy" field does not. How can I get the "copy" field to update.
By
the
way, Access 2003 on WinXP.

 

[Jeanette Cunningham]

Dave,
I understand that there is a textbox called regdollars with the tablefield
dollarsreg as its datasource.
If the field [dollarsreg] is equal to the calculated textbox
[calcdollarsreg], then the field [dollarsreg] should be equal to
[payrate]*[hoursreg].
I seem to be missing something here. Please repost as a new topic for a new
pair of eyes to look at.

Jeanette Cunningham

Jeanette, yes [regdollars] is tied to the table field "dollarsreg".
However,
it is not the calculated field. I want it to equal the value of the
calculated field [calcdollarsreg]. How do I post the data source for
[regdollars]??

Dave,
The form field [regdollars] is tied to the table field "dollarsreg"?
Please clarify the above - I expect that the name of the field in the
table
is the same as the name of the field on the form (the name of the textbox
can be different)
A calculated field has its datasource set to an expression, eg =
payrate*hoursreg
If you want [regdollars] to be a calculated field it needs its datasource
set to an expression.
If you want [regdollars] to be tied to a table field, it must have its
datsource set to the field in the table it is bound to.
I'm still not sure if I understand your setup, but I don't see how
[regdollars] can be a calculated control at the same time as it is bound
to
a field in the table.
It looks as if [regdollars] should have its datasource expression set to
something that includes the hours, but I'm not sure what else.
It would help if you would post the datasource for [regdollars].

Jeanette Cunningham

Jeanette, I guess I'm not clear in explaining myself. The form field
[calcdollarsreg] = payrate*hoursreg. This field calculates as expected
each
time I change the hoursreg. The form field [regdollars] is tied to the
table
field "dollarsreg". The form field has as its default value
[calcdollarsreg]. The form field of [hoursreg] defaults to 40. The
[payrate] field is based on a previous form that selects the persopn
and
rate. When I go from the selection form to the "payinfo" form, the 2
fields
[calcdollarsreg] and [regdollars] both update to the correct value
based
on
the payrate and hours. However, when I change the hours,
[calcdollarsreg]
updates, but [regdollars] does not. [regdollars] is supposed to
default
to
the value of [calcdollarsreg] but does not update when [calcdollarsreg]
updates. I have tried adding "afterupdate" to the default field with
no
help. Is this any clearer??

:

Dave,
If I am understanding correctly, the field RegDollars is a calculated
field - its value is HoursReg*PayRate
Maybe you need to run an update query on the after update event of
hoursreg
and payrate, whenever one of them changes, or maybe on the after
update
event for each record on your form.

Dim db as DAO.Database
Dim strSQL as String

Set db = CurrentDb()

strSQL = "UPDATE tblTheTable " _
"SET tblTheTable.RegDollars = " & me.txtTheControlCalcDollarsReg &
""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing 'Place this line in the on exit part of the sub.

Jeanette Cunningham

Jeanette, I tried that, but I don't think I am having trouble
getting
the
form to calculate. The calculating field updates just fine. Here'
what I
have.

[CalcDollarsReg]=HoursReg*PayRate. That field works correctly.
The next field is [RegDollars]. This is the field that I want to
store
in
the database and be able to change if necessary. Its control source
is
the
database field "DollarsReg". The default value is
=[CalcDollarsReg].
I
have
tried adding Before and After Update to the field with no luck. The
field
"DollarsReg" updates when I enter the form. But when I change
HoursReg
to
a
new number, the calculated field updates, but the Database field,
"DollarsReg" does not.

The recalc command does not seem to do anything additional, unless I
am
not
using it right.

:

Dave,
Me.Recalc
will force the form to recalculate.

Jeanette Cunningham

First, yes I have read the threads on storing a calculated field
and
that
it
is bad mojo to do that. However, I have pay data that I
calculate
and
input
to a database and it must be able to be reconciled with our ADP
data.
So
I
need the ability to change and fix the data so it does not change
as
a
result
of recalculations. I have a form with a field that calculates
the
pay
based
on hours and pay rate. I have another field (the "copy" field)
next
to
that
one that has the control source set to the database field. I
have
set
the
default value of that field to be equal to the calculated field.
When
I
first go into the form, the "copy" field is updated with the
calculated
info.
However, if I change the input data and the calculated field
updates,
the
"copy" field does not. How can I get the "copy" field to
update.
By
the
way, Access 2003 on WinXP.