• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

Adapting Allen Browne's Audit Trail to Include the UserName

Hi,
I need to adapt Allen Browne's Audit Trail solution: http://allenbrowne.com/AppAudit.html
to cater for the situation where more than 1 person users the same pc, accessing the database.

In this case, in addition to the NetworkUserName(), I need to write the name of user (or UserID) who has logged-in to the audit table.  The UserName would be obtained from frmLogin.cboUser.column(1).  The UserID would be obtained from frmLogin.cboUser.column(0).

Your Assistance would be greatly appreciated.

Kind Regards,
Mohamed
0
Mohamed Singh
Asked:
Mohamed Singh
  • 2
  • 2
1 Solution
 
SimonCommented:
1. In the audit temp table definition, add a new column for the login user.
2. In each of the functions in Allen Browne's module that already refers to the networkusername,
 a) add the login user parameter:
e.g.
Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long,loginUser as string) As Boolean

 b) Add the new column to the SQL statement
e.g.
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser,loginUser ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, LoginUser as Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"

3. In each of the calls to the audit table writes, add your login user:
e.g.
 Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID,0),forms!frmlogin.cbouser.column(1))
0
 
Mohamed SinghAuthor Commented:
Hi SimonAdept,

Thank You for the quick reply.

I'm not sure if i included your modifications correctly, but i'm getting the dreaded: Too Few parameters, expected...

I've attached ajbAuditTrailModified.accdb for your review.

Many Thanks

Mohamed
ajbAuditTrailModified.accdb
0
 
SimonCommented:
Thanks for posting your example db. I can see that I fell short on the dynamic SQL statement. They should be formulated as follows:

    sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, loginUser) " & _
        "SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3,'" & loginUser & "' As Expr4," & sTable & ".* " & _
        "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"

I was forgetting to split the string to insert the value of the loginuser parameter.
ie wherever loginuser appears in the sSQL string build, replace it with '" & loginuser & "'

All appears to work now.
0
 
Mohamed SinghAuthor Commented:
Hi SimonAdept,

Thank You Kindly.

Kindest Regards,
Mohamed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now