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

Tracked user name using basAuditTrail module.

P: 2
Hi there,

I'm using this audit trail code (basAuditTrail module) to log changes in my database, but I'm needing it to track by the user name logged into Access not by the account logged into Windows. Does anybody know how to change this?

Your help would be much appreciated.
Thanks heaps.

Phil.

This is the code I'm using....it seems to be a common one found in forums.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Call AuditTrail(Me, RecordNo) 'RecordNo is the primary key for each table 
  3. End Sub
In (module) basAuditTrail:
Expand|Select|Wrap|Line Numbers
  1. Const cDQ As String = """"
  2.  
  3. Sub AuditTrail(frm As Form, recordid As Control)
  4.   'Track changes to data.
  5.   'recordid identifies the pk field's corresponding
  6.   'control in frm, in order to id record.
  7.   Dim ctl As Control
  8.   Dim varBefore As Variant
  9.   Dim varAfter As Variant
  10.   Dim strControlName As String
  11.   Dim strSQL As String
  12.   On Error GoTo ErrHandler
  13.   'Get changed values.
  14.   For Each ctl In frm.Controls
  15.     With ctl
  16.       'Avoid labels and other controls with Value property.
  17.       Select Case ctl.ControlType
  18.         Case acTextBox, acCheckBox, acComboBox
  19.           If .Value <> .OldValue Then
  20.             varBefore = .OldValue
  21.             varAfter = .Value
  22.             strControlName = .Name
  23.             'Build INSERT INTO statement.
  24.             strSQL = "INSERT INTO " _
  25.                 & "tblAudit (EditDate, User, RecordID, SourceTable, " _
  26.                 & " SourceField, BeforeValue, AfterValue) " _
  27.                 & "VALUES (Now()," _
  28.                 & cDQ & Environ("username") & cDQ & ", " _
  29.                 & cDQ & recordid.Value & cDQ & ", " _
  30.                 & cDQ & frm.RecordSource & cDQ & ", " _
  31.                 & cDQ & .Name & cDQ & ", " _
  32.                 & cDQ & varBefore & cDQ & ", " _
  33.                 & cDQ & varAfter & cDQ & ")"
  34.             'View evaluated statement in Immediate window.
  35.             Debug.Print strSQL
  36.             DoCmd.SetWarnings False
  37.             DoCmd.RunSQL strSQL
  38.             DoCmd.SetWarnings True
  39.           End If
  40.       End Select
Apr 24 '08 #1
Share this Question
Share on Google+
1 Reply


missinglinq
Expert 2.5K+
P: 3,532
CurrentUser() returns the Access user name. Try substituting for Environ("username") in your code.

Welcome to bytes!

Linq ;0)>
Apr 24 '08 #2

Post your reply

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