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 always gives the error "to few parameters, expected 1", 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!

Code:
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 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'(code gives here error 3061)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
In fact, I know how to put a function like UserName() or some fixed text inside the audTable but I want to put the variable "audReason" (that I get from the user using an inputbox) inside the audtable.

Thanks a lot for your assistance, it's greatly appriciated!

Kind regards,
Tomino