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 4  2643 
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Keith |
last post by:
Hi
I am developing an ASP application which will interact with a SQL database.
A requirement of the application is that there is a full audit...
|
by: Zlatko Matić |
last post by:
Hello.
I tried to implement audit trail, by making an audit trail table with the
following fileds:...
|
by: JohnO |
last post by:
Thanks to Serge and MarkB for recent tips and suggestions.
Ive rolled together a few stored procedures to assist with creating
audit triggers...
|
by: Bradley Burton |
last post by:
I've successfully set up Allen Brown's code for audit logging
(http://allenbrowne.com/ajbAudit.txt), but I think there is something wrong.
It...
|
by: Parag |
last post by:
Hello,
I have been assigned the task to design the audit trail for the ASP.NET web
application. I don't know what the best practices for such audit...
|
by: Oscar Santiesteban Jr. |
last post by:
I need help...here is the problem.
Last weekend, the servers in our datacenter where moved around. After this
move, and maybe coincidental, 1...
|
by: sara |
last post by:
I use Allen Browne's Audit Trail code in everything I do and I love
it.
Recently, I've run into a problem I can't figure out.
I have a...
|
by: babamc4 |
last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen...
|
by: zandiT |
last post by:
hello
i'm using the microsoft audit trail example ACC2000: How to Create an Audit Trail of Record Changes in a Form and im having a problem with my...
|
by: concettolabs |
last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
|
by: teenabhardwaj |
last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
|
by: CD Tom |
last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
|
by: jalbright99669 |
last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
| |
