Put reason in audit table

[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 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!

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

[Hack]

If audReason is a field in your table, then you would need to change that to a string varible, and update the audReason field with the contents of that string.

[Tomino]

Hi Hack,

Thanks for your reply. AudReason is indeed a field in my table, I declared audreason = String in my code and in the table is the field audReason = text. I ask the reason of changes (audReason) from the user through an inputbox (audReason = inputbox "give your reason of changes") and after, I try to write it to the field "audReason" inside the audit table but I always get the error 3061 "too few parameters, expected 1".
Can you please help me out on this?
How would you write the code? What would you try to change to make it work?

Thanks and best regards,
Tomino

[si_the_geek]

I assume this is the part you are referring to:

' 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 & ");"

The problem here is that you have specified four fields to insert values into:

audType, audDate, audUser, audReason

..but have provided more than four values:

'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, audReason AS Expr4 , " & sTable & ".*

[Tomino]

Hi all,

I found the solution to my problem for audReason in expression 4:

Instead of ..., audReason as Expression 4, ...

it has to be:

..., '" & audreason & "' AS Expression 4, ...

the ampersand and quotes did the trick, for the rest I didn't had to change anything ;-)

thanks a lot for your help!

Kind regards,
Tom

[Hack]

Again, Welcome to the forums.

If you consider this resolved, you could help us out by pulling down the Thread Tools menu and clicking the Mark Thread Resolved menu item. That will let everyone know that you have your answer.

Thank you.