Results 1 to 4 of 4
  1. #1
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94

    Audit Trail works with form but not with sub-form ????

    Access 2019



    I downloaded this code from https://community.spiceworks.com/top...ccess-database After some effort I got everything working on a form.

    Code:
    
    
    Code:
    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
                                ![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
    .On the Form I set this event procedure



    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.NewRecord Then
            Call AuditChanges("KeyField", "NEW")
        Else
            Call AuditChanges("KeyField", "EDIT")
        End If
    End Sub
    .
    Where "KeyField" is the primary key for the data.

    I have a form called "frmAllBenf" with a sub-form named "frmsubMain".


    Click image for larger version. 

Name:	Audit SubForm 2021-10-18_21h03_48.jpg 
Views:	13 
Size:	214.8 KB 
ID:	46441

    When I run the open the sub-form frmSubMain by it self the audit subroutine works OK.


    Click image for larger version. 

Name:	Audit SubForm 2 2021-10-18_21h03_48.jpg 
Views:	13 
Size:	197.2 KB 
ID:	46442

    HOWEVER, when I open the main form with the subform and make changes to the data in the subform the subroutine does not work

    Suggestions ?????

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Problem is, subform is not open as a form, it is not in Forms collection. It resides in a container control that sits on a form. The main form is the "ActiveForm".

    See if this version will work http://allenbrowne.com/AppAuditCode.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to June's comment, there was an interesting thread on this subject back in 2017. A sample audit trail routine (form based) didn't work with subforms. Read through the dialog for ideas.
    Another approach to audit trail.

  4. #4
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    Thanks for the good input !!!!!!

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

Similar Threads

  1. Replies: 13
    Last Post: 12-19-2017, 10:32 AM
  2. Issue Creating Audit Trail for fields on a Form
    By nika.duncan in forum Programming
    Replies: 8
    Last Post: 07-02-2015, 11:24 AM
  3. Replies: 3
    Last Post: 01-13-2015, 03:43 PM
  4. Replies: 9
    Last Post: 07-07-2014, 02:54 PM
  5. Audit trail not working on form with subform
    By jpkeller55 in forum Access
    Replies: 52
    Last Post: 01-07-2011, 12:39 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