expression not working???

  • Thread starter Thread starter manish
  • Start date Start date
M

manish

I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.
 
First, are you sure that you want to store these calculated values? In a
normalized database you should not store values that can be derived
(calculated) from other values. Instead, you merely recalculate the values
when needed. This is easily done by using queries with calculated field
containing expressions identical to the ones you've used on your form. The
rationale behind not storing derived values is that you have created one
field which is now dependant on values from two other fields. How do you
ensure that the dependant field is updated when the independant fields are
changed? There are no methods at the database level to do this so this
leaves you with the problem of having to do it from the application level.
Trust me, it is difficult to correctly maintain a calculated value.

Having said all this I will say that there are times when this rule should
be broken (complex calculations which greatly impact performance) and there
are also many times when the rule doesn't really apply. You have to decide
for your own application whether you really need to store a derived value.

If you decide that you do, then instead of using an expression in the
ControlSource you should create an AfterUpdate events for PF. In this event
you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this code to the AfterUpdate
event for that control also. You also need to be aware that any form that
allows updates to this table will need to include similar code to keep the
values of Stax and Total correct. You also need to prevent users from
updating the table directly (via form or query views) since there is no way
to provide this functionality from table or form view. Granted, I never let
my users into a table via table view anyway but it's worth noting here as
well.
 
Dear Sandra
I think u r Right. i undetstand what u mean. i will really
give it a try. i think it is a really very very good
sugesstion. thanks for it.
Manish
-----Original Message-----
First, are you sure that you want to store these calculated values? In a
normalized database you should not store values that can be derived
(calculated) from other values. Instead, you merely recalculate the values
when needed. This is easily done by using queries with calculated field
containing expressions identical to the ones you've used on your form. The
rationale behind not storing derived values is that you have created one
field which is now dependant on values from two other fields. How do you
ensure that the dependant field is updated when the independant fields are
changed? There are no methods at the database level to do this so this
leaves you with the problem of having to do it from the application level.
Trust me, it is difficult to correctly maintain a calculated value.

Having said all this I will say that there are times when this rule should
be broken (complex calculations which greatly impact performance) and there
are also many times when the rule doesn't really apply. You have to decide
for your own application whether you really need to store a derived value.

If you decide that you do, then instead of using an expression in the
ControlSource you should create an AfterUpdate events for PF. In this event
you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this code to the AfterUpdate
event for that control also. You also need to be aware that any form that
allows updates to this table will need to include similar code to keep the
values of Stax and Total correct. You also need to prevent users from
updating the table directly (via form or query views) since there is no way
to provide this functionality from table or form view. Granted, I never let
my users into a table via table view anyway but it's worth noting here as
well.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.
Click to expand...

.
Click to expand...
 
Dear Sandra,
This is in relation to my earlier post where u suggested
to take the information from a query. however how can my
form update the query and the table at the same time.
since the information i add goes directly to the table
which other queries use.
so if i use a query the table will not be updated and if i
use the table then there is the same problem.
Please advise further.
thanks also for replying my post.

-----Original Message-----
First, are you sure that you want to store these calculated values? In a
normalized database you should not store values that can be derived
(calculated) from other values. Instead, you merely recalculate the values
when needed. This is easily done by using queries with calculated field
containing expressions identical to the ones you've used on your form. The
rationale behind not storing derived values is that you have created one
field which is now dependant on values from two other fields. How do you
ensure that the dependant field is updated when the independant fields are
changed? There are no methods at the database level to do this so this
leaves you with the problem of having to do it from the application level.
Trust me, it is difficult to correctly maintain a calculated value.

Having said all this I will say that there are times when this rule should
be broken (complex calculations which greatly impact performance) and there
are also many times when the rule doesn't really apply. You have to decide
for your own application whether you really need to store a derived value.

If you decide that you do, then instead of using an expression in the
ControlSource you should create an AfterUpdate events for PF. In this event
you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this code to the AfterUpdate
event for that control also. You also need to be aware that any form that
allows updates to this table will need to include similar code to keep the
values of Stax and Total correct. You also need to prevent users from
updating the table directly (via form or query views) since there is no way
to provide this functionality from table or form view. Granted, I never let
my users into a table via table view anyway but it's worth noting here as
well.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.
Click to expand...

.
Click to expand...
 
Actually if you base your form on an updatable query, your form will still
update the underlying table. If you do a simple select query with a few
calculated fields your query should be updatable. Start by creating the
query. Then test the query by opening it in data view. Can you add/edit
data? If yes, then the query is updatable and you can use it as the
Recordsource for your form.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear Sandra,
This is in relation to my earlier post where u suggested
to take the information from a query. however how can my
form update the query and the table at the same time.
since the information i add goes directly to the table
which other queries use.
so if i use a query the table will not be updated and if i
use the table then there is the same problem.
Please advise further.
thanks also for replying my post.

