Audit Trail

[Cathy C]

I need to be able to track who makes changes to data and the date of change.
Any suggestions?

Thanks for your assistance.
Cathy

 

[Cheryl Fischer]

Here are a couple of links from the MS Knowledgebase which show one way to
do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you want
to maintain it in the same table or in a separate table, etc. For another
look at this subject, here is a link to an excellent article by MVP Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html

 

[Cathy C]

Thanks, Cheryl. I'll check these out.

Here are a couple of links from the MS Knowledgebase which show one way to
do it:

Access 97: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;183792

Access 2000: How to Create an Audit Trail of Record Changes in a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

There are many ways to create an audit trail of changes to records,
depending on what kind of info you want in the audit trail, whether you want
to maintain it in the same table or in a separate table, etc. For another
look at this subject, here is a link to an excellent article by MVP Allen
Browne:

http://members.iinet.net.au/~allenbrowne/AppAudit.html

 

[Joan Wild]

I need to be able to track who makes changes to data and the date of
change. Any suggestions?

See if this gives you a start.
Creating an Audit Log
http://members.iinet.net.au/~allenbrowne/AppAudit.html

 

[Cathy C]

I'm getting a page cannot be displayed on this one. Can you open it?

 

[Cheryl Fischer]

The link works for me, Cathy.

 

[Allen Browne]

Let me know if you can't get through, Cathy.

 

[Cathy C]

I can't. I've tried it on two different computers. What's up with that?

 

[Cathy C]

Someone just emailed it to me. I wonder why I couldn't get to your site?

 

[Cathy C]

The following code is working, but it puts Admin for the name of the person
who made the changes regardless of who made the changes. Any suggestions on
how to put the person's name who made the change(s)?

Option Compare Database
Option Explicit
Function AuditTrail()
On Error GoTo Err_Handler

Dim Myform As Form, C As Control, xName As String, HeaderWritten As Boolean,
Header As String
Set Myform = Screen.ActiveForm
HeaderWritten = False

'If new record, record it in audit trail and exit sub.
If Myform.NewRecord = True Then
Myform!UPDATES = "New Record added by " & CurrentUser() & " on " & Now() &
"."
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & ""
'Exit Function
End If

Header = "Changes made on " & Now() & " by " & CurrentUser() & ":"

'Check each data entry control for change and record old value of Control.
For Each C In Myform.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
If C.NAME <> "UPDATES" Then
'If control had previous value, record previous value.
'Nonblank old value; nonblank current value
If C.Value <> C.OldValue Then
If HeaderWritten = False Then
HeaderWritten = True
If Myform!UPDATES = "" Then
Myform!UPDATES = Header
Else
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & Header
End If
End If
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & _
C.NAME & " - previous value was " & Chr(34) & C.OldValue &
Chr(34) & "; current value is " & Chr(34) & C.Value & Chr(34) & "."
End If

'blank old value; nonblank current value
If IsNull(C.OldValue) Or C.OldValue = "" Then
If Not (IsNull(C.Value)) Or C.OldValue <> "" Then
If HeaderWritten = False Then
HeaderWritten = True
If Myform!UPDATES = "" Then
Myform!UPDATES = Header
Else
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & Header
End If
End If
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & _
C.NAME & " - previous value was blank; current value is " &
Chr(34) & C.Value & Chr(34) & "." ' & C.OldValue
End If
End If

'nonblank old value; blank current value
If IsNull(C.Value) Or C.Value = "" Then
If Not (IsNull(C.Value)) Or C.OldValue <> "" Then
If HeaderWritten = False Then
HeaderWritten = True
If Myform!UPDATES = "" Then
Myform!UPDATES = Header
Else
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & Header
End If
End If
Myform!UPDATES = Myform!UPDATES & Chr(13) & Chr(10) & _
C.NAME & " - previous value was " & Chr(34) & C.OldValue &
Chr(34) & "; current value is blank."
End If
End If

End If
End Select
ContinueC:
Next C

'blank line between entries
If HeaderWritten = True Then Myform!UPDATES = Myform!UPDATES & Chr(13) &
Chr(10) & ""

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " &
Err.Description
Resume TryNextC
End If

If Err.Number = 64535 Then Resume ContinueC
End Function

 

[Joan Wild]

The following code is working, but it puts Admin for the name of the
person who made the changes regardless of who made the changes. Any
suggestions on how to put the person's name who made the change(s)?

The code uses the CurrentUser() function which will return the Access
username. If you haven't implemented security in Access, then the current
user is always 'Admin'. You can use the function at
http://www.mvps.org/access/api/api0008.htm
to return the network username instead.