By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

Allen Browne's audit trail - change NetworkUsername() function

P: 12
Hi,
My knowledge in Access is very limited but I have managed to build a pretty good-wroking database for my department based on help and assistance of experts from forums like this one.

A bit of background: This database will be used on a common computer, shared amongst the team members. Hence, I've created a login screen for users to login before using the database. Below is the code from my login form:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Public strUserName As String
  4.  
  5. Private Sub Form_Load()
  6.     Me.txtUsername.SetFocus
  7. End Sub
  8.  
  9. Private Sub txtUsername_AfterUpdate()
  10. 'After selecting user name set focus to password field
  11. Me.txtPassword.SetFocus
  12. End Sub
  13.  
  14. Private Sub cmdLogin_Click()
  15.  
  16. Dim rs As Recordset
  17. Dim strSQL As String
  18. Dim strPassword As String
  19. Dim strUserName As String
  20.  
  21. On Error Resume Next
  22.  
  23. strUserName = txtUsername.Value
  24.  
  25. strPassword = txtPassword.Value
  26.  
  27.     Set db = CurrentDb
  28.     strSQL = "SELECT password FROM tblUser WHERE username='" & strUserName & "'"
  29.     Set rs = db.OpenRecordset(strSQL)
  30.     If rs.RecordCount > 0 Then
  31.         If rs.Fields(0) <> strPassword Then
  32.             MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
  33.         Else
  34.             Me.Visible = False
  35.             DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acDialog
  36.             Me.txtUsername.Value = ""
  37.             Me.txtPassword.Value = ""
  38.         End If
  39.     Else
  40.         MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
  41.         txtUsername.SetFocus
  42.     End If
  43.  
  44. End Sub
  45.  
