vba Problems between Screen.MainForm.Controls and Screen.SubForm.Controls

[Susy]

Hi guys,



need your knowledge again, if possible.

Based on http://www.fontstuff.com/access/acctut21.htm I have been trying to get changes not only in the main form (as in code) but to get changes from the subforms as well...

1. Original code to retrieve changes only on the mainform

For Each ctl In Screen.ActiveForm.Controls
.
.
.
![FormName] = Screen.ActiveForm.Name

2. But if I change the code a "little" I can now record changes in the subforms but not from the mainform... plus I get an error message
"Object doesn't support this property or method" when I change a field in the mainform

strCtl = Screen.ActiveForm.ActiveControl.Name
strParent = Screen.ActiveForm.ActiveControl.Parent.Name

For Each ctl In Forms(strParent)(strCtl).Form.Controls
.
.
.
![FormName] = Forms(strParent)(strCtl).Form.ActiveControl.Name

I can I tell access to use either code depending on the location of the field (form or subform)



Playing code... Database is found here, in case you are interested as well

Option Compare DatabaseOption Explicit




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 strCtl
  Dim strParent
  Dim strTemp
  Dim strTempControl As Control
  
  strCtl = Screen.ActiveForm.ActiveControl.Name
  strParent = Screen.ActiveForm.ActiveControl.Parent.Name


'  If Forms(strParent)(strCtl).ControlType = acSubform Then
'  strTemp = Forms(strParent)(strCtl).Form.ActiveControl.Name
'  'strTempControl = Screen.ActiveForm.Controls
'  'strActCtl2 = Forms(strParent)(strCtl).Form.ActiveControl
'  Else
'  strTemp = strParent
'  'strTempControl = Screen.ActiveForm.Controls
'  End If
  
  
  
'#
    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"
        
'        If IsNull(Forms(strParent)(strCtl).Form.Controls) Then
'            strTempControl = Screen.ActiveForm.Controls
'        Else
'            strTempControl = Forms(strParent)(strCtl).Form.Controls
'        End If
            
        
        
            For Each ctl In Forms(strParent)(strCtl).Form.Controls 'Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            
                            ![FormName] = Forms(strParent)(strCtl).Form.ActiveControl.Name 'Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![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.Name
                ![Action] = UserAction
                ![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

[orange]

Susy,

Here is a thread related to the use of Martin Green's Audit Trail.
I posted a database with sample application using a revised AuditChanges. Another poster identified some issues with the Audit trail - specifically with recording the wrong recordID to the audit log when deleting record(s).
You may get some ideas from the Audit trail thread and/or the sample database audit trail with form/subforms.

I tried the Active Controls etc, but continually had an error 2474 -The expression you entered requires the control to be in the active window which I did not resolve. If you look at the code in the database, you'll see that I included the Form in the parameters to the AuditChanges sub procedure.

Good luck.

[Susy]

hi orange, this looks so good. I am going to take a look right now.

Thanks!

[Susy]

So, it works like a charm...

thank you all!