Saving a calculated field

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.
0
Utf
2/8/2008 10:14:01 PM
access.forms 6864 articles. 2 followers. Follow

8 Replies
1047 Views

Similar Articles

[PageSpeed] 18

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

Jeanette Cunningham

"Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
> 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. 


0
Jeanette
2/8/2008 11:15:13 PM
On Fri, 8 Feb 2008 14:14:01 -0800, Dave Couch
<DaveCouch@discussions.microsoft.com> wrote:

>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

--
             John W. Vinson [MVP]
0
John
2/8/2008 11:16:38 PM
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" wrote:

> Dave,
> Me.Recalc
> will force the form to recalculate.
> 
> Jeanette Cunningham
> 
> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
> > 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. 
> 
> 
> 
0
Utf
2/8/2008 11:53:02 PM
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

"Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
news:62D88EFF-4F90-4CBB-8CD2-0160BD5CDCD1@microsoft.com...
> 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" wrote:
>
>> Dave,
>> Me.Recalc
>> will force the form to recalculate.
>>
>> Jeanette Cunningham
>>
>> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
>> > 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.
>>
>>
>> 


0
Jeanette
2/9/2008 12:46:04 AM
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??  

"Jeanette Cunningham" wrote:

> 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
> 
> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
> news:62D88EFF-4F90-4CBB-8CD2-0160BD5CDCD1@microsoft.com...
> > 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" wrote:
> >
> >> Dave,
> >> Me.Recalc
> >> will force the form to recalculate.
> >>
> >> Jeanette Cunningham
> >>
> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
> >> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
> >> > 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.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/9/2008 5:21:00 PM
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

"Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
news:4F70E18D-1D34-4EB9-9029-ED8101593162@microsoft.com...
> 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??
>
> "Jeanette Cunningham" wrote:
>
>> 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
>>
>> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> news:62D88EFF-4F90-4CBB-8CD2-0160BD5CDCD1@microsoft.com...
>> > 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" wrote:
>> >
>> >> Dave,
>> >> Me.Recalc
>> >> will force the form to recalculate.
>> >>
>> >> Jeanette Cunningham
>> >>
>> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> >> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeanette
2/9/2008 9:30:27 PM
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]??

"Jeanette Cunningham" wrote:

> 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
> 
> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
> news:4F70E18D-1D34-4EB9-9029-ED8101593162@microsoft.com...
> > 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??
> >
> > "Jeanette Cunningham" wrote:
> >
> >> 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
> >>
> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
> >> news:62D88EFF-4F90-4CBB-8CD2-0160BD5CDCD1@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> Dave,
> >> >> Me.Recalc
> >> >> will force the form to recalculate.
> >> >>
> >> >> Jeanette Cunningham
> >> >>
> >> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
> >> >> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/9/2008 9:40:01 PM
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


"Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message 
news:4511647F-8C91-42F6-8B9C-DA4FC25EA49E@microsoft.com...
> 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]??
>
> "Jeanette Cunningham" wrote:
>
>> 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
>>
>> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> news:4F70E18D-1D34-4EB9-9029-ED8101593162@microsoft.com...
>> > 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??
>> >
>> > "Jeanette Cunningham" wrote:
>> >
>> >> 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
>> >>
>> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> >> news:62D88EFF-4F90-4CBB-8CD2-0160BD5CDCD1@microsoft.com...
>> >> > 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" wrote:
>> >> >
>> >> >> Dave,
>> >> >> Me.Recalc
>> >> >> will force the form to recalculate.
>> >> >>
>> >> >> Jeanette Cunningham
>> >> >>
>> >> >> "Dave Couch" <DaveCouch@discussions.microsoft.com> wrote in message
>> >> >> news:083859D1-6FC2-41EE-A09E-BEC98641143C@microsoft.com...
>> >> >> > 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeanette
2/9/2008 10:12:28 PM
Reply:

Similar Artilces:

