My Audit Trail code compared to Allen Browne's

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello All

I have an app that i was thinking about auditing, and i looked at the code
Allen Browne has, and then this code a former collegue was using:

Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String

MsgBox "Auditing"

On Error GoTo ErrHandler

'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox, acCheckBox, acOptionGroup, acComboBox,
acOptionButton, acToggleButton:
MsgBox "value: " & .Value
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "tblAudit (DateEdited, UserChangedById, RecordID,
SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (#" & Now() & "#," _
& cDQ & Forms!zzMAINFORM!cboEmployee & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
'Debug.Print strSQL
MsgBox strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End Select
End With
Next
Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.DESCRIPTION & vbNewLine & Err.Number, vbOKOnly, "Error"

End Sub

The code above is a little less complicated, and seems to work. is there
anything that would be a problem? i have already noticed that it doesnt work
on subforms, so for every form the user can see at the time it would be
necessary to put the code on the form.

another problem i noticed is that when i have filter fields that i use to
allow users to put in criteria for a search in a list box or datasheet
subform, it seems to think those are valid changes and wants to record them
but doesnt. i assume that it doesnt because they are unbound, but it is
something that causes a little more processing time. i was thinking of
finding a way to "tag" those fields and tell the code to ignore them. any
ideas on that?

as always, any and all help appreciated. :)
 
Aside from the issues that you mentioned, this code is unreliable. Here's a
few reposts from Allen's take on the subject, that your code below does not
address in any way.

<quotes>
In the form's Delete event, the code below writes a copy of the record to
the temp table. In the form's AfterDelConfirm event, these records are copied
into the true audit table only if the Status argument provided by Access
indicates that the deletion proceeded.

There is no guarantee the update will be committed when BeforeUpdate occurs

if the update fails after the BeforeUpdate code has executed, the
AfterUpdate event does not occur
</quotes>

What event do you call your AuditTrail from, and how can you be sure that
everything happened as it was supposed to (you don't even have a return value
on your function to indicate the function itself performed properly, let
alone confirmation that events were'nt cancelled...)?

No offense, but I would trust Allens before yours... he has one of the best
disseminations of the process that can be found on the web, IMHO.

Your code should probably not be used for the requirement of serious audit
logging, particularly for the reasons described by Allen regarding how Access
events work and making sure that everything is confirmed. What use is an
audit log without confirmation that the audit works as planned?

I'm suprised you even asked... he explains the reasoning behind all of it.

In case you have only heard of it, and seen the bare code without the
accompanying explainations, here's the link:

http://allenbrowne.com/AppAuditCode.html

This should answer most of your questions in detail.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
"In case you have only heard of it, and seen the bare code without the
accompanying explainations, here's the link:"

i have heard of it, i read all of it, understand all of it, but i am not
trying to log the deletion of a record, but changes a user makes in a form. i
call it on the before update event. the only things users can do are add
records or alter them. there is no actual deleting records, only certain
users can "delete" and even then they are really only causing a date to be
put in a field that filters out the record.

all i want to do is be able to know who changed a record. the code posted
seems to work, the real question i have at this point is why you think it
wouldnt? i tried it a few times and each actual bound field that i changed
was recorded on the form that i had set this code up for.

i would trust Allen's over mine as well, but i am not sure i need to do all
the stuff that his is doing. basically mine happens when the user "clicks
away" from the current record and access updates the record before they can
see the next if there was a change.

in the set up that i am using i dont allow confirmation events to bother the
user, that is a potential for confusion and errors that i handle in the
background and dont want the user having to deal with, and so i couldnt use
Allen's if i wanted to.

thanks for the response
 
i have heard of it, i read all of it, understand all of it, but i am not
trying to log the deletion of a record
Click to expand...

Based on the subject line of your post, I was under the impression that you
wanted your code compared to Allen's, which is what I did.

i have heard of it, i read all of it, understand all of it, but i am not
trying to log the deletion of a record, but changes a user makes in a form.
Click to expand...

Please do not ask us to compare your code to other code, if that is not what
you want. For the record, Allen's code handles changes (in the best
capabilities of access, I might add) as well as deletions, and additions.


the real question i have at this point is why you think it wouldnt?
Click to expand...

Because you said you were having problems with it,

ex:
and because you have no method of confirming the update, which I believe to
be a weak point for someone intending to track activity in a record.



And as a side note, why not use a recordset clone to capture the data before
an update? Then you wouldn't have these issues with it trying to save
controls that are not bound, and your code would probably be quite a bit
cleaner. Just a thought. This is the way most people do it, I think, if
they don't have a requirement for *serious* auditing, in which case they
probably use something very similar to Allen's code.


Bottom line... you asked for a comparison for your code vs. Allen's, which
is what I gave.



--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
Hello Dymondjack

i have been thinking about this situation. i think i should have asked the
question, what or why my audit code would not work? what did Allen's do that
mine doesnt in terms of useage situations.

again mine seems to work, and some of the things that Allen's does, should
not be an issue in my app, deleting, for example.

is there an instance where my code might fail me?
 
Back
Top