Inconsistant Compile Error using Allen Browne's Audit Coding (1 Viewer)

[PaulA]

I am using Allen Browne's coding for setting up an auditing process for my database containing multiple tables that will require editing. I successfully did it for one table and then copied and pasted the code under other tables, changing the specifics of the call function. It seems for all the other tables, I'm getting one of two compile errors: "ByRef argument type mismatch" or "Variable not defined", both of them highlighting the argument "bWasNewRecord" in the call function.

Here is a sample of the call function causing the error:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Exit Sub
Else
Call AuditEditBegin("tblIndClinicalUtilTrack", "audIndClinicalUtilTrack", "IndSessionID", Nz(Me.IndSessionID, 0), bWasNewRecord)
End If

End Sub

Here is Allen Brown's AuditEditBegin coding:

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean

    'Purpose:    Write a copy of the old values to temp table.
    '            It is then copied to the true audit table in AuditEditEnd.
    'Arugments:  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

    '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 ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
        db.Execute sSQL, dbFailOnError
    End If
    AuditEditBegin = True

Exit_AuditEditBegin:
    Set db = Nothing
    Exit Function
    
End Function

Not sure why it worked on one table but none of the others.

Any help would be appreciated.

Paul

 

[Ranman256]

Did you define all your variables in Form_BeforeUpdate or FORM ?
like
DIM bWasNewRecord AS Boolean

always use OPTION EXPLICIT at the top of every module.

 

[sneuberg]

I don't see where you are defining bWasNewRecord. Somewhere there should be a

Dim bWasNewRecord as Boolean

and somewhere

bWasNewRecord = True

or

bWasNewRecord = False

before

Call AuditEditBegin("tblIndClinicalUtilTrack", "audIndClinicalUtilTrack", "IndSessionID", Nz(Me.IndSessionID, 0), bWasNewRecord)

Since you have

If Me.NewRecord Then

Maybe it should just be

Call AuditEditBegin("tblIndClinicalUtilTrack", "audIndClinicalUtilTrack", "IndSessionID", Nz(Me.IndSessionID, 0), False)

 

[PaulA]

Thanks to both of you.

All I can say is "duh.."

You are both correct. I forgot the Options Explicit and bWasNewRecord, both at the top of the window.

thanks...

 

[Cronk]

Why bother with bWasNewRecord in the calling form?

The Audit recording routine is only called when a new record is NOT added ie just use False in place of bWasNewRecord.

 

[PaulA]

A good question. Actually, I'm confused now. While the functions are working, I am getting new records added to the audit tables when I don't want them to. I only want edited or deleted records.

As you said, I thought the the "me.new record" would result in leaving the sub.

 

[Cronk]

Is the Audit procedure being called from anywhere else?

If you want to see when the audit procedure is being called, add a new line
Stop
after the Dim statements.

The code will be interrupted on this line and you can resume its running with the F5 key, or even step through the code with successive presses of the F8 key.