Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What kind of event to use ? (Access 2007)

    On my form "frmOrders" I have a commandbutton to make it possible to delete orderitems from the "subfrmOrders", as well as delete the order itself if all the related orderitems are deleted.
    When on the moment that a orderitem is entered, the stock of the ordered article isn' t sufficient for a complete delivery, a special record is intered in a special table to keep track of subsequent deliveries.
    If such an order is booked, and after that, for some reason the orderitem has to be canceled and thus deleted, not only the code for deleting this orderitem has to be executed, but after this deleting a lookup for a related record in the special table for subsequent orders is to be fullfilled, in order to delete this record also. I placed the code for looking up and deleting this record in the "Afterupdate " event of my "subfrmOrders", but it seems that this event dont take place after deleting the orderitem. So my question is: In what kind of event should i best place the code for deleting subsequent deliveries after deleting the orderitem? Thanks for the help!!!

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: What kind of event to use ? (Access 2007)

    The easiest way to solve this is to create a relationship between the order items table and the special table and to specify "Enforce Referential Integrity" and "Cascade Delete Related Records" for this relationship. When you delete an order item, related records in the special table will automatically be deleted too, without requiring code.

    See the screenshot below (it's for the relationship between an orders and an order details table, but the same principle applies to other relationships).
    Attached Images Attached Images
    • File Type: png x.png (10.2 KB, 1 views)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What kind of event to use ? (Access 2007)

    Thank you very much for the help, however ...
    As far as the problem is limited to deleting related records, i believe your solution is exactly what i need. But i didn't told you the whole story.
    I my program i also try to solve the following problem.
    Suppose that the orderitem that has to be deleted has to do with an article for which between the time the orderitem was entered and the moment that it has to be deleted, other orderitems concerning the same article where entered. Suppose futheron, that for some of this orderitems, there where subsequent orders, for reason of unsufficient stock of the article. As I explained in my first question for help, also for this orderitems there exits related records in the special table. By deleting the canceled orderitem, the stock of the article in question becomes again more than 0, and therefore can be assigned to one ore more records in the special table that refer to the same article. My goal is , as soon as the canceled orderitem is deleted, a lookup for other records in the special table is executed, in order to find one ore more that are related to the same article. When my program find such a records, it goes to the first one of them, check the amount that the record shows that has to be delivered, and if this amount is smaller or equal the new stock of the article, the record is deleted from this table and the related orderitem in the table tblOrderitems is also updated. This sytem goes on as long as there is sufficient stock of the article. The only problem i have I do not know in what kind of event i have to put this code.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: What kind of event to use ? (Access 2007)

    A form has three events related to deleting records:

    On Delete
    Before Del Confirm
    After Del Confirm

    When the user deletes a record, this record is placed in a temporary buffer. Then the On Delete event occurs.
    If you have set the option to confirm deletions, the Before Del Confirm occurs, then the confirmation dialog is displayed, and finally the After Del Confirm event occurs.

    See the Access help for more information, or its MSDN equivalents: Delete event, BeforeDelConfirm event and AfterDelConfirm event.

  6. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What kind of event to use ? (Access 2007)

    Thank You for your advice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •