Caculating between 2 different tables

K

Kevin

Can someone help me.

I have 2 tables "Table1" and "Table2", and each Table has a field "Field1"
and "Field2", and i want to subtract "Field2" from "Field1" upon exiting

I use a form that uses the "Table1" data and on exiting the table i want to
subtract the 2 fields.

What i need is some code that i can connect to the "Next Record" button upon
exit.

I am sure i have set it up wrong, but i am sort of stuck with it now.
 
J

John W. Vinson

Can someone help me.

I have 2 tables "Table1" and "Table2", and each Table has a field "Field1"
and "Field2", and i want to subtract "Field2" from "Field1" upon exiting

I use a form that uses the "Table1" data and on exiting the table i want to
subtract the 2 fields.

What i need is some code that i can connect to the "Next Record" button upon
exit.

I am sure i have set it up wrong, but i am sort of stuck with it now.
Click to expand...

Well... it IS set up wrong. Tables are not spreadsheets, and storing
calculated values in a table is generally very unwise.

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.

There is no code that I know of which will run when you *JUST* go to
the "next record". The Form's BeforeUpdate event will run if you have
made any changes to the record, however, so that may be the best
place.

If you could describe the real-world data that these tables represent,
perhaps someone could suggest a better structure.

John W. Vinson [MVP]
 
Top