Mathematical Calculations in a form linked back to a table
Hello I palnning on implementing calculation between two fields, and the result within another field. Suppose I have three fields called A B and C respectively. In the form I plan on Inputing a value for[A] and Inputing a value for [B]. Now, Field [C] will calculate [A]-[B]. When filed [C] is calculated, it is not in the table, like A and B are. I would like to know how to solve this problem. It is rarely necessary to store a calculated value in a table in Access. Much more commonly, a form (as you are using) or a query is used to do the calculation "on the fly", as it were. ...

saving series of OUTLOOK messages in WORD
Hello, A few weeks ago I posted a question in this newsgroup regarding extracting attachments in Outlook, and the answer was very helpful. This time I would like to save the actual message from an e-mail into a Word document.(There are no attachments) I know you can "SAVE AS" the contents of one particular message, but I would like to automate the process for multiple messages. Suppose in a specific Outlook Path: PersonalFolder\Inbox\20041215 I have 50 emails, and I would like to save the contents of all of them (one after the other) in ONE WORD text or document file called 200...

Can I Save an Excel spread sheet as html text
Can I Save an Excel spread sheet as html text (ie) just a simple text file, having nothing to do with webpages at the moment ? I will eventually want to apply the text in the simple text file to the source for a web page. -- Joe Miller ------------------------------------------------------------------------ Joe Miller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29900 View this thread: http://www.excelforum.com/showthread.php?threadid=499012 Yes: File > Save As... and specify html -- Gary''s Student "Joe Miller" wrote: > >...

WorksheetFunction Calculation Error
errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" & errorWS_startRow - 1) The above line of code is always 1, even when errorWS_startRow is much > 1, i.e: errorWS_startRow=144, 749 etc. Any ideas what is going on? I'd suggest including the sheet reference for the CountA; something like this (untested) errorWS.Range("B3").Value = Application.WorksheetFunction.CountA(errorWS.Range("A5:A" & errorWS_startRow - 1)) "Ayo" wrote: > errorWS.Range("B3").Value = Application.W...

image field where clause
I have sql table (20million records)with a image field A. I need to set up a where clause like ---- select * from tablea where A(image type) is not null. that query takes forever. How do I optimize that query? Thanks, Mecn (mecn@yahoo.com) writes: > I have sql table (20million records)with a image field A. I need to set > up a where clause like ---- select * from tablea where A(image type) is > not null. > that query takes forever. > How do I optimize that query? About how many rows in the table do you expect to have a non-NULL value in A? If it 16 mil...

Why do old, unused values still show in my Pivot Page fields?
When I originally created a PivotTable based on an Access database, for one of the page fields I used a certain set of range values (i.e. 560-600, 600-650, etc.). We've decided the range values need to be different & I recreated & replaced the data in the Access DB with the new range values. But, my PivotTable still shows the old, unused values along with the new, correct ones. I don't want to recreate the PivotTable because I've created a large number of calculated fields. How do I get rid of the unwanted range values in that Page field? There are instructions ...

Saving a spreadsheet off the web
When I save a spreading after modifying it off the web, the file permission are changed and anyone other than the 'writer' cannot access it. I would like to have a setting where anyone can access, read or write (writing is off the web). Please let me know if this can be done. Thanks, R ...

Simple calculated field problem
Hi, I am very new to Access and am trying to do something I think should be reasonably simple, but can't work out the syntax I need. My database is to track weight loss. I have only one table at the moment (this will grow but my problem only involves a single table), which is called 'Measurements'. The table has only 3 fields, 'Date', 'Weight' and 'Fat%'. I would like to be able to track weight loss from one day to the next, so am trying to make a query that shows, for each day, the values in the Weight and Fat% fields, and a calculated field tha...

error opening saved .pub website
When I try to open the website I saved as a .pub document yesterday, the following error message displays "Publisher has detected a prolem in the file you are trying to open. To protect your computer, Publisher will not open this file." Are there any suggestions for what I can do? I put so much work into it yesterday and really want to recover it. Thanks! Do you have Norton? How to use Office programs with the Norton AntiVirus Office plug-in http://support.microsoft.com/kb/329820/en-us Error message when you try to open a publication in Publisher: "Publisher cannot op...

