error audit form

[slimjen1]

All, I am using access 2010. I am using code for Audit Changes as follows:

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.ActiveControl.Form
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.ActiveControl.Form.Name
'                           ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.ActiveControl.Form(IDField).Value
'                           ![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.ActiveControl.Form.Name
'               ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.ActiveControl.Form(IDField).Value
'              ![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
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

This code was working. But now; it gives me an error:

"you entered an expression that has an invalid reference to the property form/report"

The lines it's throwing the error is:

For Each ctl In Screen.ActiveForm.ActiveControl.Form

and

![RecordID] = Screen.ActiveForm.ActiveControl.Form(IDField).Value

I tried

![RecordID] = Screen.ActiveForm.Controls(IDField).Value

I get the error either way.
I haven't made any changes to the form. The form does contain a subform. Can someone give me some insight on this.

 

[Cronk]

When you use
Screen.ActiveForm.ActiveControl.Form
you are assuming the Active control is a sub form. You would get an error if the control was anything else.

Incidentally, you know that you are putting a value in !FormName and then immediately writing over it with another value.

Same with !RecordID

 

[slimjen1]

Ahh. I tweaked the code a little because it was not tracking the subform. Changed:

Screen.ActiveForm.Controls

to

Screen.ActiveForm.ActiveControl.Form

I would have to go back to the original code but How can I audit both main form and subform?

thanks for assisting.

 

[Cronk]

Break it into 2 parts - do the parent form first, then the subform

First loop
For Each ctl In Screen.ActiveForm

Second loop
For Each ctl In Screen.ActiveForm!YourSubformnameControlname.Form

 

[slimjen1]

O. Ok thanks for the assist