At the same time, I would like to create an audit trail for any changes/updates done on the database. I have used the Allen Browne's audit trail sample code and changed it to fit mine - which worked well; except, it is returning the Windows login code of the Computer itself, not the user login. Here's the code for my audit trail module:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conMod As String = "ajbAudit"
  5. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  6.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7.  
  8.  
  9. Function NetworkUserName() As String
  10. On Error GoTo Err_Handler
  11.     'Purpose:    Returns the network login name
  12.     Dim lngLen As Long
  13.     Dim lngX As Long
  14.     Dim strUserName As String
  15.  
  16.     NetworkUserName = "Unknown"
  17.  
  18.     strUserName = String$(254, 0)
  19.     lngLen = 255&
  20.     lngX = apiGetUserName(strUserName, lngLen)
  21.     If (lngX > 0&) Then
  22.         NetworkUserName = Left$(strUserName, lngLen - 1&)
  23.     End If
  24.  
  25. Exit_Handler:
  26.     Exit Function
  27.  
  28. Err_Handler:
  29.  
  30. Resume Exit_Handler
  31. End Function
  32.  
  33.  
  34. Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
  35. On Error GoTo Err_AuditDelBegin
  36.     'Purpose:    Write a copy of the record to a tmp audit table.
  37.     '            Copy to be written to real audit table in AfterDelConfirm.
  38.     'Arguments:  sTable = name of table to be audited.
  39.     '            sAudTmpTable = the name of the temp audit table.
  40.     '            sKeyField = name of AutoNumber field in table.
  41.     '            lngKeyValue = number in the AutoNumber field.
  42.     'Return:     True if successful.
  43.     'Usage:      Call from a form's Delete event. Example:
  44.     '                Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
  45.     'Note:       Must also call AuditDelEnd in the form's AfterDelConfirm event.
  46.     Dim db As DAO.Database           ' Current database
  47.     Dim sSQL As String               ' Append query.
  48.  
  49.     ' Append record to the temp audit table.
  50.     Set db = DBEngine(0)(0)
  51.     sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  52.         "SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
  53.         "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  54.     db.Execute sSQL, dbFailOnError
  55.  
  56. Exit_AuditDelBegin:
  57.     Set db = Nothing
  58.     Exit Function
  59.  
  60. Err_AuditDelBegin:
  61.  
  62. Resume Exit_AuditDelBegin
  63. End Function
  64.  
  65.  
  66. Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
  67. On Error GoTo Err_AuditDelEnd
  68.     'Purpose:    If the deletion was completed, copy the data from the
  69.     '                temp table to the autit table. Empty temp table.
  70.     'Arguments:  sAudTmpTable = name of temp audit table
  71.     '            sAudTable = name of audit table
  72.     '            Status = Status from the form's AfterDelConfirm event.
  73.     'Return:     True if successful.
  74.     'Usage:      Call from form's AfterDelConfirm event. Example:
  75.     '                Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
  76.     Dim db As DAO.Database           ' Currrent database
  77.     Dim sSQL As String               ' Append query.
  78.  
  79.     ' If the Delete proceeded, copy the record(s) from temp table to delete table.
  80.     ' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
  81.     Set db = DBEngine(0)(0)
  82.     If Status = acDeleteOK Then
  83.         sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
  84.             " WHERE (" & sAudTmpTable & ".audType = 'Delete');"
  85.         db.Execute sSQL, dbFailOnError
  86.     End If
  87.  
  88.     'Remove the temp record(s).
  89.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  90.     db.Execute sSQL, dbFailOnError
  91.     AuditDelEnd = True
  92.  
  93. Exit_AuditDelEnd:
  94.     Set db = Nothing
  95.     Exit Function
  96.  
  97. Err_AuditDelEnd:
  98.  
  99. Resume Exit_AuditDelEnd
  100. End Function
  101.  
  102.  
  103. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  104.     lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  105. On Error GoTo Err_AuditEditBegin
  106.     'Purpose:    Write a copy of the old values to temp table.
  107.     '            It is then copied to the true audit table in AuditEditEnd.
  108.     'Arugments:  sTable = name of table being audited.
  109.     '            sAudTmpTable = name of the temp audit table.
  110.     '            sKeyField = name of the AutoNumber field.
  111.     '            lngKeyValue = Value of the AutoNumber field.
  112.     '            bWasNewRecord = True if this was a new insert.
  113.     'Return:     True if successful
  114.     'Usage:      Called in form's BeforeUpdate event. Example:
  115.     '                bWasNewRecord = Me.NewRecord
  116.     '                Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
  117.     Dim db As DAO.Database           ' Current database
  118.     Dim sSQL As String
  119.  
  120.     'Remove any cancelled update still in the tmp table.
  121.     Set db = DBEngine(0)(0)
  122.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  123.     db.Execute sSQL
  124.  
  125.     ' If this was not a new record, save the old values.
  126.     If Not bWasNewRecord Then
  127.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  128.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
  129.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  130.         db.Execute sSQL, dbFailOnError
  131.     End If
  132.     AuditEditBegin = True
  133.  
  134. Exit_AuditEditBegin:
  135.     Set db = Nothing
  136.     Exit Function
  137.  
  138. Err_AuditEditBegin:
  139.  
  140. Resume Exit_AuditEditBegin
  141. End Function
  142.  
  143.  
  144. Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
  145.     sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  146. On Error GoTo Err_AuditEditEnd
  147.     'Purpose:    Write the audit trail to the audit table.
  148.     'Arguments:  sTable = name of table being audited.
  149.     '            sAudTmpTable = name of the temp audit table.
  150.     '            sAudTable = name of the audit table.
  151.     '            sKeyField = name of the AutoNumber field.
  152.     '            lngKeyValue = Value of the AutoNumber field.
  153.     '            bWasNewRecord = True if this was a new insert.
  154.     'Return:     True if successful
  155.     'Usage:      Called in form's AfterUpdate event. Example:
  156.     '                Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
  157.     Dim db As DAO.Database
  158.     Dim sSQL As String
  159.     Set db = DBEngine(0)(0)
  160.  
  161.     If bWasNewRecord Then
  162.         ' Copy the new values as "Insert".
  163.         sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
  164.             "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
  165.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  166.         db.Execute sSQL, dbFailOnError
  167.     Else
  168.         ' Copy the latest edit from temp table as "EditFrom".
  169.         sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
  170.             " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
  171.         db.Execute sSQL
  172.         ' Copy the new values as "EditTo"
  173.         sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
  174.             "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
  175.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  176.         db.Execute sSQL
  177.         ' Empty the temp table.
  178.         sSQL = "DELETE FROM " & sAudTmpTable & ";"
  179.         db.Execute sSQL, dbFailOnError
  180.     End If
  181.     AuditEditEnd = True
  182.  
  183. Exit_AuditEditEnd:
  184.     Set db = Nothing
  185.     Exit Function
  186.  
  187. Err_AuditEditEnd:
  188.  
  189. Resume Exit_AuditEditEnd
  190. End Function
  191.  
