Audit Trail Code

[Konchetta via AccessMonster.com]

I have the following code to help with an audit trail in Access 2007 but it
keeps bringing me back to the code below when I try to save my form. It
highlights Call LogError but the first and second lines are in all yellow. I
am a novice and have no clue of what all that means. I copied the code of
course.

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField
As String, _
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditBegin
'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin("tblInvoice", "audTmpInvoice",
"InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database ' Current database
Dim sSQL As String

'Remove any cancelled update still in the tmp table.
Set db = DBEngine(0)(0)
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL

' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser )
" & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
End If
AuditEditBegin = True

Exit_AuditEditBegin:
Set db = Nothing
Exit Function

Err_AuditEditBegin:
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", ,
False)
Resume Exit_AuditEditBegin
End Function

 

[Dorian]

I don't have version 2007 but I suspect one of your SQL calls is incorrect.
Does your project compile ok?
Try putting a 'Msgbox sSQL' statement before these statements.
db.Execute sSQL
db.Execute sSQL, dbFailOnError
You could also try commenting out the 'On Error GoTo' statement (put a
single quote in front of it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Tokyo Alex]

Hi,

LogError is not a native VBA function, but a procedure written by the author
of this code (Allen Browne, I'm guessing). If you don't have that procedure
installed your code won't run because Access doesn't know what to do.

Try commenting out
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", ,
False)

and see if that solves your problem.

Cheers,
Alex.

 

[Allen Browne]

Looks like code from here:
http://allenbrowne.com/AppAudit.html
#3 under the 3rd heading of that article says:
Either remove the four references to function LogError(),
or set up error logging as described:
http://allenbrowne.com/ser-23a.html

The fact that the error handler is being called indicates that you may have
another issue to sort out also. you will need to follow the instructions
exactly (e.g. regarding the field names, order, and indexes of your temp and
audit tables.)

 

[Konchetta via AccessMonster.com]

Thanks Alex, yes it is from Allen Browne. I will try and see what works and
follow his instructions as he stated. Thanks so much for responding.

Hi,

LogError is not a native VBA function, but a procedure written by the author
of this code (Allen Browne, I'm guessing). If you don't have that procedure
installed your code won't run because Access doesn't know what to do.

Try commenting out
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", ,
False)

and see if that solves your problem.

Cheers,
Alex.

I have the following code to help with an audit trail in Access 2007 but it
keeps bringing me back to the code below when I try to save my form. It

[quoted text clipped - 47 lines]

Resume Exit_AuditEditBegin
End Function

 

[Konchetta via AccessMonster.com]

Thanks Mr. Browne,
Yes it is your code and I tried to follow the instructions but being a newbie
at this is really the problem of course. I will run back through the
instructions to see what I am missing. I know I didn't do anything with #3 so
that most likely is my problem. Thanks again for your assistance!!!

Looks like code from here:
http://allenbrowne.com/AppAudit.html
#3 under the 3rd heading of that article says:
Either remove the four references to function LogError(),
or set up error logging as described:
http://allenbrowne.com/ser-23a.html

The fact that the error handler is being called indicates that you may have
another issue to sort out also. you will need to follow the instructions
exactly (e.g. regarding the field names, order, and indexes of your temp and
audit tables.)

I have the following code to help with an audit trail in Access 2007 but
it

[quoted text clipped - 54 lines]

Resume Exit_AuditEditBegin
End Function

 

[Konchetta via AccessMonster.com]

Thanks fo r your response Dorian, I will try and see what works. I know I am
missing a few things just have to figure out what in the world they are.
Thanks a bunch!!

I don't have version 2007 but I suspect one of your SQL calls is incorrect.
Does your project compile ok?
Try putting a 'Msgbox sSQL' statement before these statements.
db.Execute sSQL
db.Execute sSQL, dbFailOnError
You could also try commenting out the 'On Error GoTo' statement (put a
single quote in front of it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I have the following code to help with an audit trail in Access 2007 but it
keeps bringing me back to the code below when I try to save my form. It

[quoted text clipped - 47 lines]

Resume Exit_AuditEditBegin
End Function