VBA problem with audit tables

[Tomino]

Hi all,

I have small issue with the code from Allen Brown. His audit code works ok, no problem, but I would like to register a reason in the audit table when the user change something.

I placed a 4th field inside the audit table "audReason" and this variable comes from an inputbox. the problem after running this code is that my errorlog table always gives the error "to few parameters", error 3061. Can you please help me out with this? I am working on it for days and nothing really works

thanks in advance for your help, it's greatly appriciated!

Option Compare Database
Option Explicit

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 AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
On Error GoTo Err_AuditDelBegin
'Purpose: Write a copy of the record to a tmp audit table.
' Copy to be written to real audit table in AfterDelConfirm.
'Arguments: sTable = name of table to be audited.
' sAudTmpTable = the name of the temp audit table.
' sKeyField = name of AutoNumber field in table.
' lngKeyValue = number in the AutoNumber field.
'Return: True if successful.
'Usage: Call from a form's Delete event. Example:
' Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
Dim db As DAO.Database ' Current database
Dim sSQL As String ' Append query.

' Append record to the temp audit table.
Set db = DBEngine(0)(0)
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError

Exit_AuditDelBegin:
Set db = Nothing
Exit Function

Err_AuditDelBegin:
Call LogError(Err.Number, Err.Description, conMod & ".AuditDelBegin()", , False)
Resume Exit_AuditDelBegin
End Function


Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
On Error GoTo Err_AuditDelEnd
'Purpose: If the deletion was completed, copy the data from the
' temp table to the autit table. Empty temp table.
'Arguments: sAudTmpTable = name of temp audit table
' sAudTable = name of audit table
' Status = Status from the form's AfterDelConfirm event.
'Return: True if successful.
'Usage: Call from form's AfterDelConfirm event. Example:
' Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
Dim db As DAO.Database ' Currrent database
Dim sSQL As String ' Append query.

' If the Delete proceeded, copy the record(s) from temp table to delete table.
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
Set db = DBEngine(0)(0)
If Status = acDeleteOK Then
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
db.Execute sSQL, dbFailOnError
End If

'Remove the temp record(s).
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
AuditDelEnd = True

Exit_AuditDelEnd:
Set db = Nothing
Exit Function

Err_AuditDelEnd:
Call LogError(Err.Number, Err.Description, conMod & ".AuditDelEnd()", False)
Resume Exit_AuditDelEnd
End Function


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.
'Arguments: 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
Dim audReason As String

audReason = InputBox("Give a reason for changes")

'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, audReason ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, 'audReason' AS Expr4, " & 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


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.
'Arguments: 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
Dim audReason As String
Set db = DBEngine(0)(0)

audReason = InputBox("Give a reason for changes")

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser, audReason ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, 'audReason' AS Expr4, " & 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, audReason ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, audReason AS Expr4, " & 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

 

[jdraw]

Hi all,

I have small issue with the code from Allen Brown. His audit code works ok, no problem, but I would like to register a reason in the audit table when the user change something.

I placed a 4th field inside the audit table "audReason" and this variable comes from an inputbox. the problem after running this code is that my errorlog table always gives the error "to few parameters", error 3061. Can you please help me out with this? I am working on it for days and nothing really works

thanks in advance for your help, it's greatly appriciated!

Option Compare Database
Option Explicit

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 AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
On Error GoTo Err_AuditDelBegin
'Purpose: Write a copy of the record to a tmp audit table.
' Copy to be written to real audit table in AfterDelConfirm.
'Arguments: sTable = name of table to be audited.
' sAudTmpTable = the name of the temp audit table.
' sKeyField = name of AutoNumber field in table.
' lngKeyValue = number in the AutoNumber field.
'Return: True if successful.
'Usage: Call from a form's Delete event. Example:
' Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
Dim db As DAO.Database ' Current database
Dim sSQL As String ' Append query.

' Append record to the temp audit table.
Set db = DBEngine(0)(0)
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError

Exit_AuditDelBegin:
Set db = Nothing
Exit Function

