Formula in Form Not updating Table

[Guest]

Hi,

I created a form from a source table. In that form I made a simple
computation wich will add Amt1 + Amt2 and it equals to Total

Control source: =Amt1+Amt2
Name: Total

Ex:
Table: Form:
Amt1 Amt2 Total Amt1: 100
100 200 0 Amt2: 200
Total: 300


Everything seems okay but when I check my table, the Total column is not
updating. Actually, I am not really sure if it supposed to be updated with
the Total amount in my form coz all sample database that I referred to, they
don't have a column on there table for there calculation in form. Is it
possible to updated the table with the calculated field in the form?

Thanks
TY

 

[Rick Brandt]

Hi,

I created a form from a source table. In that form I made a simple
computation wich will add Amt1 + Amt2 and it equals to Total

Control source: =Amt1+Amt2
Name: Total

Ex:
Table: Form:
Amt1 Amt2 Total Amt1: 100
100 200 0 Amt2: 200
Total:
300


Everything seems okay but when I check my table, the Total column is
not updating. Actually, I am not really sure if it supposed to be
updated with the Total amount in my form coz all sample database that
I referred to, they don't have a column on there table for there
calculation in form. Is it possible to updated the table with the
calculated field in the form?

Thanks
TY

Calculations do get saved and they should not be anyway. Just use your
expression wherever you need to see the total and remove that field from your
table.

 

[John W. Vinson]

Everything seems okay but when I check my table, the Total column is not
updating. Actually, I am not really sure if it supposed to be updated with
the Total amount in my form coz all sample database that I referred to, they
don't have a column on there table for there calculation in form. Is it
possible to updated the table with the calculated field in the form?

Yes... but you shouldn't 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.

John W. Vinson [MVP]

 

[Guest]

Thank you, but the reason I asked this is to solve my problem in the database
I'm developing. I will need the total column in my table updated is for my
other event. It supposed to be like this:

Formula: OpeningB+Payment-Collect= EndB

Table:
Branch Date OpeningB Payment Collect EndB
BTF 6/9/2007 1000 100 200 1100
BTF 6/10/2007 1100

The Opening balance field should be automated in the form, Payment & Collect
are data entered manually, EndB formula.

The EndB will be OpeningB on the next day. How can I do this In a form?I was
thinking that if the Total which is my EndB in table updated I could create a
query that will make the EndB as OpeningB. If you have other way to do this
please let me know and It's very much appreciated.

 

[Rick Brandt]

Thank you, but the reason I asked this is to solve my problem in the
database I'm developing. I will need the total column in my table
updated is for my other event. It supposed to be like this:

Formula: OpeningB+Payment-Collect= EndB

Table:
Branch Date OpeningB Payment Collect EndB
BTF 6/9/2007 1000 100 200 1100
BTF 6/10/2007 1100

The Opening balance field should be automated in the form, Payment &
Collect are data entered manually, EndB formula.

The EndB will be OpeningB on the next day. How can I do this In a
form?I was thinking that if the Total which is my EndB in table
updated I could create a query that will make the EndB as OpeningB.
If you have other way to do this please let me know and It's very
much appreciated.

You are attempting to build a spreadhseet. Something that Excel is good at but
that Access (and all other databases) are not good at.

Expressions across columns can be done in queries (not tables) , but running
balances are not easily done because they depend on records being processed in a
particular order and database tables have no inherant order. Order is always
imposed by queries, reports, and forms. A report has the most capability to do
that part because reports ARE processed sequentially rather than as a set
operation as queries are. A correlated subquery could also do it, but those are
somewhat complicated to build and slow to execute.

 

[Guest]

In order for me to solve a little bit of the problem I created a subform that
will show up the Opening Balance on the next day but the user's still need to
manually enter or maybe copy the amount from that subform and paste it in the
Opening Balance line of the main form but that is not reliable solution coz
there will be a possibility that the user might change other amt in record
and not updating the Opening Balance line that will screw my computation for
closing Balance. Now, I need a code or solution that will take the Opening
Balance of subform and automatically will apply in the Opening Balance of the
Main form. Hope you got what I mean. Thank you.

 

[Rick Brandt]

In order for me to solve a little bit of the problem I created a
subform that will show up the Opening Balance on the next day but the
user's still need to manually enter or maybe copy the amount from
that subform and paste it in the Opening Balance line of the main
form but that is not reliable solution coz there will be a
possibility that the user might change other amt in record and not
updating the Opening Balance line that will screw my computation for
closing Balance.

You've just explained the exact reason why calculaton results should be
calculated on the fly and not stored. If another process changes one of your
operand values without re-doing the calculation, your database is now WRONG with
(often) no easy way to discover that fact and/or fix it.