So my question is:
How do I tweak the codes above so that my audit trail will display the user login name (entered upon entering the database) instead of the Computer's windows login?

Your assistance is highly appreciated. Thanks!
Nov 28 '13 #1

✓ answered by zmbd

Lets try this:

In the AuditDelBegin() function:
There is a line (#23?) in Neopa/AB's code that currently reads:
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"


Change this to read:
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"


NOTE THE HIGHLIGHTED SECTION!
The spaceing here is VERY importaint - missing a space will result in an error.

Also note the SINGLE QUOTE marks around the TempVars!UserName just inside the double quotes on each side.

If You used "TestUserName" as the value in TempVars!UserName then debug.print sSQL the resolved string will be:
INSERT INTO audtmptblpuke1 ( audType, audDate, audUser ) SELECT 'Delete' AS Expr1, Now() AS Expr2, 'TestUserName', tblpuke1.* FROM tblpuke1 WHERE (tblpuke1.id = 1);
(ok, tblpuke1... don't ask... just say I have 4 kids, three under 5 and it was a very long and very sick day when I made this database to play with (@_@) )

This should now work... it worked in my test database.

You will need to make the same changes to the remaining functions.

BOL

-Z

[edit] This is the same code as in the section above, I've just made it easier to cut and paste and added the debug.print
Expand|Select|Wrap|Line Numbers
  1. sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  2.         "SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
  3.         "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  4. '
  5. Debug.Print sSQL ''<<< remove this line after troubleshooting is done!
  6. '

Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,492
A properly formatted question from a new user. How refreshing.

I'd start by storing the name of the validated user in a session-level variable. Recent versions of Access have these, and they persist beyond the point at which the project crashes or stops.

From there, it's a simple case of changing any references in your logging procedures to reference that variable instead of using NetworkUserName().

Let us know if you need more help implementing this. I'm rushing at the moment, but will have more time when next I look.

Welcome to Bytes :-)
Nov 28 '13 #2

P: 12
Hi NeoPa,

Thanks for the warm welcome. I've read through dozens of threads in Bytes and somehow learned how's best to approach the forum :)

Anyways, you guessed right. I do need further help on this. I dont quite understand by what you meant with "session-level variable".

My guess is I would have to change Line 4-31 accordingly from the audit trail module; but I have no idea how to do this.

I'll be patient and wait till you have some time to reply.

Thanks!
Nov 28 '13 #3

zmbd
Expert Mod 5K+
P: 5,397
I think that Neopa is refering to the newer "temporary variable" collection that is available in ACC2007/2010.

This link has a VERY basic explanation and example of useage:
Power Tip: Maximize the use of TempVars in Access 2007 and 2010

The cool thing here is that you can reference these from the stored queries without a function call!

Neopa will be along shortly to correct me if I'm off...

If you need still more help let us know.
Nov 28 '13 #4

NeoPa
Expert Mod 15k+
P: 31,492
Nothing to correct (of course). That's what I was talking about.

I use another approach for 2003 and earlier, but that's more complicated and unnecessary if you have TempVars available ;-)
Nov 28 '13 #5

P: 12
Hi all,

I've read through the article suggested by zmbd - as much as I am excited at how useful it can be, I'm beyond clueless at this point. To be frank, I have no training in Access at all- just learned it briefly during my uni days and the rest are through the web!

Appreciate if you can provide a detailed guide/sample on how I can write the code to fulfil my requirements - to capture the current user name instead of the Windows login name.

Thanks guys
Nov 29 '13 #6

zmbd
Expert Mod 5K+
P: 5,397
Bellina:

