J
JMorrell
I have a control on a form which holds a calculated value. How can I take this value and update a table with it
tia
jeff
tia
jeff
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
W
Wayne Morgan
How you do it will depend on what causes the calculated field to change.
However, why do you want to store it. It is bad standardization to store
calculated values except in a few specific circumstances. It is better just
to calculate the value whenever you need it.
--
Wayne Morgan
Microsoft Access MVP
However, why do you want to store it. It is bad standardization to store
calculated values except in a few specific circumstances. It is better just
to calculate the value whenever you need it.
--
Wayne Morgan
Microsoft Access MVP
this value and update a table with it?JMorrell said:I have a control on a form which holds a calculated value. How can I take
J
John Vinson
I have a control on a form which holds a calculated value. How can I take this value and update a table with it?
tia,
jeff
Well... ordinarily one would not want to do so.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
If you REALLY feel that you need to store it (perhaps as a
point-in-time where the underlying values will change but you need the
current value kept), use the Form's BeforeUpdate event. Have a second
control bound to the table field and use code like
Me!txtBoundControl = Me!txtCalcControl
W
Wayne Morgan
You should be able to operate from a "base value" then go through all of the
accrued and used leave to arrive at a balance.
As John mentioned, if the calculated value changes when you change other
fields on the form, you can simply have a hidden, bound control and update
it to the calculated control's value in the form's BeforeUpdate event.
The linked worked, thanks.
accrued and used leave to arrive at a balance.
As John mentioned, if the calculated value changes when you change other
fields on the form, you can simply have a hidden, bound control and update
it to the calculated control's value in the form's BeforeUpdate event.
The linked worked, thanks.
J
JMorrell
----- John Vinson wrote: ----
On Mon, 1 Mar 2004 11:41:05 -0800, "JMorrell
Well... ordinarily one would not want to do so
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it
in the control source of a Form or a Report textbox
If you REALLY feel that you need to store it (perhaps as
point-in-time where the underlying values will change but you need th
current value kept), use the Form's BeforeUpdate event. Have a secon
control bound to the table field and use code lik
Me!txtBoundControl = Me!txtCalcContro
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
OK! You've all convinced me.
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself
tia
Jeff
On Mon, 1 Mar 2004 11:41:05 -0800, "JMorrell
I have a control on a form which holds a calculated value. How can I take this value and update a table with it
jef
Well... ordinarily one would not want to do so
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it
in the control source of a Form or a Report textbox
If you REALLY feel that you need to store it (perhaps as
point-in-time where the underlying values will change but you need th
current value kept), use the Form's BeforeUpdate event. Have a secon
control bound to the table field and use code lik
Me!txtBoundControl = Me!txtCalcContro
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
OK! You've all convinced me.
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself
tia
Jeff
J
John Vinson
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself?
You don't.
Instead, put an expression in a vacant Field cell in a Query:
ShowSickbal: IIF([Leavetype] = "S", [compsickbal]-[leavehours],
[tblleavebal].[sickbal])
assuming that compsickbal is another field in the query. If it's not,
replace compsickbal with sickbal.