Link to home
Start Free TrialLog in
Avatar of gbnorton
gbnortonFlag for United States of America

asked on

Allen Browne's Audit Log code

There is a lot of stuff here.  Please bear with me.  I'm trying to implement Allen Browne's Audit Log

http://allenbrowne.com/AppAudit.html   The pdf is attached.

I've studied it and added the code as described to my app.

I want to understand the how it is supposed to work(of course I have errors too)

There are four functions.  Right now I'm focused on edit.

The AuditEditBegin("Diodes", "audTempDiodes", "audID", Nz(Me.audID, 0), bWasNewRecord) function is called in the form BeforeUpdate event.  

The AuditEditEnd("Diodes", "audTempDiodes", "audDiodes", "audID", Nz(Me!audID, 0), bWasNewRecord) function is called in the form AfterUpdate event.  

So if I open the form to edit a record.  Then edit a text box value.  When is the form BeforeUpdate event called?  

When is the form AfterUpdate event called?

...and

... the Nz(Me.audID,0)  in AuditEditBegin It is supposed to be passing the value of the field audID.  But when executed I get Compile error: Method or data member not found and .audID is highlighted.

Here is the function call and function:
Call AuditEditBegin("Diodes", "audTempDiodes", "audID", Nz(Me.audID, 0), bWasNewRecord)

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.
    '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

Err_AuditEditBegin:
    Call LogError(Err.Number, Err.Description)
    Resume Exit_AuditEditBegin
End Function


Thanks,
Brooks
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<When is the form BeforeUpdate event called?  >>

  BeforeUpdate gets fired just before a record is saved, either by an explicit call for a save, or you tried to move off the record.

<<When is the form AfterUpdate event called?>>

 After the record is actually saved.   If the save was triggered by trying to move to a new record, you have not as yet moved to the new record.

 It's important to note that Before and After update are fired both for new (inserted records and records that were already existing and simply updated.

 In addition, for new records, the AfterInsert fires after the AfterUpdate.  For a new record, here's the order of events:

BeforeInsert > BeforeUpdate > AfterUpdate > AfterInsert.

<<audID>>

 make sure you have a field in the forms underlying recordsource or control named as that.

 What you can do is put a breakpoint on that line (or a stop on the line above it) and when the code halts, hover over the reference to see if it gives you a value.

 Use F8 to step through the code line by line, or F5 to continue from that point.

Jim.
Avatar of gbnorton

ASKER

Jim,
I added a control.  That error went away.  Now I get the error 'too few parameters. Expected 1' on this:

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

It fails on db.Execute sSQL, dbFailOnError

I want to understand the statement.  
Table name: "INSERT INTO " & sAudTmpTable  
Field names in table: ( audType, audDate, audUser )
Don't understand:  "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, "

Thanks Brooks
this is the sql statement created by the preceding code:
"INSERT INTO audTempDiodes ( audType, audDate, audUser ) SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, Diodes.* FROM Diodes WHERE (Diodes.audID = 1);"
Brooks,

<<Now I get the error 'too few parameters. Expected 1' on this:>>

This means that there is an expression that Access doesn't know how to resolve and get a value for.

<<This is the sql statement created by the preceding code:
"INSERT INTO audTempDiodes ( audType, audDate, audUser ) SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, Diodes.* FROM Diodes WHERE (Diodes.audID = 1);" >>

  The statement itself is fine, but do you have a procedure call 'NetworkUserName'?

 Call up the debug window (Ctrl/G) and type:

?  Networkusername()

 and key return.  Think you'll get an error.

Jim.
The networkusername returns my network login name.  Seems to be working.

What should be the result of the "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, Diodes." ?

Thanks Brooks
Wait a second, your call is wrong.

What you should be doing is something like this:

   Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID, 0), bWasNewRecord)

Your doing this:

Call AuditEditBegin("Diodes", "audTempDiodes", "audID", Nz(Me.audID, 0), bWasNewRecord)


 "audID" is the key field in the temp audit table you created, not in your table that your creating an audit trail for.   You want the name of your key field and the value.

 Remove the control your created for AudID and change the call to AuditEditBegin() to reference your tables PK field and the field/control that has the value for that key.

Jim.
Jim,  for the next week I'll be consumed with another project.  It will be after 1/27 that I return to this....  I'll be back.
Thanks,
Brooks
I'm finally back.  My sql statement is:
"INSERT INTO audTempDiodes ( audType, audDate, audUser ) SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, Diodes.* FROM Diodes WHERE (Diodes.ID = 1);"

It fails with this error.

Run-time error 3127

The INSERT INTO statement contains the following unknown field name: ID.  Make sure you have typed the operation name correctly and try the operation again.

ID is the autonumber field in the the Diodes table.

Any ideas?
Thanks,
Brooks
What's your call to AuditEditBegin look like?

Jim.
Call AuditEditBegin("Diodes", "audTempDiodes", "ID", Nz(Me.txtID, 0), bWasNewRecord)

Diodes table contains the record I'm copying into table audTempDiodes.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You got it!  I was missing the ID field in audTempDiodes.
I've requested that this question be closed as follows:

Accepted answer: 0 points for gbnorton's comment #a39853364

for the following reason:

Thanks Jim for all your input.
Think you closed this wrong.

Jim.
oops.
Jim,
This audit app is working most excellent.   You made me look good.
Thank you,
Brooks