Using the method shown in the article to set a memeber of the tempvars collection. When the user enters the information in your login/on form, after validating the user's entries, store the user name in the tempvars.
TempVars.Add Name:="LoggedInUser", Value:=strTheUsersNameFromTheForm
Here I've explicitly set the agruments by name and the "strTheUsersNameFromTheForm" would be from the form's control

Now whenever you need the user name you refer to it using one of the three methods shown in the article keeping in mind that all three though valid within VBA (Macro uses a slightly different method) in stored queries only the TempVars!LoggedInUser would be used. Also if you set three of these and want to use the TempVars.Item(0) format, the "0" refers to the first one you set and "2" would be the third (think arrays or listbox referencing)

As we're getting ready for the big family feed I've not a lot if time this weekend.
Nov 29 '13 #7

NeoPa
Expert Mod 15k+
P: 31,492
Your original checking code could be altered to :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public strUserName As String
  3.  
  4. Private Sub Form_Load()
  5.     Me.txtUsername.SetFocus
  6. End Sub
  7.  
  8. Private Sub txtUsername_AfterUpdate()
  9.     'After selecting user name set focus to password field
  10.     Me.txtPassword.SetFocus
  11. End Sub
  12.  
  13. Private Sub cmdLogin_Click()
  14.     Dim rs As Recordset
  15.     Dim strSQL As String
  16.  
  17.     On Error Resume Next
  18.     With Me
  19.         Set db = CurrentDb()
  20.         strSQL = "SELECT [password] FROM [tblUser] WHERE [username]='" & strUserName & "'"
  21.         Set rs = db.OpenRecordset(strSQL)
  22.         If rs.RecordCount > 0 Then
  23.             If rs.Fields(0) <> .txtUserName Then
  24.                 MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
  25.             Else
  26.                 .Visible = False
  27.                 Call TempVars.Add(Name:="UserName", Value:=CStr(.txtUserName))
  28.                 .txtUsername = Null
  29.                 .txtPassword = Null
  30.                 DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acDialog
  31.             End If
  32.         Else
  33.             MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
  34.             txtUsername.SetFocus
  35.         End If
  36.     End With
  37. End Sub
