Adapting Allen Browne's Audit Trail to Include the UserName

I need to adapt Allen Browne's Audit Trail solution:
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 SinghAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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
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:
 Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID,0),forms!frmlogin.cbouser.column(1))
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

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohamed SinghAuthor Commented:
Hi SimonAdept,

Thank You Kindly.

Kindest Regards,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.