Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Lakeland, Florida
    Posts
    22

    Unanswered: Knowing what is added/deleted in subform?

    I have an order entry app that will also keep track of inventory stock quantities.

    If a customer adds or deletes products in a typical order entry subform, how do I know what they added or deleted so I can update the inventory?

    Is there a way to read each line of the subform so I can tell what is going on during an update or delete?

    What is the best way to accomplish this?

    Seems this would be easily accomplished using a trigger in SQL Server, but how is this done in an Access subform?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    I have an order entry app that will also keep track of inventory stock quantities.

    If a customer adds or deletes products in a typical order entry subform, how do I know what they added or deleted so I can update the inventory?

    Is there a way to read each line of the subform so I can tell what is going on during an update or delete?

    What is the best way to accomplish this?

    Seems this would be easily accomplished using a trigger in SQL Server, but how is this done in an Access subform?
    On any edit of the subform have a hidden column (and textbox) that you set a flag in.

  3. #3
    Join Date
    Mar 2002
    Posts
    32
    I think it would be best to do this at the table level with relationships between the table the subform updates and the inventory table.

    Then Use Cascade Update and Cascade Delete checkmarks on those relationships.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    I have an order entry app that will also keep track of inventory stock quantities.

    If a customer adds or deletes products in a typical order entry subform, how do I know what they added or deleted so I can update the inventory?

    Is there a way to read each line of the subform so I can tell what is going on during an update or delete?

    What is the best way to accomplish this?

    Seems this would be easily accomplished using a trigger in SQL Server, but how is this done in an Access subform?
    Have you worked much with VBA?

    You can run a procedure or function when things change(delete, update etc.) that opens a recordset and updates the stock quantities.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Feb 2004
    Location
    Lakeland, Florida
    Posts
    22

    Re: Knowing what is added/deleted in subform?

    Originally posted by basicmek
    Have you worked much with VBA?

    You can run a procedure or function when things change(delete, update etc.) that opens a recordset and updates the stock quantities.
    I believe the proper way to do this is not update the inventory after each product order, but on a schedule.

    I've been using VB since 1992, and VBA off an on. It's tricky to know programmatically exactly what records are being added and deleted within a subform. I'm still not quite sure how to do this accurately. I suppose you could take a before and after snapshot of the records.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    I believe the proper way to do this is not update the inventory after each product order, but on a schedule.

    I've been using VB since 1992, and VBA off an on. It's tricky to know programmatically exactly what records are being added and deleted within a subform. I'm still not quite sure how to do this accurately. I suppose you could take a before and after snapshot of the records.
    That depends on how you set the subform up ... You can tell what has been updated easily ... You can tell what has been added easily ... And for that matter you can tell what has been deleted easily ... How much VBA code work are you willing to do? None of these can be done with a continuous form without doing some coding (and in the case of the deletions some additional filtering ...)

  7. #7
    Join Date
    Feb 2004
    Location
    Lakeland, Florida
    Posts
    22

    Re: Knowing what is added/deleted in subform?

    Originally posted by M Owen
    That depends on how you set the subform up ... You can tell what has been updated easily ... You can tell what has been added easily ... And for that matter you can tell what has been deleted easily ... How much VBA code work are you willing to do? None of these can be done with a continuous form without doing some coding (and in the case of the deletions some additional filtering ...)
    I'm an excellent programmer, been programming for over 22 years, so please give me some insight to what has been added/deleted.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    I'm an excellent programmer, been programming for over 22 years, so please give me some insight to what has been added/deleted.
    Mind you all I currently do is to flag when a change has been performed ... I have Yes/No columns in the underlying table that get set when an edit is performed ( trap on the controls After_Update event) that get written as part of overall update of the record. Deletion CAN be handled in the same manner ... Have a button to delete records which will merely TURN ON/OFF a Visible flag of that record such that upon requery WHERE IsVisible=True the record has been "deleted" ... Simple?

    I will grant a Caveat: You can't really do this on a "live" table ... This works best for a snapshot where you can (after all changes are made) process all the Inserts/Updates/Deletes on the active data ...

  9. #9
    Join Date
    Feb 2004
    Location
    Lakeland, Florida
    Posts
    22

    Re: Knowing what is added/deleted in subform?

    Originally posted by M Owen
    Mind you all I currently do is to flag when a change has been performed ... I have Yes/No columns in the underlying table that get set when an edit is performed ( trap on the controls After_Update event) that get written as part of overall update of the record. Deletion CAN be handled in the same manner ... Have a button to delete records which will merely TURN ON/OFF a Visible flag of that record such that upon requery WHERE IsVisible=True the record has been "deleted" ... Simple?

    I will grant a Caveat: You can't really do this on a "live" table ... This works best for a snapshot where you can (after all changes are made) process all the Inserts/Updates/Deletes on the active data ...
    Sounds simple enough. Now, you mention having the user press a button for the delete which allows you to know exactly what gets deleted.

    But, if the user selects several rows to delete in a subform and presses the Delete key, would you trap the Form_Delete event within a subform to determine the subform records deleted?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    Sounds simple enough. Now, you mention having the user press a button for the delete which allows you to know exactly what gets deleted.

    But, if the user selects several rows to delete in a subform and presses the Delete key, would you trap the Form_Delete event within a subform to determine the subform records deleted?
    Something to note: Continuous Forms do not allow multiple row selections... But even still: If you have the capability to retrieve the records selected say from the ItemsSelected property (listbox) then you can still accomplish it ... Best thing in that case is to have a hidden autonumber column to reference the row selections to ... And then do the same thing.

  11. #11
    Join Date
    Feb 2004
    Location
    Lakeland, Florida
    Posts
    22

    Re: Knowing what is added/deleted in subform?

    Originally posted by M Owen
    Something to note: Continuous Forms do not allow multiple row selections... But even still: If you have the capability to retrieve the records selected say from the ItemsSelected property (listbox) then you can still accomplish it ... Best thing in that case is to have a hidden autonumber column to reference the row selections to ... And then do the same thing.
    OK, thanks for you help!

    I'm an out-of-work programmer looking forward to getting back into the scene! Can't seem to get any bites!

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Knowing what is added/deleted in subform?

    Originally posted by nmccamy
    OK, thanks for you help!

    I'm an out-of-work programmer looking forward to getting back into the scene! Can't seem to get any bites!
    Good luck with that! I just played with a form that's CF switching it to datasheet view and it didn't work like I thought ... Definitely, if you have Continuous Forms as the form view then only 1 row can be selected at a time ... Makes deletion a whole lot easier ...

Posting Permissions

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