Then, when you want to use it (in a way similar to your earlier code) use :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conMod As String = "ajbAudit"
  5.  
  6. Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
  7.     On Error GoTo Err_AuditDelBegin
  8.     'Purpose:    Write a copy of the record to a tmp audit table.
  9.     '            Copy to be written to real audit table in AfterDelConfirm.
  10.     'Arguments:  sTable = name of table to be audited.
  11.     '            sAudTmpTable = the name of the temp audit table.
  12.     '            sKeyField = name of AutoNumber field in table.
  13.     '            lngKeyValue = number in the AutoNumber field.
  14.     'Return:     True if successful.
  15.     'Usage:      Call from a form's Delete event. Example:
  16.     '                Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
  17.     'Note:       Must also call AuditDelEnd in the form's AfterDelConfirm event.
  18.     Dim db As DAO.Database           ' Current database
  19.     Dim sSQL As String               ' Append query.
  20.  
  21.     ' Append record to the temp audit table.
  22.     Set db = DBEngine(0)(0)
  23.     sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  24.         "SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
  25.         "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  26.     db.Execute sSQL, dbFailOnError
  27.  
  28. Exit_AuditDelBegin:
  29.     Set db = Nothing
  30.     Exit Function
  31.  
  32. Err_AuditDelBegin:
  33.     Resume Exit_AuditDelBegin
  34. End Function
  35.  
  36. Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
  37.     On Error GoTo Err_AuditDelEnd
  38.     'Purpose:    If the deletion was completed, copy the data from the
  39.     '                temp table to the autit table. Empty temp table.
  40.     'Arguments:  sAudTmpTable = name of temp audit table
  41.     '            sAudTable = name of audit table
  42.     '            Status = Status from the form's AfterDelConfirm event.
  43.     'Return:     True if successful.
  44.     'Usage:      Call from form's AfterDelConfirm event. Example:
  45.     '                Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
  46.     Dim db As DAO.Database           ' Currrent database
  47.     Dim sSQL As String               ' Append query.
  48.  
  49.     ' If the Delete proceeded, copy the record(s) from temp table to delete table.
  50.     ' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
  51.     Set db = DBEngine(0)(0)
  52.     If Status = acDeleteOK Then
  53.         sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
  54.             " WHERE (" & sAudTmpTable & ".audType = 'Delete');"
  55.         db.Execute sSQL, dbFailOnError
  56.     End If
  57.  
  58.     'Remove the temp record(s).
  59.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  60.     db.Execute sSQL, dbFailOnError
  61.     AuditDelEnd = True
  62.  
  63. Exit_AuditDelEnd:
  64.     Set db = Nothing
  65.     Exit Function
  66.  
  67. Err_AuditDelEnd:
  68.     Resume Exit_AuditDelEnd
  69. End Function
  70.  
  71. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  72.                         lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  73.     On Error GoTo Err_AuditEditBegin
  74.     'Purpose:    Write a copy of the old values to temp table.
  75.     '            It is then copied to the true audit table in AuditEditEnd.
  76.     'Arugments:  sTable = name of table being audited.
  77.     '            sAudTmpTable = name of the temp audit table.
  78.     '            sKeyField = name of the AutoNumber field.
  79.     '            lngKeyValue = Value of the AutoNumber field.
  80.     '            bWasNewRecord = True if this was a new insert.
  81.     'Return:     True if successful
  82.     'Usage:      Called in form's BeforeUpdate event. Example:
  83.     '                bWasNewRecord = Me.NewRecord
  84.     '                Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
  85.     Dim db As DAO.Database           ' Current database
  86.     Dim sSQL As String
  87.  
  88.     'Remove any cancelled update still in the tmp table.
  89.     Set db = DBEngine(0)(0)
  90.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  91.     db.Execute sSQL
  92.  
  93.     ' If this was not a new record, save the old values.
  94.     If Not bWasNewRecord Then
  95.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  96.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
  97.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  98.         db.Execute sSQL, dbFailOnError
  99.     End If
  100.     AuditEditBegin = True
  101.  
  102. Exit_AuditEditBegin:
  103.     Set db = Nothing
  104.     Exit Function
  105.  
  106. Err_AuditEditBegin:
  107.     Resume Exit_AuditEditBegin
  108. End Function
  109.  
  110. Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
  111.                       sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  112.     On Error GoTo Err_AuditEditEnd
  113.     'Purpose:    Write the audit trail to the audit table.
  114.     'Arguments:  sTable = name of table being audited.
  115.     '            sAudTmpTable = name of the temp audit table.
  116.     '            sAudTable = name of the audit table.
  117.     '            sKeyField = name of the AutoNumber field.
  118.     '            lngKeyValue = Value of the AutoNumber field.
  119.     '            bWasNewRecord = True if this was a new insert.
  120.     'Return:     True if successful
  121.     'Usage:      Called in form's AfterUpdate event. Example:
  122.     '                Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
  123.     Dim db As DAO.Database
  124.     Dim sSQL As String
  125.     Set db = DBEngine(0)(0)
  126.  
  127.     If bWasNewRecord Then
  128.         ' Copy the new values as "Insert".
  129.         sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
  130.             "SELECT 'Insert' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
  131.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  132.         db.Execute sSQL, dbFailOnError
  133.     Else
  134.         ' Copy the latest edit from temp table as "EditFrom".
  135.         sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
  136.             " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
  137.         db.Execute sSQL
  138.         ' Copy the new values as "EditTo"
  139.         sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
  140.             "SELECT 'EditTo' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
  141.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  142.         db.Execute sSQL
  143.         ' Empty the temp table.
  144.         sSQL = "DELETE FROM " & sAudTmpTable & ";"
  145.         db.Execute sSQL, dbFailOnError
  146.     End If
  147.     AuditEditEnd = True
  148.  
  149. Exit_AuditEditEnd:
  150.     Set db = Nothing
  151.     Exit Function
  152.  
  153. Err_AuditEditEnd:
  154.     Resume Exit_AuditEditEnd
  155. End Function