-----Original Message-----
First, are you sure that you want to store these calculated values?
In a normalized database you should not store values that can be
derived (calculated) from other values. Instead, you merely
recalculate the values when needed. This is easily done by using
queries with calculated field containing expressions identical to
the ones you've used on your form. The rationale behind not storing
derived values is that you have created one field which is now
dependant on values from two other fields. How do you ensure that
the dependant field is updated when the independant fields are
changed? There are no methods at the database level to do this so
this leaves you with the problem of having to do it from the
application level. Trust me, it is difficult to correctly maintain a
calculated value.

Having said all this I will say that there are times when this rule
should be broken (complex calculations which greatly impact
performance) and there are also many times when the rule doesn't
really apply. You have to decide for your own application whether
you really need to store a derived value.

If you decide that you do, then instead of using an expression in the
ControlSource you should create an AfterUpdate events for PF. In
this event you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this code to the
AfterUpdate event for that control also. You also need to be aware
that any form that allows updates to this table will need to include
similar code to keep the values of Stax and Total correct. You also
need to prevent users from updating the table directly (via form or
query views) since there is no way to provide this functionality
from table or form view. Granted, I never let my users into a table
via table view anyway but it's worth noting here as well.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.
Click to expand...

.
Click to expand...
Click to expand...
 
Dear Sandra
how can i make an existing query updatable????
i already have many queries . please advise.
-----Original Message-----
Dear Sandra
I think u r Right. i undetstand what u mean. i will really
give it a try. i think it is a really very very good
sugesstion. thanks for it.
Manish
-----Original Message-----
First, are you sure that you want to store these calculated values? In a
normalized database you should not store values that can be derived
(calculated) from other values. Instead, you merely recalculate the values
when needed. This is easily done by using queries with calculated field
containing expressions identical to the ones you've used on your form. The
rationale behind not storing derived values is that you have created one
field which is now dependant on values from two other fields. How do you
ensure that the dependant field is updated when the independant fields are
changed? There are no methods at the database level to
Click to expand...
do
this so this
leaves you with the problem of having to do it from the application level.
Trust me, it is difficult to correctly maintain a calculated value.

Having said all this I will say that there are times
Click to expand...
when
this rule should
be broken (complex calculations which greatly impact performance) and there
are also many times when the rule doesn't really apply. You have to decide
for your own application whether you really need to
Click to expand...
store
a derived value.
If you decide that you do, then instead of using an expression in the
ControlSource you should create an AfterUpdate events
Click to expand...
for
PF. In this event
you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this
Click to expand...
code
to the AfterUpdate
event for that control also. You also need to be aware that any form that
allows updates to this table will need to include
Click to expand...
similar
code to keep the
values of Stax and Total correct. You also need to prevent users from
updating the table directly (via form or query views) since there is no way
to provide this functionality from table or form view. Granted, I never let
my users into a table via table view anyway but it's worth noting here as
well.
Click to expand...
this
newsgroup.
I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.
Click to expand...

.
Click to expand...
.
Click to expand...
 
Here are a couple of resource that should help you determine when a query is
updatable:

Online help - in Answer wizard type the following:
"When can I update data from a query?" - open the article by the same name.

ACC2000: How to Edit Records in Related Tables in a Microsoft Access
Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;304474

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear Sandra
how can i make an existing query updatable????
i already have many queries . please advise.
-----Original Message-----
Dear Sandra
I think u r Right. i undetstand what u mean. i will really
give it a try. i think it is a really very very good
sugesstion. thanks for it.
Manish
-----Original Message-----
First, are you sure that you want to store these calculated values?
In a normalized database you should not store values that can be
derived (calculated) from other values. Instead, you merely
recalculate the values when needed. This is easily done by using
queries with calculated field containing expressions identical to
the ones you've used on your form. The rationale behind not storing
derived values is that you have created one field which is now
dependant on values from two other fields. How do you ensure that
the dependant field is updated when the independant fields are
changed? There are no methods at the database level to do this so
this leaves you with the problem of having to do it from the
application level. Trust me, it is difficult to correctly maintain
a calculated value.

Having said all this I will say that there are times when this rule
should be broken (complex calculations which greatly impact
performance) and there are also many times when the rule doesn't
really apply. You have to decide for your own application whether
you really need to store a derived value.

If you decide that you do, then instead of using an expression in
the ControlSource you should create an AfterUpdate events for PF.
In this event you would have the following:

Me.Stax=me.PF*.08
Me.Total=me.PF+me.ST

I don't know what ST is - you would need to add this code to the
AfterUpdate event for that control also. You also need to be aware
that any form that allows updates to this table will need to
include similar code to keep the values of Stax and Total correct.
You also need to prevent users from updating the table directly
(via form or query views) since there is no way to provide this
functionality from table or form view. Granted, I never let my
users into a table via table view anyway but it's worth noting here
as well.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

manish wrote:
I have three fields (other than others) in a table named
say PF, STAX, TOTAL.
I have made a form where the user will enter the
information. However i only want to enter the PF amount.
STAX will be calculated automatically by the formula
=([PF] * .08)
and similarly TOTAL will be calculated as
=[PF]+[ST]
i have entered these expressions in the related Control
source. and it is working fine in the form where it is
calculated automatically. but the table is not being
updated.
please help.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top