implemented the code from Allen Browne's website for auditing changes and
everything works fine except for one problem. The only audTypes that are
showing up in the audit table, audErrorReportTable, are Edit To, and Edit
From. The users don't have the capability to delete, so that audType will
never show up anyway, but all new records are showing up as EditTo instead of
Insert. I have a few questions about what might be causing this.
1.) Would having a form autofill fields from a previous record dirty the
new record and cause the new record to have an audType of EditTo?
I am guessing that it wouldn't because I tried it without the form
autofilling, but I still had the same problem. I am not a programmer, so I
just want to be sure and consider all possibilities.
2.) Would this problem be caused by default values?
The really odd thing is that I have tried removing all default values from
the form and the table, and also without the form autofilling values, but
after I do that, the audit trail stops capturing records at all. I have
posted the code from the audit trail below, as implemented per Allen Brown's
instructions, and also the code needed for the form.
Option Compare Database
' Author: Allen Browne, XXXX@XXXXX.COM , 2002.
' Purpose: Audit trail, to track Deletes, Edits, and Inserts.
' Does not audit any Cascading Updates/Deletes.
' Requirements: The table to be audited must have an AutoNumber primary key.
' Data entry must be through a form.
' Method: Makes a copy of the record in a temp table, and logs the
' change when it is guaranteed. The temp table copes with:
' - multiple deletes at once (continuous/datasheet view)
' - cancelled deletes or failed updates.
' - requirement for sequential numbering in the audit table.
' On a multi-user split (front-end/back-end) database, the
' temp table may reside in the front end, and the audit log
' in the back-end.
' Result: The audit table will contain one record for each deletion or
' insertion, and two records for each edit (before and after).
' Delete Copy of the deleted record, marked "Delete".
' Insert Copy of the new record, marked "Insert".
' Change: Copy of the record before change, marked "EditFrom".
' Copy of the record after change, marked "EditTo".
' This approach, together with the sequential numbering of the
' AutoNumber in the audit table makes tampering with the audit
' log more detectable.
'Note: Record confirmations need to be on. When opening the database:
' If Not Application.GetOption("Confirm Record Changes") Then
' Application.SetOption ("Confirm Record Changes"), True
' End If
Private Const conMod As String = "ajbAudit"
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function NetworkUserName() As String
On Error GoTo Err_Handler
'Purpose: Returns the network login name
Dim lngLen As Long
Dim lngX As Long
Dim strUserName As String
NetworkUserName = "Unknown"
strUserName = String$(254, 0)
lngLen = 255&
lngX = apiGetUserName(strUserName, lngLen)