AuditTrail not working on Sub Forms

[rebeccab]

Hiya,

I use the following module to run an audit trail on my database, which is then linked on to the form by using the following code on the BeforeUpdate property of the form. This works perfectly on the form itself - and in tabs - until I am using a subform. How can I get the module to work without having to name each subform (this form uses 3 SFs!)??

Sub PropertyAuditChanges(IDField As String)
    On Error GoTo PropertyAuditChanges_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 PropertyAudit", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    For Each ctl In Screen.ActiveForm.Controls
        If ctl.Tag = "PropertyAudit" 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
PropertyAuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
PropertyAuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume PropertyAuditChanges_Exit
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not Me.NewRecord Then Call PropertyAuditChanges("SiteCode")
End Sub

[pbaldy]

I suspect you'll have to call the function from each of the subforms. If you call from the main form's before update event, the subform's will already be updated and the OldValue property would not be available.

[rebeccab]

Thanks for your reply Paul. I have already tried calling the function on the BeforeUpdate of each subform, but I can't seem to make it work

[pbaldy]

I don't know what "can't seem to make it work" means exactly. Do you get an error? It could be that ActiveForm will always refer to the main form, so you may need to modify the function to accept the name of the form or subform, and use that instead.

By the way, opening the recordset on the entire table:

rst.Open "SELECT * FROM PropertyAudit", cnn, adOpenDynamic, adLockOptimistic

could prove to be a performance problem when the table accumulates a lot of records. You can use the append only option or:

rst.Open "SELECT * FROM PropertyAudit WHERE 1=0", cnn, adOpenDynamic, adLockOptimistic