Search code examples
databaseformsloggingtrackingms-access-2016

MS Access: Audit a table with a full log table to track table changes

I found some posts on this site that somehow relate to may issue, but non of them helped me. so this is NOT a duplicate.

The Problem:

This seemed to me a very easy thing to do at the beginning, but it got complicated and very challenging. I am pretty new to MS Access, so -after struggling for many days- I hope to find the solution here.

Say we have a simple table like this:

enter image description here

Now, I need to track changes on this table on the record level and Log the changes in another table (Say Log table) like this:

enter image description here

Where track_type:

  1. for add(insert)
  2. for edit(update)
  3. for delete

The changes(add/edit/delete) should be logged when the record on the Student Form is changed.

My Problem is that I am not able to generate the changes correctly.

My form has to refresh a lot which make excessive entries in the log table like this one:

enter image description here

as you can see, instead of one log for the insert, it takes two logs to insert a record.

I searched the net thoroughly, but I cannot find this kind of logging. I found information about logging on the field level, but this is not what I want. I know it may be better in most cases, but not for my case.

So, I will appreciate any guiding information/code.

a minimized database that focuses on the problem is posted Here and you can download it and see what I have tried and where I am stuck.

Notes:

  1. Changes should be logged only when the user: - Move from the current record on the form. - Exit the form
  2. Changes Should not happen while on the same record even if these changes committed to the Student table.
  3. Refresh is unavoidable.
  4. Audit Trail is not helpful if on the field level.
  5. Data Macro is not helpful as changes can be committed more than once.
  6. Before/After Insert or update event is problematic due to the Refresh method.
  7. It is highly appreciated to exhibit your solution using the attached database.
  8. Custom Navigation button are not an option.
Solution

  • After many tries, I came up with this solution:

    Create a custom user data type that has the same structure as the record to be tracked.

    Something like:

    Private Type Student_Record
   s-id
   s_Name
   s_age
End Type

Dim SR as Student_Record

    Then do all your validations on this SR record instead of the actual record.

    If things OK, you just go.

    If something is wrong you go back to the record and ask user to correct it.

    Requires more coding, but it works.

    Thanks for the user custom types.