I'm using Martin Green's audit table code which I gleaned from the internet. Unfortunately, the code doesn't seem to track what sort of action is actually happening while I'm in one of my edit forms. The line ![Action] = UserAction does not populate the audit_trail with any actions, but rather just leaves it blank.
What can I do to track which action item was used? I'm interested in the edit and add new function.
The following code is a snippet of what I'm using:
Sub AuditChanges(IDField As String)
On Error GoTo auditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Select * from Audit_Trail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case Edit
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = Audit Then
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
'Debug.Print ctl.Name
'Debug.Print ctl.Value
'Debug.Print ctl.OldValue
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction -------------------line that doesn't produce anything.
CementCarver