Err_AuditDelBegin:
Call LogError(Err.Number, Err.Description, conMod & ".AuditDelBegin()", , False)
Resume Exit_AuditDelBegin
End Function


Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
On Error GoTo Err_AuditDelEnd
'Purpose: If the deletion was completed, copy the data from the
' temp table to the autit table. Empty temp table.
'Arguments: sAudTmpTable = name of temp audit table
' sAudTable = name of audit table
' Status = Status from the form's AfterDelConfirm event.
'Return: True if successful.
'Usage: Call from form's AfterDelConfirm event. Example:
' Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
Dim db As DAO.Database ' Currrent database
Dim sSQL As String ' Append query.

' If the Delete proceeded, copy the record(s) from temp table to delete table.
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
Set db = DBEngine(0)(0)
If Status = acDeleteOK Then
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
db.Execute sSQL, dbFailOnError
End If

'Remove the temp record(s).
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
AuditDelEnd = True

Exit_AuditDelEnd:
Set db = Nothing
Exit Function

Err_AuditDelEnd:
Call LogError(Err.Number, Err.Description, conMod & ".AuditDelEnd()", False)
Resume Exit_AuditDelEnd
End Function


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.
'Arguments: 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
Dim audReason As String

audReason = InputBox("Give a reason for changes")

'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, audReason ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, 'audReason' AS Expr4, " & 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


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.
'Arguments: 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
Dim audReason As String
Set db = DBEngine(0)(0)

audReason = InputBox("Give a reason for changes")

If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser, audReason ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, 'audReason' AS Expr4, " & 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, audReason ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, audReason AS Expr4, " & 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

First let me say I'm a fan of Allen Browne. I have been through many of his tutorials and have found his help on forums (dynamic forms/subforms) very helpful.

I took a quick look at your code, and I see an inconsistency in your formats. It may not be the root of the problem but I'm sure it needs to be looked at.

In this line, notice the quotes around audReason

"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, [COLOR="DarkRed"][B]'audReason'[/B][/COLOR] AS Expr4, " & sTable & ".* " & _

and look in this line audReason with no quotes.

 ' Copy the new values as "EditTo"
        sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser, audReason ) " & _
            "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, [COLOR="Blue"][B]audReason [/B][/COLOR]AS Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"

I think it does NOT need the quotes in any of the strings in which you are building the SQL.

Give it a try and let us know.

 

[Tomino]

Hi Jdraw,

Thanks for your reply, it's really appriciated. I tried both to put quotes around "audReason" and without quotes. With quotes it just put the value 'Audreason' as reason in the table, without quotes I get error 3061 that says: "to few parameters, expected 1" so none of them works...
What I want to do is put the variable "audReason" inside my audTable, that variable has to come from an Inbox but I really don't know how, I know you can put a textfield with 'quotes' inside the audtable, or the result of a function like UserName() but I don't know hwo to put a variable inside the table with SQL...

Any help is greatly appriciated!!!

Thanks and regards,
Tomino

 

[jdraw]

Hi Jdraw,

Thanks for your reply, it's really appriciated. I tried both to put quotes around "audReason" and without quotes. With quotes it just put the value 'Audreason' as reason in the table, without quotes I get error 3061 that says: "to few parameters, expected 1" so none of them works...
What I want to do is put the variable "audReason" inside my audTable, that variable has to come from an Inbox but I really don't know how, I know you can put a textfield with 'quotes' inside the audtable, or the result of a function like UserName() but I don't know hwo to put a variable inside the table with SQL...

Any help is greatly appriciated!!!

Thanks and regards,
Tomino

You're in test mode right?
Can you try to add a record to your table via SQL? Perhaps you've already done this.

If you use 'audReason', it will try to put that string into the record.

If you use audReason (no quotes) it will use the value associated with that variable. So no quotes are required.

Also, I don't like the as Expr1... as Expr2 etc in your vba. These are aliases and are added by default by Access. They are not required.

 

[Tomino]

hi,

Yes, I am in test mode at this moment. adding a record works thx to Allen Brown his code, the code puts UserName(), Now(), all the old values etc inside the table but when I want to put a reason also inside the table (expression 4) it doesn't work (error msg 3061)...can I just let the expressions out of my code?
How can I put expression 4 (audReason as a variable) inside the audtable?

Thanks a lot for your assistance,

regards,
Tom