Audit Trail

summary

here is the summary of it:

1st: use the following code to create a new module, call it (dAuditTrail):

Code: 
Option Compare Database
Option Explicit

Public Function Audit_Trail()
On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
    
    Dim MyForm As Form
    Dim ctl As Control
    Dim sUser As String
    Set MyForm = Screen.ActiveForm
'    sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
'    sUser = Environ("UserName") 'get the users login name
    sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been modified.
    MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
    
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
            'If old value is Null and new value is not Null
            ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
            'If new value is Null and old value is not Null
            ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
            End If
    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If Err.Number = 64535 Then 'Operation is not supported for this type of object.
        Exit Function
    ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
        Beep
        MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function
2nd: in your table, create a new memo field, call it (AuditTrail).

3rd: in your form add the new memo field, call it (tbAuditTrail) and make it 'Locked'.

4th: in the BeforeUpdate event of the form, write the following code:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err
    
    Call Audit_Trail
    
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit
    
End Sub
i hope i didn't miss any part ^^"..

BUT, if you have a form with a subForm, do all the above and the following changes:
http://www.access-programmers.co.uk/forums/showpost.php?p=173439&postcount=15
but instead of 'frm', write 'MyForm' and it should work.

and if the parent form is based on more than one table, do the following changes:
http://www.access-programmers.co.uk/forums/showpost.php?p=713245&postcount=171

I attached the sample provided by ghudson with the required changes in the module, and added a form with subForm with the Audit Trail, I hope it helps ^^..

and yes, THANKS everyone for this nice discussion and the ones who helped for your help.
 

Attachments

Last edited:
Many thanks for your detailed reply. I will go through this and see how it works. Will get back to you, if there are any issues.
 
Uvuriel03 said:
How do you implement this?
Click to expand...

paste that code (the whole thing) into a new standard module, name the module something like modUsers and then whenever you need it you call it like:

Code: 
If fOSUserName = "Whatever" Then ...


or

Code: 
Me.YourTextBox = fOSUserName
 
I'm still not sure how you could integrate this into the audit trail for the auditor's name.