We are no longer using the procedure NetworkUserName(), so that can be excluded from your code.
Nov 30 '13 #8

P: 12
Thank you zmbd & NeoPa!

I've done the changes as you have suggested. However, the audit trail did not work. I then tried including ' to quote the TempVars in the audit module for all the different functions as such:

Expand|Select|Wrap|Line Numbers
  1. ..... 'TempVars!UserName' AS Expr3....
The audit trail worked, BUT, it recorded "TempVars!UserName" in the audit table as the current user.

Somehow, the tempvars is not capturing the user login from the login form. Any reason why?

Btw, happy thanksgiving guys. For me, I'm thankful for the hope in humanity shown through the helpful people around the world like you guys :)
Dec 2 '13 #9

zmbd
Expert Mod 5K+
P: 5,397
Do not Quote the Tempvars.
They should be "outside" of the quotes.
"some text here and then " & tempvars!username
etc...
Dec 2 '13 #10

NeoPa
Expert Mod 15k+
P: 31,492
Which version of Access are you using?
Dec 2 '13 #11

P: 12
zmbd, did that but then the audit trail did not work. :(

NeoPa, I'm using Access2013. Sorry, I should probably have mentioned this much earlier.
Dec 3 '13 #12

zmbd
Expert Mod 5K+
P: 5,397
Let's go back to your original code in post#1 and try something very simple and see if we can get the simpler code to work:

Change Line#23 from the original:
strUserName = txtUsername.Value

so that it now reads: tempvars.add "strUserName", txtUsername.Value

Now to use the stored user name in the remaining code let's take a look at line#28, which is currently:
strSQL = "SELECT password FROM tblUser WHERE username='" & strUserName & "'"

So change it to this:
strSQL = "SELECT password FROM tblUser WHERE username='" & tempvars.item(0) & "'"
note that here I'm using the numerical index... this is to avoid the issues with the multiple quotes that run into things like """"" and ""'"'"" which are a real treat to troubleshoot.

So if the user name was "zmbd" then if you run the code the strSQL would debug.print out to be:
SELECT password FROM tblUser WHERE username='zmbd'

New here's the neat thing:
Run the code... if everything runs, no errors, then do the following:
<ctrl><g>
this will open the immediates window in the VBE
in this window type the following:

?tempvars!strUserName press return
it should return the last user name entered in the login form

?tempvars.item("strUserName") press return
it should return the last user name in the login form

tempvars.removeallpress return
Now both:
?tempvars!strUserName press return
?tempvars.item("strUserName") press return
should return a null

Once we can handle the more straight forward use, then we'll dive into the audit code.
Dec 3 '13 #13

NeoPa
Expert Mod 15k+
P: 31,492
It should be fine as long as it's after version 2003. They were introduced in V2007.

I doubt the problem is with the TempVars. It's hard to say exactly what the problem is without direct access to it, or at least a clear explanation of exactly what is going wrong where.

Did you try the code as I posted it? Exactly what went wrong when you did? I'm afraid we need a more detailed answer than simply "the auditing".

PS. I missed Z's post earlier (Page loaded ready much earlier). You should follow his straightforward instructions to provide info that will be very helpful and illuminative.
Dec 4 '13 #14

P: 12
Hi zmbd, NeoPa,

I followed zmbd easy step-by-step instructions and now, my code for the login form looks like this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public strUserName As String
  3.  
  4. Private Sub Form_Load()
  5.     Me.txtUsername.SetFocus
  6. End Sub
  7.  
  8. Private Sub txtUsername_AfterUpdate()
  9. 'After selecting user name set focus to password field
  10. Me.txtPassword.SetFocus
  11. End Sub
  12.  
  13. Private Sub cmdLogin_Click()
  14.  
  15. Dim rs As Recordset
  16. Dim strSQL As String
  17. Dim strPassword As String
  18. Dim strUserName As String
  19.  
  20. On Error Resume Next
  21.  
  22. TempVars.Add "strUserName", txtUsername.Value
  23.  
  24. strPassword = txtPassword.Value
  25.  
  26.     Set db = CurrentDb
  27.     strSQL = "SELECT password FROM tblUser WHERE username='" & TempVars.Item(0) & "'"
  28.     Set rs = db.OpenRecordset(strSQL)
  29.     If rs.RecordCount > 0 Then
  30.         If rs.Fields(0) <> strPassword Then
  31.             MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
  32.         Else
  33.             Me.Visible = False
  34.             Call TempVars.Add(Name:="UserName", Value:=strUserName)
  35.             DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acNormal
  36.  
  37.         End If
  38.     Else
  39.         MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
  40.         txtUsername.SetFocus
  41.     End If
  42.  
  43. Me.txtUsername = Null
  44. Me.txtPassword = Null
  45.  
  46. End Sub
  47.  
  48.  
After doing the checking on the immediate window, it did show the correct username accordingly.

Now, on the audit module- I've copied & pasted the code from NeoPa's earlier thread. When I tried to amend the information in my database, the audit trail is not recorded in the audit table, which worked fine earlier using the NetworkUserName.

I wish I can attach my database here for you to have a look at it. But I noticed that .accdb is not an accepted attachment file format here.
Dec 4 '13 #15

zmbd
Expert Mod 5K+
P: 5,397
If we get to the point where we need your database you will need to compress it. There are several free/shareware file compressors on the net and WindowsXP, Windows7 have native support so never fear will walk you thru it when the time comes.

I'm in the morning routine so once I have a few free moments we'll take a closer look at the other code.
Dec 4 '13 #16

zmbd
Expert Mod 5K+
P: 5,397
When you were using the AllenBrowne code, in the event calling the functions, you are using something along these lines:
Expand|Select|Wrap|Line Numbers
  1. Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID,0))
