HOW TO UPDATE A TABLE BASED ON CHANGES IN ANOTHER TABLE?

I'm making a sort of Customer and Orders database in MS
Access 2003. My problem is that I wish to update the
table "tblTransaction" based on changes made in the
table "tblOrderDetails". There is no direct relationship
between these two tables. The "tblProducts" has a one-to-
many relationship to "tblOrderDetails"
and "tblTransaction". I use the "tblTransaction" to count
number of items in stock for each product. I therefore
wish to update this table when "tblOrderDetails" changes,
so that my "items in stock" is dynamically updated.

I have found out that Access don't have record triggers
at the table level like Oracle does. But I assume that my
problem can be solved with some Visual Basic Event
Procedures in the form I use to input my Orders and
OrdersDetails.

The form I use for this is called "frmPrivateUser" and
contains two sub forms, "frmOrdersSubform"
and "frmOrderDetailsSubform". "frmOrdersSubform" contains
information about the order and "frmOrderDetailsSubform"
contains this orders products. "frmOrderDetailsSubform"
is linked to "frmOrdersSubform" on the field "OrderID"(an
auto number).

When I add a record in the form I want to take some of
the values from the forms and place them
in "tblTransaction". I also need to place these values
differently based on some values
in "frmOrdersSubform"(Order type). I also want to check
the ProductID against the "tblProducts" to see if a
product is a physical unit and not a service(Attribute
Lagervare=yes in tblProducts) . If the product is not a
physical unit there is no need to add transactions.
Depending on the type of order I want to take the
following values from my forms and put it
in "tblTransaction:

The controls on my form a
"frmOrdersSubform": Ordernr and OrderType
"frmOrderDetailsSubform": ProductID and
NumberOfUnits

If OrderType="Innhenting" in frmOrdersSubform then set:
tblTransaction
TransactionID (autonumber)
Transactiondate =Date()
Transactiontype ="Oppdrag"
UnitsIn =value of control "NumberOfUnit"s
in "frmOrderDetailsSubform"
UnitsOut ="0"
Ordernr = value of control "Ordernr"
in "frmOrdersSubform"
ProductID = value of control "ProductID"
in "frmOrderDetailsSubform"

Else set:
tblTransaction
TransactionID (autonumber)
Transactiondate =Date()
Transactiontype ="Oppdrag"
UnitsIn ="0"
UnitsOut = value of
control "NumberOfUnit"s in "frmOrderDetailsSubform"
Ordernr = value of control "Ordernr"
in "frmOrdersSubform"
ProductID = value of control " ProductID"s
in "frmOrderDetailsSubform"

When I delete a record from "frmOrderDetailsSubform" any
records in "tblTransaction" with the same Ordernr AND
ProductID should be deleted (if they exist).
When I update a record from "frmOrderDetailsSubform" any
records in "tblTransaction" with the same Ordernr and
ProductID should be deleted (if they exist), and the
updated values should add a new record in "tblTransaction"

Any code suggestions? What Events do I need to use?
Should these Events be placed in
the "frmOrderDetailsSubform"?

You can use triggers if it's not too late to make your db a project and use the MSDE, Acc 2000? and later. Then download the "SQL Server books online" and search for the create trigger examples. You can use Oracle skills with it. Much the better way.

Otherwise, if you use jet/vb put the code in the AfterUpdate event of the control where the user changes it, that gives them a chance to cancel and you a chance to validate/reject. Make it a function to abstract it away from the UI. Make the function tough and handle your errors. Don't overindex the transactions table. I do the transaction log thing quite a bit and have a lot still stuck in Jet. Good luck. Peter