Audit on subform

[deadtrees]

I found some code that I'm using as an audit trail, but it only works on the main form, it'd like to use it on the sub forms and I'm thinking that I could set the active form to whatever subform has focus and set the field that holds the audits to formname.field. Can someone help me with that?

thanks

Public Function AuditData()
' Comments   : This function is used to provide an audit trail of all changes to a record
'        : entered on a secured form.  After adding a locked memo field called 'Updates'
'        : on the source form, add a reference to the Before Update event to this function.
' Requirement: A bound memo field named 'Updates' must exist on the active form
' Parameters : None
' Returns    : Appends Audit Trail data as a string to the memo field on the active form
' --------------------------------------------------------
Dim frmActive As Form
Dim ctlData As Control
Dim strEntry As String
On Err GoTo NextCtl
    Set frmActive = Screen.ActiveForm
    
    'Determine who, when and where
    strEntry = " by " & Environ("Username") & " on " & Now
    
    ' If new record, record it in audit trail and exit sub.
    If frmActive.NewRecord = True Then
       frmActive!Updates = frmActive!Updates & "New Record Added" & strEntry
       'Uncomment the next line if you do not want to record the details of the initial entry
       'Exit Function
    End If
    
    'Check each data entry control for change and record
    For Each ctlData In frmActive.Controls
       ' Only check data entry type controls.
       Select Case ctlData.ControlType
          Case acTextBox, acComboBox, acCheckBox, acOptionButton
             ' Skip Updates field.
            If ctlData.Name = "Updates" Then GoTo NextCtl
            
            'Skip unbound controls (3 is ControlSource)
            If ctlData.Properties(3) = "" Then GoTo NextCtl
             
             Select Case IsNull(ctlData.Value)
                'Check for deleted data
                Case True
                    If Not IsNull(ctlData.OldValue) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Deleted: Old value was '" & ctlData.OldValue & "'" & strEntry
                    End If
                'Check for new or changed data
                Case False
                    If IsNull(ctlData.OldValue) And Not IsNull(ctlData.Value) Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " Data Added: " & ctlData.Value & strEntry
                    'If control had previous value, record previous value.
                    ElseIf ctlData.Value <> ctlData.OldValue Then
                        frmActive!Updates = frmActive!Updates & Chr(13) & Chr(10) & "  " & ctlData.Name & " changed from '" & ctlData.OldValue & "' to '" & ctlData.Value & "'" & strEntry
                    End If
            End Select
   End Select
NextCtl:
   Next ctlData
End Function

 

[gemma-the-husky]

i think a subform is just a control ON a main form

so this bit

Select Case ctlData.ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionButton

is ignoring the subform CONTROL, because it is not one of these types.

you may be able to change the code to respond correctly to the correct controltype, or you may need to rewrite the audit trail, and actually PASS it the data which you want to be audited, rather than let it read it off the active form.

hope this makes sense

[edited
i assume you call this in the forms before update event?]

 

[deadtrees]

You are correct I am calling AuditData() from the BeforeUpdate event on the main form.

I added acSubForm to the case, but nothing happens, does the syntax need to be more specific to actually name the controls?

I read the audit trail thread on this forum where someone mentions passing the form data and I tried mimicing the code, but it didn't work for me. My VBA skills are novice at best. Any good resources on where I could learn that?

thanks

 

[gemma-the-husky]

assuming that the subforms control type is indeed acsubform - now you have the problem of disentangling what has changed in the subform itself - your code is just checking each controls value against the oldvalue - but this method certainly will not work for a subform.

but i'm really not sure how to do it for a subform - try calling it from the subform's before update event, and see what happens - I don't thing you can use the mainform's update event, as the subform's changes are saved on entering the mainform - and therefore there will be no valid "oldvalues" for any of the subform fields -

i actually pass my audit text to a function, that stores the changes details in a central table, so I don't use the activeform in this way at all