Help with 'Save' command in VBA
Hello, I am trying to create a macro that saves a file after the file has been printed to avoid the prompt which typically appears onscreen which asks the user if they want to save the current open file. As you know, Word will prompt you to save a file that you had already saved if you attempt to close the file after it has been printed. Printing the file seems to store printer data into the file which of course would technically count as a changed file, subsequently prompting the user to save changes. I try to avoid this message prompt with the “ActiveDocument.Save” command at the ...

Calculating tiome between 17 PM and 6 AM
I thought I was quite good at date/time calculations, but apparently not good enough. I use the following formularto calculate how much time between start time and end time lies between 5PM and 6AM. =(B1<=A1)*(1-(C2)+(C1))+MIN((C1),B1)-MIN((C1),A1)+MAX((C2),B1)-MAX((C2),A1) A1 is start time, B1 is End time, C1 is Early time limit (6 AM) and C2 is late time limit (5 PM). The formula does the job if i just enter fx 03:00 AM in A1 and 7:00 PM Here is my challenge: I need to add the date to the Entry, so that I enter 09-01-08 03:00 PM and 09-01-08 7:00 PM. I have tried a l...

custom primary key using multiple fields
I would like to create my own primary key using a concatenation of existing fields: eventnumber & date & time. How do I go about doing this? Any help is greatly appreciated. Thank you in advance. Why? Access is capable of using a primary key that's comprised of up to ten separate fields. Concatenating values into a single field is rarely a good idea. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "DGregg" <DGregg@discussions.microsoft.com> wrote in message news:BD804869-FD9D-473C-826B-48FB2B2BEC42@microsoft.com... >I...

Date field showing up as day-month when it should be mm/yy
I am exporting from a web report where the field shows up as MM/YY. When I export to Excel the column shows up as DD-Month. So 05/09 becomes 9-Dec. 05/05 becomes 5-May. I cant get it to format correctly. Any ideas? On Thu, 22 Sep 2011 12:34:35 -0700 (PDT), Kristen <kristen.hallock@gmail.com> wrote: >I am exporting from a web report where the field shows up as MM/YY. >When I export to Excel the column shows up as DD-Month. So 05/09 >becomes 9-Dec. 05/05 becomes 5-May. I cant get it to format correctly. > >Any ideas? You are going to have to import the date as "Text...

How can you Force "0s" in the field
I have a field named Point Code, DataType of Number and has an InputMask of 000\-000\-000;0;_ It looks like this -- 001-041-050 in the table view but when i tried to show it in form view, it looks like this -- 001-041-50 . How can i make this lokk like the format in table view? On 15 May 2007 08:45:51 -0700, emerlita@shaw.ca wrote: > I have a field named Point Code, DataType of Number and has an > InputMask of 000\-000\-000;0;_ > It looks like this -- 001-041-050 in the table view but when i tried > to show it in form view, it looks like this -- > 001-041-50 . How can i make ...

