We help IT Professionals succeed at work.

Allen Browne's Audit Log code

gbnorton
gbnorton asked
on
943 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
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.

Author

Commented:
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

Author

Commented:
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);"
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.

Author

Commented:
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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.

Author

Commented:
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

Author

Commented:
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
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
What's your call to AuditEditBegin look like?

Jim.

Author

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

Diodes table contains the record I'm copying into table audTempDiodes.
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
You got it!  I was missing the ID field in audTempDiodes.

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Think you closed this wrong.

Jim.

Author

Commented:
oops.

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.