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

[satifali]

I have a database shared with 5 users & want track who is changing what as they keep blaming each other for the changes. Please help.

[jforbes]

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_BeforeUpdate (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.

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):

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:

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:

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

Or...

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.RecordSource" 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:

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'