Tracked user name using basAuditTrail module.

[suckerpunchnz]

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.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, RecordNo) 'RecordNo is the primary key for each table 
End Sub

In (module) basAuditTrail:

Const cDQ As String = """"
 
Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
      'Avoid labels and other controls with Value property.
      Select Case ctl.ControlType
        Case acTextBox, acCheckBox, acComboBox
          If .Value <> .OldValue Then
            varBefore = .OldValue
            varAfter = .Value
            strControlName = .Name
            'Build INSERT INTO statement.
            strSQL = "INSERT INTO " _
                & "tblAudit (EditDate, User, RecordID, SourceTable, " _
                & " SourceField, BeforeValue, AfterValue) " _
                & "VALUES (Now()," _
                & cDQ & Environ("username") & cDQ & ", " _
                & cDQ & recordid.Value & cDQ & ", " _
                & cDQ & frm.RecordSource & cDQ & ", " _
                & cDQ & .Name & cDQ & ", " _
                & cDQ & varBefore & cDQ & ", " _
                & cDQ & varAfter & cDQ & ")"
            'View evaluated statement in Immediate window.
            Debug.Print strSQL
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
          End If
      End Select

[missinglinq]

CurrentUser() returns the Access user name. Try substituting for Environ("username") in your code.

Welcome to bytes!

Linq ;0)>