Solved

Allen Browne's Audit Log code

Posted on 2014-01-15
16
651 Views
Last Modified: 2014-02-12
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
0
Comment
Question by:gbnorton
  • 10
  • 6
16 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.
0
 

Author Comment

by:gbnorton
Comment Utility
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
0
 

Author Comment

by:gbnorton
Comment Utility
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);"
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 

Author Comment

by:gbnorton
Comment Utility
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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 

Author Comment

by:gbnorton
Comment Utility
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
0
 

Author Comment

by:gbnorton
Comment Utility
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
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
What's your call to AuditEditBegin look like?

Jim.
0
 

Author Comment

by:gbnorton
Comment Utility
Call AuditEditBegin("Diodes", "audTempDiodes", "ID", Nz(Me.txtID, 0), bWasNewRecord)

Diodes table contains the record I'm copying into table audTempDiodes.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<audTempDiodes>>

 Does this contain the three fields audType, audDate, and audUser along with all the fields from table Diodes including ID?

Jim.
0
 

Author Comment

by:gbnorton
Comment Utility
You got it!  I was missing the ID field in audTempDiodes.
0
 

Author Comment

by:gbnorton
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Think you closed this wrong.

Jim.
0
 

Author Closing Comment

by:gbnorton
Comment Utility
oops.
0
 

Author Comment

by:gbnorton
Comment Utility
Jim,
This audit app is working most excellent.   You made me look good.
Thank you,
Brooks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now