Simple Access Audit Trail In VBA


Do you need to track who is making changes to your company records?

Sometimes you want to go back to the person who changed a record, and find out why they did it? They had some reasoning behind why they made such a change, but you don’t know.

Well, in this post I am going to show you how you can make a simple Access audit trail in VBA. It’s going to help you figure out who made a record change and when.

Here’s the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("This record was modified, would you like to continue?", vbYesNo)
        Case vbYes
            Me.DateModified = Now()
            Me.UpdatedBy = Environ("UserName")
        Case vbNo
            Cancel = True
    End Select
End Sub

This will automatically enter a current timestamp and who made the change (on whose computer was the change made.)

So this is just a simple form. First add a couple fields to your table and then add a couple fields to your form, and then add the prior code to your form’s BeforeUpdate event.

Why? Because that event fires before the record is committed to the database, and you can back out of it (using the built in “Cancel” argument).

So here I am adding (changing) the contact name.

Here, I get a message box asking if I really want to make the change.

After telling the form that I really what to make the change, it will track who made and change and when.

Any questions, let me know?

Offsite Related Information:

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , ,