How to create Audit Trail on a Form ( MS-Access 2007)

**jforbes** · Jan 22 '15, 01:58 PM

That is hilarious. I’ve been in your position and it’s not fun.

There are multiple ways of going about this depending on the level of reportability you are looking for.
If you are using SQL Server as your backend, you can create a trigger to take care of this with a large amount of flexibility. If you have SQL SEVER and want an example, let us know.
If you are using Access only, the easiest and most basic way that I’ve found is to create a table to store logging information. Then populate it on the Form_BeforeUpda te (or AfterUpdate, up to you) Event from any Form that contains Tables you want to monitor.

I mocked up some of this with some code I had lying around. Here is the basic table structure. Some people get really bent out of shape with reserved words, so you might want to rename Table to something not reserved.

Code:

Name: LastUpdate
Field Name	Data Type
----------      ---------
LastUpdateID	AutoNumber
LastUpdate	Date/Time
LastUpdateBy	Short Text
Table		Short Text
TableID		Short Text

There are multiple versions of the following code all over the internet that gets the currently logged in Windows User. If you don't have one already paste this into a code Module. (The top line needs to be outside of any methods):

Code:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetWindowsComputerName() As String
    Dim strComputerName As String
    Dim lngBuffSize As Long
    
    strComputerName = Space$(255)
    lngBuffSize = Len(strComputerName)
    If GetComputerName(strComputerName, lngBuffSize) Then
        strComputerName = Left$(strComputerName, lngBuffSize)
    End If
    
    GetWindowsComputerName = strComputerName
End Function

Also in that module, past the following function. This is the code that will insert the Logging record:

Code:

Public Function trackLastUpdate(ByRef sTable As String, ByRef sTableID As String) As Boolean
    Dim sSQL As String
    
    trackLastUpdate = False
    
    sSQL = sSQL & "INSERT INTO LastUpdate ("
    sSQL = sSQL & "  [Table] "
    sSQL = sSQL & ", [TableID]"
    sSQL = sSQL & ", [LastUpdate]"
    sSQL = sSQL & ", [LastUpdateBy]"
    sSQL = sSQL & ") VALUES ("
    sSQL = sSQL & " '" & sTable & "'"
    sSQL = sSQL & ", '" & sTableID & "'"
    sSQL = sSQL & ", #" & Now() & "#"
    sSQL = sSQL & ", '" & GetWindowsUser() & "'"
    sSQL = sSQL & ")"
    
    CurrentDb.Execute sSQL

    trackLastUpdate = True
End Function

Now from any Form that you would like to implement logging, edit the BeforeUpdate to something like this:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Cancel = (Not trackLastUpdate(Me.RecordSource, Me!ID))
End Sub

Or...

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Call trackLastUpdate(Me.RecordSource, Me!ID)
End Sub

You may have to tweak this a bit to get it to match your fields. Also you can hard code the "Me.RecordSourc e" to the table name, especially if your recordsource is a query of sorts. Also, also you don't have to Cancel the update, it's just an option.

There are additional things you can do, like including what fields were changed. You can find out what is changed by inspecting the OldValue Property of a Control.

Lastly, you can see what has happened easier by creating Queries to link the LastUpdate Table back to logged Table. There are a lot of options, but here is a basic example:

Code:

SELECT Test.Test, Test.Category, Test.[Select], LastUpdate.LastUpdate, LastUpdate.LastUpdateBy
FROM LastUpdate INNER JOIN Test ON val(LastUpdate.TableID) = Test.ID
WHERE [Table]='Test'

How to create Audit Trail on a Form ( MS-Access 2007)

How to create Audit Trail on a Form ( MS-Access 2007)

Comment