If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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"? |
#2
|
|||
|
|||
HOW TO UPDATE A TABLE BASED ON CHANGES IN ANOTHER TABLE?
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 |
Thread Tools | |
Display Modes | |
|
|