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

[Eva Lyshoel]

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

 

[Nikos Yannacopoulos]

Eva,

I have developed an Order Entry system for my company's Customer Service
Dept. as well, and at some point, much like you, I found myself thinking
about the events part. I was worried about users tabbing through the
controls vs. using the mouse to jump through, using the scroll weel etc. so
finally I decided to use a form with unbound controls, so the only choice
they had to fire an event was to click on a Save command button. I put a
number of control groups (for item code, description and quantity) that
exceeds the maximum number of item lines I would ever have on a single order
(that's fourty in my case), arranged them so they look like a continuous
form, and used a little VB code to hide the unused "rows". Then I used some
VB code again behind the Save button to read the populated "rows" (in a
loop), and save them to the target table, opened as a recordset. I handle
the changes in much the same way (delete all line items for the given order
and then create the records again - the secong part uses the same sub as
before). If you decide this approach suits your needs, it would be very easy
to add the extra code to populate the transactions table, handling it in
much the same way.
The question that comes to mind first, though, is, do you really need the
Transactions table, if all the information is already in the OrderDetails
table? You could calculate your stock reading the latter instead of the
former, or you could take an altogether different, simpler and faster
approach (actually this is the one employed by major ERPs for the purpose):
add one more field in your products table to store stock at hand, and
subtract from / add to that upon a transaction.

HTH,
Nikos