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?
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 "Eva Lyshoel" wrote in message ... 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"? |
Thread Tools | |
Display Modes | |
|
|