Audit Trail - SubForm

[satifali]

Hi,
Following code is working perfectly when form is used as main but when inserted as sub form it works only for main form field & doesn't record anything for sub form fields. What necessary changes i might need to do in this.

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 tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    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
                    ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

[Rabbit]

You also need to loop through the ActiveForm.[Enter subform control name here].Form.Controls array as well

[satifali]

Hi Rabbit,
I am sorry i didn't get you exactly.

[Rabbit]

You're looping through the parent form controls, you also need to loop through the subform controls by accessing it's controls per the above.

[neelsfer]

If you do not come right with Rabbit's adjustments then look at this example that i downloaded and adapt your code accordingly. I struggled with the exact same problem (and code) for days and easily managed to sort it out, using this method
In the subform adapt code like this. See example of audittable for fields where changes are saved.

Option Explicit
Const txtTableName = "yoursubformname"

'Choose a field in subform and adapt it in the "BeforeUpdate"
Private Sub InvoiceQty_BeforeUpdate(Cancel As Integer)
On Error GoTo InvoiceQty_BeforeUpdate_Err
WriteAuditUpdate txtTableName, Me.IDField, "InvoiceQty", Me.InvoiceQty.OldValue, Me.InvoiceQty.Value
InvoiceQty_BeforeUpdate_Exit:
    Exit Sub
 
InvoiceQty_BeforeUpdate_Err:
'Err.Clear
   MsgBox Error$
    Resume InvoiceQty_BeforeUpdate_Exit
End Sub

In a module i use something like this that you can adapt

Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName, OrgValue, CurValue)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("AuditTable")
 
    rs.AddNew
    rs!TableName = txtTableName
    rs!RecordPrimaryKey = lngRecordNum
    rs!FieldName = txtFieldName
   ' rs!LoginName = GetCurrentUserName
    rs!LoginName = [Forms]![Home]![capt2]
    rs!MachineName = GetComputerName
    rs!Transaction = [Forms]![frmEdittransN]![Text8]
    rs!User = CurrentUser
    rs!OriginalValue = OrgValue
    rs!NewValue = CurValue
    rs!DateTimeStamp = Now()
   rs!Code = [Forms]![frmEdittransN]![frmNewOrderAddTrans]![ICN]
    rs.Update
rs.Close
db.Close
End Sub

[NeoPa]

Something that may not be obvious, and is probably causing your confusion, is that Screen.ActiveForm returns the main form - even if focus is on the subform. If you want to get the currently active form, including subforms, then use Screen.ActiveControl.Parent, but check that it's a form object too :

If Not TypeOf Screen.ActiveControl.Parent Is Form Then Exit Sub