Audit Trail into Subform

[AdamMVRRS]

Hi Everyone,

Sorry for posting a thread that pops up a lot on the internet, but I haven't been able to find a solution in the past couple hours of searching. I'll try to explain my situation the best that I can.

I have a form with a few tabs and 3 subforms. I used the following Audit Trail http://www.fontstuff.com/Access/acctut21.htm to complete wha I have so far and it's been really great. Simple, easy to use and works well. The only issue that I have is it doesn't read the edits from the sub forms. It tells me when a new record is created or deleted but I can't see if anything was changed or what was input into the new record.

I believe it's something to do with Screen.ActiveForm but I can't be certain.

Below is the code that I use in a module and the code on the event section of the forms and the module that I use. Also all the controls I want audited have Audit in the tag of the Other menu.

BadAudit Module

Sub AuditChanges(IDField As String, UserAction 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 tblAuditTrail", 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 Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit

End Sub


Learner Details - Main Form

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("MVRRS Learner ID", "DELETE")

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("MVRRS Learner ID", "NEW")
Else
Call AuditChanges("MVRRS Learner ID", "EDIT")
End If
End Sub

The After Del and Before Up are also on the forms SuspensionsSubFrm, ReviewsSubFrm and Framework which are all subforms.


Thanks to anyone who can actually help me in this little mess!

 

[AdamMVRRS]

Bump - Could anyone help? Or am I a lost cause

 

[topdesk123]

Hi

I'm working on the exact same problem, with the same code. If you were able to figure it out, would you mind sharing?

Thank you!!
Gina