?
Dec 4 '13 #17

zmbd
Expert Mod 5K+
P: 5,397
Lets try this:

In the AuditDelBegin() function:
There is a line (#23?) in Neopa/AB's code that currently reads:
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"


Change this to read:
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"


NOTE THE HIGHLIGHTED SECTION!
The spaceing here is VERY importaint - missing a space will result in an error.

Also note the SINGLE QUOTE marks around the TempVars!UserName just inside the double quotes on each side.

If You used "TestUserName" as the value in TempVars!UserName then debug.print sSQL the resolved string will be:
INSERT INTO audtmptblpuke1 ( audType, audDate, audUser ) SELECT 'Delete' AS Expr1, Now() AS Expr2, 'TestUserName', tblpuke1.* FROM tblpuke1 WHERE (tblpuke1.id = 1);
(ok, tblpuke1... don't ask... just say I have 4 kids, three under 5 and it was a very long and very sick day when I made this database to play with (@_@) )

This should now work... it worked in my test database.

You will need to make the same changes to the remaining functions.

BOL

-Z

[edit] This is the same code as in the section above, I've just made it easier to cut and paste and added the debug.print
Expand|Select|Wrap|Line Numbers
  1. sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
  2.         "SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
  3.         "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  4. '
  5. Debug.Print sSQL ''<<< remove this line after troubleshooting is done!
  6. '
Dec 4 '13 #18

P: 12
Hi zmbd,

"tablepuke"? I understand. I have 2 kids of my own too.

Anyways, AMAZING! It worked! Although, I did face some issues at first ("Editfrom" did not show in audTable etc). But managed to check everything in detail and repaired the code accordingly.

Thank you guys for an amazing help!
Dec 10 '13 #19

P: 17
I haven't read through all posts, and there probably is a good reason you don't use the built-in CurrentUser variable in vba, or [CurrentUser] in queries. Maybe you are not using the Access security to login, but those who are, can probably use this variable instead?

CurrentUser Function
Dec 10 '13 #20

zmbd
Expert Mod 5K+
P: 5,397
PPelle
Very simple reasons... it can be hacked and it only returns the currently logged in person to the database which in ACCDB file will always be "admin" as user level security is disabled - If you have ACC2007/2010, try it...open a new database, or any database using the new ACCDB extension, <ctrl><g> then
Expand|Select|Wrap|Line Numbers
  1. ?currentuser
(^-^)
Dec 10 '13 #21

Post your reply

Sign in to post your reply or Sign up for a free account.