Solved

Allen Browne's Audit Log code

Posted on 2014-01-15
16
681 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 57
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 57
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 57
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 57
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 57

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 57
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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