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

I used your code and it is working perfectly, but how do I stop the 'Reason for changes' box coming up every time a new record is added.

Hi Fiona

If you delete Dim strReason As String

strReason = InputBox("Reason For Changes")

Anywhere where it appears in the code, the reason dialog box will not come up.

Cheers

Spirited Away

Quote:

Originally Posted by Csteed [COLOR=Blue]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

hi Spireted Away, i used this code and is great just one thing, i am new at this and i am trying to add copuple fileds to show in the audit table that are in the form. Project NAme and Project Area, do know exactly how to accomplish any help is welcome.

Thanks!

Hi dof2001, Add the following field to your Audit table - ProjectName and ProjectArea and then next add this additional snippet to the existing code.

rs!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord


As Below....

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!ProjectName = Screen.ActiveForm.CurrentRecord
rs!ProjectArea = Screen.ActiveForm.CurrentRecord
rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

This should then record changes to those two fields.

Cheers

Spirited Away

Hello Spireted Away,

Thanks for the prompt response, i follow your isntruction but there is one problem, in the fields addedd i don't get the actual contents of the fields, instead i get the record number. i need to get the actual project name and area if possible.

Thanks in advance for your time.

Never Mind i figure it out.


Thanks

Hello,

This works in a form... but is there a way to make it audit what is entered or changed in a drop down menu in a data access page?

Thanks,

MattB

I copied the code into the module and named it TrackChanges. Created the Table Audit. I went into each control field in the form and in the Beforeupdate added "Event Procedure" and then did Call TrackChanges.

Nothing happens what I am doing wrong, I get an error though

In the BeforeUpdate for each of the controls forms, how do you add function trackchanges. I am new on access 2007 database. how do you add trackchanges in module to work with form?
Thanking you in advanced, ade

I know this is an old post, but I'm hoping someone can still answer. I have forms where records have been deleted. So the currentrecord doesn't return my actual record number. What code would return the value of my records autonumber field?

thanks in advance for any assistance.

Added "Address" field in audit table and following code has been updated.. Need help to get the right code ....Address field get the record number only not the actual data.....


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.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!CurrentUser = fOSUserName
rs!Address = Screen.ActiveForm.Name

rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function

Hi everyone.

I have used the code but i get a message

"Run-time error'13':Type mismatch"
and inside the code is highlighted the line

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


can you please help me on this?

thank you in advance.

[audit trail] [ms access 2010] I realize also that this an old thread - but just in case there's still someone monitoring. How can this same procedure be done with ACCESS 2010 ?? Any help would be appreciated...Thank You!

I would like to know how to modify this code to recognize combo boxes and check boxes. Any help is greatly appreciated. I've tried to incorporate a select case statement but that is not working.

Code should be the same for Access 2010.

As far as I can tell, code doesn't care what type the control is. As long as the control's name is in the Audit table and retrieved in the recordset, should work.

details are stored in another table.

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.
URL
URL

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.

Hi.
I used the code above but I'm having to problem:
1) The old value and the new value are always coming up as the same
2) I have a check box that when clicked deletes the values of other boxes on the same form but the audit trail only records the change to the checked box and not those automatically deleted. Any way to track those changes?

Thanks!

One word.....Awesome.

Hi,

I used this code wich works like a charme
But i would like to optimize it: currently we need to copy the code in each forms that needs to be audited.

I will need to use it in a Db where I will have aroud 50 forms to audit; How can I made the code global to avoid aving to copy it 50 times?

Thanks in advance for your suggestions

david

I expect that will be very complicated code. I do something similar to replicate data from existing record to create a new record but the source and destination tables will vary. An excerpt from my code is: In your case the source data is on form. Dynamic reference to form and controls using variables is like:
Forms(strFormName).Controls(strControlName)
You should attempt code and when you encounter specific issue start a thread with your question.

How to create an audit log to record the history of changes to records in a Microsoft ?