Save as... on intranet
The below code works well if I run the file from my workstation, however when the same file is moved to an intranet server, and when you click on the link for this file, and submit (which runs the code), the code always bombs on the Save as... I have made sure that the folder on the server has read, write permissions. Also I have tried a few syntax variations but with no success. How do you save a file on an Intranet server??? (UNC with forward/slashes; with backward\slashes, using the URL Http://; or something else)??? Dim wb As Workbook Dim WBname As String Application.ScreenUpd...

Adding a Field to a Function
This function gives the ,father,mother.age,sex to a horse if he has no name or if named shows his Name, I want to add one field to both Names the field is called "Extra" Thanks for any help ..................Bob Function funGetHorseName(lngInvoiceID As Long, lngHorseID As Long) As String Dim recHorseName As New ADODB.Recordset, strAge As String, strName As String recHorseName.Open "SELECT * FROM tblInvoice WHERE InvoiceID=" _ & lngInvoiceID & " AND HorseID=" & lngHorseID, CurrentProject.Connection, adOpenDynamic, adLockOptimistic If r...

trim fields
Hi, When I create a mail merge in Publisher 2007 and I use an existing Access database, how would I trim the field lengthes in my template when mail merging? Thanks Steve I would suggest trimming them in Access before the merge. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Steve" <adelxt@hotmail.com> wrote in message news:eIzSY%23EFKHA.4220@TK2MSFTNGP02.phx.gbl... > Hi, > > When I create a mail merge in Publisher 2007 and I use an existing Access > database, how would I trim the field lengt...

Please help with multiple field Link Criteria
Form_1 contains cbo_Application_Acronym and a subform containing DataElement_Name. Form_2 is 'keyed' by Application_Acronym and DataElement_Name. From the subform, I want to link to Form_2 using both fields. Here is the code for stLinkCriteria: stLinkCriteria = "[DataElement_Name]=""" & Me![DataElement_Name] & _ """ And [Application_Acronym] = "" & Me.Parent.Form![cbo_Application_Acronym] & """ The syntax for this string absolutely baffles me. I ran the debugger. Me![DataElement_Name]...

calculate age #2
I know you can calculate your age in days by date born A1= 30/09/1978 and B1= today() = A1-B1 but can I calculate days and time born - today's date and time? date born A1= 30/09/1972 16:20 and B1= now() To find the result number of days and hours Thanks Rob www.CrashMatRob.com Yes, of course you can - just apply a custom format to the cell to display days and time. Note, though, that NOW() changes more often than TODAY() - everytime you recalculate the sheet the value will be updated. You will also have to subtract the birthdate and time away from NOW,...

Can't eliminate flicking of chart on every calculate #3
I am using Excel 2002. I have real time data being charted , and I manually calculate ranges and sheets as needed. This is not a problem. I have recently added charts to plot this data. Every time there is a recalculation anywhere on the sheet (even areas not being charted) the charts flicker. Since there is new data multiple times per second , this is a problem. Yes, I know about Application.DisplayAlerts = False, and tried inserting that before EVERY calculation call. I even went so far as to use the LockWindowUpdate api call, which freezes the entire app. Unfortunately , as ...

Want to Save Popup Started on its own
I have a test file that I put macros in. It has recently started the pop up "Do you want to save changes in test.xls" when no changes have been made. I have no auto macro and the only change before this started happening was a change to the pagesetup line in one macro. What do I look for. Thanks very much. To start, you can look for RAND functions or current date/time functions. "Bill" <anonymous@discussions.microsoft.com> wrote in message news:014001c3d60d$107a3830$a301280a@phx.gbl... > I have a test file that I put macros in. It has recently > s...

What records does HITB save in SEE30303?
In the 10.0HITB.pdf manual, it says the following: 'The information that prints on the report is stored in the new HITB Inventory Transaction History Detail (SEE30303) table. All inventory transactions and cost adjustments will create records in the SEE30303 table.' Does this mean *all* inventory transactions will be written to this table going forward after the reset is run (ending up with a duplication between this table and the normal inventory transaction tables)? Or, is it only used to store the records for the report you're currently running? I'd hate to think this...

Date Calculation
Hi I have an unbound box on my form which shows how many days are left between the dates, 'Project Arrived' and 'Project Required' this works great. What I want to know is whether I could get this box to stop counting when a third date, 'project complete' is filled in? Any advice would be greatly appreciated Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 In order to help we need to see the code and the code's location that you're using to do this calculation. -- There's A...

Field Services Hot Fixes
Hello, Does anyone know if there is a place within GP 7.5 which lists the last Field Service hot fix installed? -- Thank you, Jessie I have passed your question on to two of our members who are the most knowledgable about hot fixes. I have asked that either they repsond to you directly or get back to me if they have an answer. Meanwhile, as a fellow user of Great Plains and the Field Service modules, we are putting together a list of other users who might be willing to share their expertise, experiences, ideas, problems, etc. with other Field Service users. For those of you who m...

trial version--saving/converting
i downloaded the trial version, opened an old office 2000 database, but was not able to save or copy or really do anything with it. what's the point of the trial? or am i doing something wrong? secondly, is there any way for me to convert this old .mdb file into a .wdb file (since ms works database is the only database program that came with my new computer)? or am i going to be forced to buy ms office if i ever want to "access" this file again? Unlike other Office products, Access doesn't have a Save As option on the File menu. You opened a MDB file when you start...