Results 1 to 4 of 4
  1. #1
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134

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

    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
    Code:
    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
    Code:
    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

    Code:
    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,824
    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.

  3. #3
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134
    hi orange, this looks so good. I am going to take a look right now.

    Thanks!

  4. #4
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134
    So, it works like a charm...

    thank you all!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Best screen resultion for most common screen sizes
    By sjs94704 in forum Database Design
    Replies: 3
    Last Post: 05-26-2015, 06:12 AM
  2. Replies: 3
    Last Post: 09-10-2014, 12:18 PM
  3. Log In Screen programming problems
    By GGCR in forum Access
    Replies: 1
    Last Post: 01-20-2014, 10:27 AM
  4. Replies: 11
    Last Post: 06-05-2011, 09:51 PM
  5. Replies: 10
    Last Post: 12-31-2010, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums