Solved

Allen Browne's Audit Log code

Posted on 2014-01-15
16
687 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 
LVL 58
ID: 39782461
<<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
ID: 39785417
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
ID: 39785457
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 58
ID: 39785579
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
ID: 39785946
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 58
ID: 39786493
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
ID: 39788600
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
ID: 39853236
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
 
LVL 58
ID: 39853314
What's your call to AuditEditBegin look like?

Jim.
0
 

Author Comment

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

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39853349
<<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
ID: 39853364
You got it!  I was missing the ID field in audTempDiodes.
0
 

Author Comment

by:gbnorton
ID: 39853399
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 58
ID: 39853400
Think you closed this wrong.

Jim.
0
 

Author Closing Comment

by:gbnorton
ID: 39853408
oops.
0
 

Author Comment

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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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