subform audit

[slimjen1]

GM Using Access 2010. I am using the following code to audit a form in my database which works fine:

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
                            ![ClientID] = 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
                ![ClientID] = 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
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

I also need to audit the subform that is on the form. Can anyone help me modify this code to include the subform please?

 

[Uncle Gizmo]

I believe that section of code you have posted is from one of the various methods of "auditing" your users actions in your database. There's a lot of code missing? How's it called? What events call it? Without showing these, it makes it difficult to see what you could do to record entries in a subform. I did post a sample database years ago which would record entries within a subform, it was in the "Audit Thread", a big thread in this forum with many contributions from many people. I'm sure this question has been asked and answered before, and I have an inkling you put the code in the subform, --- but!! -- it won't work the way it is set up. It will need some modification. I would check the Audit thread and repost if you don't find anything suitable.

 

[Cronk]

Jen

To loop through the controls on a sub form, you'd have something like

For Each ctl In Screen.ActiveForm![YourSubFormname].Form.Controls

The problem with this is that if you are providing the audit on multiple forms, the subform names might not be the same, if indeed the audit is done on forms which have one and only one subform.

 

[sneuberg]

The problem with this is that if you are providing the audit on multiple forms, the subform names might not be the same, if indeed the audit is done on forms which have one and only one subform.

I wonder if he couldn't detect subforms as scans the forms controls by looking at the controlType to see if it's acSubform, then he could get the name for that control and then do what you suggested. Since subform forms can have subforms I guess this should be done recursively.

 

[slimjen1]

Hi All, I''ve been on a mini vacation. Thanks for all the replies. I have the code in a module and call it in before update on both forms. The form and subform both work separately but not together.