Creating audit trail of all edits to database

Can anyone help with creating audit trail of all edits made to a table/form, (date, time of the initial loading of the table or form and all subsequent changes, plus identification of who entered the data and who subsequently made changes and document reason why changes were made.

Data can be entered in a form. If changes are made to the form, a record is kept of the original entry and the details are stored in another table.

Create a table "Audit" and add the following fields:

FormName
ControlName
DateChanged
TimeChanged
PriorInfo
NewInfo
CurrentUser
Reason

Behind the forms module, copy and paste the following code:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the login name for Adminstrator use
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Function TrackChanges()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
strReason = InputBox("Reason For Changes")
strCtl = Me.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then rs.MoveLast
With rs
.AddNew
rs!FormName = Me.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Me.ActiveControl.OldValue
rs!NewInfo = Me.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

In the BeforeUpdate for each of the controls you want to audit on your form add the function TrackChanges

This will record in the Audit table the name of the Form, the specific Control, the date and time it was changed, the old value and the new value for the control, the current user making the change and the reason for the change.

It isn't as complete as what you wanted but it is what I use. I didn't find it necessary to record when the table was created or who created the table originally. I was only interested if the data was changed after the record was entered.

Hopefully this is something you can use.

lwells

Thank you for the code to create an audit trail. I had to do some tweaking as I had some error messages "Compile Error - Use-defined type not defined". I added in DAO to the Track Changes and changed the Me to Screen as the error message read "invalid use of ME keyword".

All fields to the Audit table are being updated, but the only problem I have left is the form name is not being added to the Audit table. Can you help me any further.

Many thanks.


Option Compare Database
Option Explicit

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

Function fOSUserName() As String

' Returns the login name for Adminstrator use

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If

End Function


Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String

strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT Audit.* FROM Audit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

The function TrackChanges() needs to be in the forms module not a global module in order for the Me.Name to work. So try this:

Screen.ActiveForm.Name

See if that will correct your problem.

lwells

Thank you this worked!

If I want to add additional data from the form to the audit table i.e. control "RefId", how would the code be inserted into module?

I assume you are wanting to identify which record was changed which is the Refld in your form. Add the following field to your Audit table - RecordID Next add this additional snippet to the existing code.

rs!RecordID = Screen.ActiveForm.CurrentRecord

This will add the current record to the Audit table to identify which record was changed.

lwells