Allen Brown's audit log code problem

[Bradley Burton]

I'm using Allen Brown's code for audit logging
(http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud
table doesn't populate with the tracking info at all. I think it might be a
problem with the table set-up. I just can't find the problem. These are
the fields in my table:
Table1

ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber

I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does
anyone have any suggestions?

Thanks,

Brad

[Tom van Stiphout]

On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

What is the value of sSQL at that time?
-Tom.

I'm using Allen Brown's code for audit logging
(http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud
table doesn't populate with the tracking info at all. I think it might be a
problem with the table set-up. I just can't find the problem. These are
the fields in my table:
Table1

ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber

I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this. Does
anyone have any suggestions?

Thanks,

Brad

[Bradley Burton]

sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
This is the function:

Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As
String, _
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As
Boolean
On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the 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 AfterUpdate event. Example:
' Call AuditEditEnd("tblInvoice", "audTmpInvoice",
"audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database
Dim sSQL As String
Set db = DBEngine(0)(0)

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable
& ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " &
sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True

Exit_AuditEditEnd:
Set db = Nothing
Exit Function

Err_AuditEditEnd:
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", ,
False)
Resume Exit_AuditEditEnd
End Function

Thanks.

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:kv********************************@4ax.com...

On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

What is the value of sSQL at that time?
-Tom.

I'm using Allen Brown's code for audit logging
(http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud
table doesn't populate with the tracking info at all. I think it might be
a
problem with the table set-up. I just can't find the problem. These are
the fields in my table:
Table1

ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber

I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute
sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this.
Does
anyone have any suggestions?

Thanks,

Brad

[Tom van Stiphout]

On Thu, 21 Apr 2005 19:01:30 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

I think I see the problem. Your INSERT INTO statement at runtime looks
something like this:
INSERT INTO tblTest(audType, audDate, audUser )
SELECT 'Insert', #5/21/2005 12:00:00#, 'BBurton', SomeTable.*
FROM SomeTable
WHERE (SomeTable.SomeID=123);

This is really the value I was asking you to supply. Just set a
breakpoint at that point in the code, and in the Immediate window
write:
?sSQL

Then create a new query, switch to SQL view, and paste in this text,
and try to run it. You'll find that the query processor will complain
because of a syntax error.
Looking at the sql, you are inserting into 3 fields, but you are
sending 3 values plus however many fields SomeTable has. You have to
supply as many values as you are specifying fields for.

Code like this would likely work:
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) "
& _
"SELECT 'Insert' AS Expr1, Now() AS Expr2,
NetworkUserName() AS
Expr3 & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField &
" = "
& lngKeyValue & ");"

-Tom.

sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
This is the function:

Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As
String, _
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As
Boolean
On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the 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 AfterUpdate event. Example:
' Call AuditEditEnd("tblInvoice", "audTmpInvoice",
"audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database
Dim sSQL As String
Set db = DBEngine(0)(0)

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable
& ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " &
sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser )
" & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = "
& lngKeyValue & ");"
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True

Exit_AuditEditEnd:
Set db = Nothing
Exit Function

Err_AuditEditEnd:
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", ,
False)
Resume Exit_AuditEditEnd
End Function

Thanks.

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:kv********************************@4ax.com.. .

On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

What is the value of sSQL at that time?
-Tom.

I'm using Allen Brown's code for audit logging
(http://allenbrowne.com/AppAudit.html), but I'm having a problem. My aud
table doesn't populate with the tracking info at all. I think it might be
a
problem with the table set-up. I just can't find the problem. These are
the fields in my table:
Table1

ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber

I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute
sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this.
Does
anyone have any suggestions?

Thanks,

Brad

[Bradley Burton]

It turned out that it was a stupid mistake by me. The ID field wasn't on my
form. Thanks for the help though.

Brad
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:do********************************@4ax.com...

On Thu, 21 Apr 2005 19:01:30 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

I think I see the problem. Your INSERT INTO statement at runtime looks
something like this:
INSERT INTO tblTest(audType, audDate, audUser )
SELECT 'Insert', #5/21/2005 12:00:00#, 'BBurton', SomeTable.*
FROM SomeTable
WHERE (SomeTable.SomeID=123);

This is really the value I was asking you to supply. Just set a
breakpoint at that point in the code, and in the Immediate window
write:
?sSQL

Then create a new query, switch to SQL view, and paste in this text,
and try to run it. You'll find that the query processor will complain
because of a syntax error.
Looking at the sql, you are inserting into 3 fields, but you are
sending 3 values plus however many fields SomeTable has. You have to
supply as many values as you are specifying fields for.

Code like this would likely work:
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) "
& _
"SELECT 'Insert' AS Expr1, Now() AS Expr2,
NetworkUserName() AS
Expr3 & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField &
" = "
& lngKeyValue & ");"

-Tom.

sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName()
AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " =
"
& lngKeyValue & ");"
This is the function:

Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable
As
String, _
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As
Boolean
On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the 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 AfterUpdate event. Example:
' Call AuditEditEnd("tblInvoice", "audTmpInvoice",
"audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database
Dim sSQL As String
Set db = DBEngine(0)(0)

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate,
audUser )
" & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName()
AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " =
"
& lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " &
sAudTmpTable
& ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY "
&
sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate,
audUser )
" & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName()
AS
Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " =
"
& lngKeyValue & ");"
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True

Exit_AuditEditEnd:
Set db = Nothing
Exit Function

Err_AuditEditEnd:
Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()",
,
False)
Resume Exit_AuditEditEnd
End Function

Thanks.

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:kv********************************@4ax.com. ..

On Wed, 20 Apr 2005 18:23:03 -0500, "Bradley Burton"
<br***********@comcast.net> wrote:

What is the value of sSQL at that time?
-Tom.
I'm using Allen Brown's code for audit logging
(http://allenbrowne.com/AppAudit.html), but I'm having a problem. My
aud
table doesn't populate with the tracking info at all. I think it might
be
a
problem with the table set-up. I just can't find the problem. These
are
the fields in my table:
Table1

ID (primary key) AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTmpTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audTable1

audType Text
audDate Date/Time
audUser Text
ID AutoNumber
Defect Number
End-user Text
Date Date/Time
Summary Memo
audID (primary key) AutoNumber

I didn't substitute anything in the code with audID, I just used 'ID' in
place of InvoiceID (I'm not sure what audID is for). I commented out
the
error handler and checked the code line by line. I'm getting a runtime
error 3131 (Syntax error in FROM clause). It highlights "db.Execute
sSQL."
When I comment that out, I also get other errors with db.Execute sSQL
(Syntax error in INSERT INTO statement). I'm not sure how to fix this.
Does
anyone have any suggestions?

Thanks,

Brad