Search code examples
ms-accessvba

MS Access "record changes" log

Whenever a record is deleted or updated on a form, I want to save its old values in a history table (let's call it Revised). I guess I have to do the following:

For record changes:

  • use the BeforeUpdate event to save the data somewhere (collection ? array ? or to a recordset -the Revised table- without saving-yet ?)
  • use the AfterUpdate event to add/save that data to the Revised table

For Deletions:

  • use the OnDelete event to save the data - but again how ? several records could be deleted at once since the form (a subform in fact) is in datasheet view
  • use the AfterDelConfirm to add that data to the Revised table.

Do you have any clues, comments or links for this ?
This is all in a "pure Access" (no SQL Server) at the moment. Many thanks !

Edit: as usual, properly asking the question gaves me ideas:

  • option 1
    use the BeforeUpdate or the OnDelete to build the SQL statement, and use the AfterUpdate or the AfterDelConfirm to Execute the SQL statement. But that won't work for multiple deletions ?

  • option 2
    have the Revised recordset defined at form level, insert the record "Before" but only Update "After". Again, problem with multiple deletes.

Solution

  • I've successfully used a variation of Allen Browne's approach in a couple of different projects. Check out his website for more details:

    Creating an Audit Log

    His solution uses temp tables and four generic function calls to handle the issue with multiple deletes.