Sorry, I just got massively busy and I don't have time to dig into it myself. I'm hoping you might have just a quick explanation!! :(
 
Hello im using the Audit trail and is working great although as i am using a drop down combo box for employees I want to show the names in the audit trail and not the employee ID's as shown below

New Record added on 18/07/2008 12:15:10 by Admin;
Changes made on 18/07/2008 12:16:18 by Admin;
EmployeeID: Changed From: 1, To: 2

1 = Dave
2 = Nick

I want to show it as:

New Record added on 18/07/2008 12:15:10 by Admin;
Changes made on 18/07/2008 12:16:18 by Admin;
EmployeeID: Changed From: Dave, To: Nick
 
Hi All

Just wanted to post this ref. audit trails. I have been banging my head on this one, though as always the simplest solution seems to prevail!!

I kept getting the runtime error 3251, after much deliberation I have found the reason, two of my forms are built on tables and one on a query. Yes, you guessed it the table forms run the audit trail fine and the query one gave me the error. I don't understand the logic (should be able to write to the query!) though at least I now know what the Access problem is.

Just though it may help anyone in the future.
 
Hi all,

There are basically two samples in this thread.

  1. An audit trail that requires the "Audit Trail Field" in each table.
  2. An audit trail that requires a table and where all changes are stored in a table.
I really like the idea of the separate table and is how I am currently set up for this. However, as the database grows, I am envisioning this table getting quite large. Granted I don't want users editing records all the time, but it's still going to happen. Given that I am thinking the Audit trail table may get quite large, I now am going back and forth as to which method I may prefer.

So my questions are:

  1. Which method do you prefer and why?
  2. For either method, have you experienced a large table or large memo fields that might be semi-difficult to search?
Thanks,
Craig
 
Just to throw out a alternative- I use MySQL backend, among other reasons, to satisfy the audit trail requirement, which is easier as I can just use binary logs and triggers to timestamp & userstamp everything. Surely one can do same thing with SQL Server if this is available (I don't know whether SQL Server Express, which is free, supports the functionalities), and of course there's PostgreSQL which among free RDBMS has slightly more documentation for working with Access (but that's just one giant FAQ. Not that lot.)

Going back to pure Access solution, I'd use table because it's easier and keeps table normalized. As for size, I'd just truncate the table periodically. How much and how long ago, depends on your individual needs. Logs are good up to a point, but when you're keeping logs of what happened 100 years ago and books has been long since closed, it's kind of... pointless.

As for memo fields, one option is to truncate the memo to 255 character and store it in a separate field. Not normalized, but provides you an option for faster search. Of course, this may mean that auditing memos would require its own table for better performance.

HTH.
 
In keeping with the spirit of this thread, here's my code that uses the separate table method. I'm not so sure I am looking for the existing table in the preferred manner, but this works quite well. Let me know what you think.

Code: 
Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim sAuditTable As String
Dim sSQL As String

Dim sTable As String        'Table where the record is being edited
Dim CTL As Control          'The control in the form being edited
Dim sFrom As String         'Original Data in the control
Dim sTo As String           'What the original data was changed to

Dim sPCName As String       'Name of the PC that is being used
Dim sPCUser As String       'Name of the User on the Networked PC
Dim sDBUser As String       'Name of the Database User
Dim sDateTime As String     'Date and Time of the change

'===========================================================================================
'
'   This Audit Trail will track changes to existing records.
'   In the "Before Update" event of the FORM enter the following:
'
'       Call AuditTrail(Me.Form, [RecordID])
'
'   Make sure to create a table called "tbl_AuditLog" and have the following fields:
'   (A table will be created automatically if it does not exist)
'
'       1. RecordID (This is a unique number)
'       2. txt_Table (This is the table where the record was changed)
'       3. lng_TblRecord (This is the RecordID number from the record being changed)
'       4. txt_Form (This is the form being used to edit the record)
'       5. txt_Control (This is the data entry control (field) that was edited
'       6. mem_From (This is the original data in the control (field)
'       7. mem_To (This is what the original data was changed to)
'       8. txt_PCName (This is the name of the PC used to edit the record)
'       9. txt_PCUser (This is the name of the user logged onto the PC)
'       10. txt_DBUser (This is the name of the person looged on to the databse if used)
'       11. dat_DateTime (This is the date and time the record was edited.)
'
'
'   The inspiration behind this code is from:
'       1. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
'       2. http://www.access-programmers.co.uk/forums/showthread.php?t=44231
'
'
'============================================================================================
    


Public Function AuditTrail(frm As Form, lngRecord As Long)
On Error GoTo Error_Handler
    
    '----------------------------------------------------------------------
    '   Skips this procedure if a new record is being entered in the form
    '----------------------------------------------------------------------
    If frm.NewRecord = True Then
        Exit Function
    End If
        
    '----------------------------------------------------------------------
    '   Checks to see if the tbl_AuditLog Exists
    '   Creates the table if it does not exist
    '----------------------------------------------------------------------
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
    
    sAuditTable = "tbl_AuditLog"
    On Error Resume Next
    If IsNull(dbs.TableDefs(sAuditTable)) Then
        'Table does not exist
        On Error GoTo Error_Handler
        sSQL = "CREATE TABLE tbl_AuditLog([RecordID] COUNTER PRIMARY KEY, [txt_Table] TEXT(50), [lng_TblRecord] LONG, " & _
                "[txt_Form] TEXT(50), [txt_Control] TEXT(50), [mem_From] MEMO, [mem_To] MEMO, [txt_PCName] TEXT(50), " & _
                "[txt_PCUser] Text(50), [txt_DBUser] Text(50), [dat_DateTime] DATETIME);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
    Else
        'Table Exists. Do Nothing
        On Error GoTo Error_Handler
        
    End If

    Set dbs = Nothing
       
     
       
    '----------------------------------------------------------------------
    '   Runs through each control on the form and checks for edits/changes
    '----------------------------------------------------------------------
    For Each CTL In frm
    
        Select Case CTL.ControlType     'Only checks data entry type controls.
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                
                sFrom = Nz(CTL.OldValue, "Null")
                sTo = Nz(CTL.Value, "Null")
                
                If sFrom <> sTo Then
                
                    '-----------------------------------
                    '   Gets the required Info
                    '-----------------------------------
                    sTable = frm.RecordSource
                    sPCName = Environ("COMPUTERNAME")
                    sPCUser = Environ("Username")
                    sDBUser = "Me"      'Get Username from the database login
                    sDateTime = Now()

                    sSQL = "INSERT INTO tbl_AuditLog ([txt_Table], [lng_TblRecord], [txt_Form], [txt_Control], " & _
                           "[mem_From], [mem_To], [txt_PCName], [txt_PCUser], [txt_DBUser], [dat_DateTime]) " & _
                           "VALUES ('" & sTable & "', '" & lngRecord & "', '" & frm.Name & "', " & _
                           "'" & CTL.Name & "', '" & sFrom & "', '" & sTo & "', '" & sPCName & "', " & _
                           "'" & sPCUser & "', '" & sDBUser & "', '" & sDateTime & "')"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL sSQL
                    DoCmd.SetWarnings True
    
                End If
        End Select
    Next CTL
    
Error_Handler_Exit:
   Exit Function

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

End Function
 
Last edited:
Hi,

I'd like to combine the audit trail code with the following which also uses the BeforeUpdate preceedure. Anyone done this/know how to do it?

Private Sub Form_BeforeUpdate(Cancel As Integer)
' This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue with the save operation or to cancel it. Then
' the action that triggered the BeforeUpdate event is completed.
Dim ctl As Control
On Error GoTo Err_BeforeUpdate
' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If
Exit_BeforeUpdate:
Exit Sub
Call Audit_Trail

Form_BeforeUpdate_Exit:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub
 
Last edited:
Put the audit trail code in the vbYes operation.

Something like:

Do you want to save?
If yes then
Audit trail here
If no then
Undo

It can be added to the audit trail function.

Just my opinion, but why would you want the user to be able to choose whether or not the audit trail is recorded or not? Wouldn't that defeat the purpose of it?

Just some food for thought.
Craig




eddie_j_derbys said:
Hi,

I'd like to combine the audit trail code with the following which also uses the BeforeUpdate preceedure. Anyone done this/know how to do it?
Click to expand...
 
Last edited:
You're right, I dont want users to decide whether to record the audit trail or not.

I just want to incorporate the save changes prompt box with the audit trail when moving to the next record or closing the form.

Sorry im new to this, where is the vbYes operation?
 
eddie_j_derbys said:
You're right, I dont want users to decide whether to record the audit trail or not.

I just want to incorporate the save changes prompt box with the audit trail when moving to the next record or closing the form.

Sorry im new to this, where is the vbYes operation?
Click to expand...

So you'd like the user to be able to save or undo the changes, before the audit trail procedure? That make more sense. Sorry I misunderstood your goal.

I believe that would be quite easy.

If you are using my audit trail example, try something like this in the before_update event of the form (You may have to tweak it a little) and I am not sure it will undo ALL of the changes:
Code: 
    Dim sPrompt As String
    Dim sTitle As String
    Dim iResponse As Integer
    
    sPrompt = "Do you wish to save the changes made?"
    sTitle = "Database Message"
    iResponse = MsgBox(sPrompt, vbYesNo, sTitle)
    If iResponse = vbYes Then
        Call AuditTrail(Me.Form, [RecordID])
        DoCmd.RunCommand acCmdSaveRecord
    ElseIf iResponse = vbNo Then
        DoCmd.RunCommand acCmdUndo
    End If
 
Last edited:
Two questions;

1. I'm only required to audit the date of changes made to my data so I've selected 'date/time' for the data type of my audittrail column. This works fine, however it doesn’t overwrite with the latest date when subsequent changes are made. Can anyone suggest how I can get the most recent date of change to appear in my table?

2. Is it possible to run the audit trail exclusively on one field rather than the whole form?

Thanks in advance for your help.
 
Last edited:
irish634 said:
In keeping with the spirit of this thread, here's my code that uses the separate table method. I'm not so sure I am looking for the existing table in the preferred manner, but this works quite well. Let me know what you think.

Code: 
Option Compare Database
Option Explicit

Dim dbs As DAO.Database
Dim sAuditTable As String
Dim sSQL As String

Dim sTable As String        'Table where the record is being edited
Dim CTL As Control          'The control in the form being edited
Dim sFrom As String         'Original Data in the control
Dim sTo As String           'What the original data was changed to

Dim sPCName As String       'Name of the PC that is being used
Dim sPCUser As String       'Name of the User on the Networked PC
Dim sDBUser As String       'Name of the Database User
Dim sDateTime As String     'Date and Time of the change

'===========================================================================================
'
'   This Audit Trail will track changes to existing records.
'   In the "Before Update" event of the FORM enter the following:
'
'       Call AuditTrail(Me.Form, [RecordID])
'
'   Make sure to create a table called "tbl_AuditLog" and have the following fields:
'   (A table will be created automatically if it does not exist)
'
'       1. RecordID (This is a unique number)
'       2. txt_Table (This is the table where the record was changed)
'       3. lng_TblRecord (This is the RecordID number from the record being changed)
'       4. txt_Form (This is the form being used to edit the record)
'       5. txt_Control (This is the data entry control (field) that was edited
'       6. mem_From (This is the original data in the control (field)
'       7. mem_To (This is what the original data was changed to)
'       8. txt_PCName (This is the name of the PC used to edit the record)
'       9. txt_PCUser (This is the name of the user logged onto the PC)
'       10. txt_DBUser (This is the name of the person looged on to the databse if used)
'       11. dat_DateTime (This is the date and time the record was edited.)
'
'
'   The inspiration behind this code is from:
'       1. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
'       2. http://www.access-programmers.co.uk/forums/showthread.php?t=44231
'
'
'============================================================================================
    


Public Function AuditTrail(frm As Form, lngRecord As Long)
On Error GoTo Error_Handler
    
    '----------------------------------------------------------------------
    '   Skips this procedure if a new record is being entered in the form
    '----------------------------------------------------------------------
    If frm.NewRecord = True Then
        Exit Function
    End If
        
    '----------------------------------------------------------------------
    '   Checks to see if the tbl_AuditLog Exists
    '   Creates the table if it does not exist
    '----------------------------------------------------------------------
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
    
    sAuditTable = "tbl_AuditLog"
    On Error Resume Next
    If IsNull(dbs.TableDefs(sAuditTable)) Then
        'Table does not exist
        On Error GoTo Error_Handler
        sSQL = "CREATE TABLE tbl_AuditLog([RecordID] COUNTER PRIMARY KEY, [txt_Table] TEXT(50), [lng_TblRecord] LONG, " & _
                "[txt_Form] TEXT(50), [txt_Control] TEXT(50), [mem_From] MEMO, [mem_To] MEMO, [txt_PCName] TEXT(50), " & _
                "[txt_PCUser] Text(50), [txt_DBUser] Text(50), [dat_DateTime] DATETIME);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
        DoCmd.SetWarnings True
    Else
        'Table Exists. Do Nothing
        On Error GoTo Error_Handler
        
    End If

    Set dbs = Nothing
       
     
       
    '----------------------------------------------------------------------
    '   Runs through each control on the form and checks for edits/changes
    '----------------------------------------------------------------------
    For Each CTL In frm
    
        Select Case CTL.ControlType     'Only checks data entry type controls.
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                
                sFrom = Nz(CTL.OldValue, "Null")
                sTo = Nz(CTL.Value, "Null")
                
                If sFrom <> sTo Then
                
                    '-----------------------------------
                    '   Gets the required Info
                    '-----------------------------------
                    sTable = frm.RecordSource
                    sPCName = Environ("COMPUTERNAME")
                    sPCUser = Environ("Username")
                    sDBUser = "Me"      'Get Username from the database login
                    sDateTime = Now()

                    sSQL = "INSERT INTO tbl_AuditLog ([txt_Table], [lng_TblRecord], [txt_Form], [txt_Control], " & _
                           "[mem_From], [mem_To], [txt_PCName], [txt_PCUser], [txt_DBUser], [dat_DateTime]) " & _
                           "VALUES ('" & sTable & "', '" & lngRecord & "', '" & frm.Name & "', " & _
                           "'" & CTL.Name & "', '" & sFrom & "', '" & sTo & "', '" & sPCName & "', " & _
                           "'" & sPCUser & "', '" & sDBUser & "', '" & sDateTime & "')"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL sSQL
                    DoCmd.SetWarnings True
    
                End If
        End Select
    Next CTL
    
Error_Handler_Exit:
   Exit Function

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

End Function
Click to expand...

I like this method of doing this, keeping it in its own table. Also, these seems like it doesn't directly feed off a text box, as it is not coded to anything except the current frm.

However, when I set up the table and try to run this by changing data in a particular form it gives me the "Compile Error: Invalid Use of Property" at the "Call AuditTrail(Me.Form, [RecordID])" line.

Any ideas why?

To Clarify, under my form I have the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer) --> this is where the error occurs

Call AuditTrail(Me.Form, [RecordID])

End Sub
Click to expand...
And then under a separate Public Function known as "AuditTrail" I have the exact code from above. Let me know if I'm doing something wrong.
 
Last edited:
I use RecordID as Long Integer (Auto Number). Is yours the same? Do you have a RecordID on your form? Do you have the "Microsoft DAO 3.6 Object Library" Reference enabled?

Here is a copy of mine. Take a look and see what's different.
View attachment Audit_Trail.zip


kylephilly said:
I like this method of doing this, keeping it in its own table. Also, these seems like it doesn't directly feed off a text box, as it is not coded to anything except the current frm.

However, when I set up the table and try to run this by changing data in a particular form it gives me the "Compile Error: Invalid Use of Property" at the "Call AuditTrail(Me.Form, [RecordID])" line.

Any ideas why?

To Clarify, under my form I have the following code:

And then under a separate Public Function known as "AuditTrail" I have the exact code from above. Let me know if I'm doing something wrong.
Click to expand...
 
Last edited:
eddie_j_derbys said:
I also get the same error despite being the same to Irish634's attachment.
Click to expand...

Does the attachment I put in work for you guys? It works here.
Something has to be different. If you want attach yours and I'll look
 
hi ghudson

i have used the audit trail for access2003 and it works fine the problem i have is for all the users it is showing username as admin i am not using access security can u plz help me to solve this problm
 
You must log in or register to reply here.

Similar threads

GPGeorge
Access Pacific User Group -- Create Auditing in an Access Database with 3 Mouse Clicks
Replies
1
Views
499
GPGeorge
GPGeorge
M
I'm sure there is a simple solution asset tracking MY NEW PROJECT.
2
Replies
29
Views
675
Ken Sheridan
Ken Sheridan
J
Row Height of subform reverts back
Replies
12
Views
992
jco23
J
E
Best Practices for User Access Control in Access Databases - Emmanuel Katto Uganda
Replies
2
Views
2,106
GPGeorge
GPGeorge
B
Pop up forms not "stacking" in the right order
Replies
10
Views
1,130
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom