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

[EKL]

Hi,

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 are:

"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"?

Thanks for all help....