Small issued with audit trail

[Asspin]

Ok, so I have an audit trail setup in my form, it is working fine, unless I change my rep dropdown to choose another user. Then it records the new user, instead of the one that was changed. I am sure this is a simple fix but I am drawing a blank!

I am thinking I need to change 'sName' which is the field with the dropdown that I am changing to something else, though I am not sure what!

Before Update for the forum.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call AuditTrail(Me, sName)
End Sub

Audit Trail Module

Option Compare Database

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, acComboBox, acCheckBox, acOptionGroup
        If .Value <> .OldValue Then
          varBefore = .OldValue
          varAfter = .Value
          strControlName = .Name
          'Build INSERT INTO statement.
          strSQL = "INSERT INTO " _
             & "Audit (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
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

Dan

 

[Asspin]

Oh and the field sName is an unbound combo box with a row source of the following:

SELECT tData.sName FROM tData ORDER BY tData.sName;

Dan

 

[MichaelRed]

.selected for combobox?


MichaelRed