Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32

    Access 2010: Audit Trail not Working in Subform

    I've read other posts about this, but they weren't helpful because I'm using different code. I'm hoping somehow can tell me simply how to get my audit trail to work in my subforms. The code works perfectly in a main form, but doesn't write anything to my audit table from a subform.

    This is the module code I'm using:

    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
    I also use this on the form & subform BeforeUpdate and BeforeDelConfirm:



    Code:
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
        If Status = acDeleteOK Then Call AuditChanges("SessionID", "DELETE")
    End Sub
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.NewRecord Then
            Call AuditChanges("SessionID", "NEW")
        Else
            Call AuditChanges("SessionID", "EDIT")
        End If
    End Sub
    Does anyone know how to get this to work from my subforms??

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    are you also running the code from your subform? I don't use bound forms/subforms but I would think that if you had the same function in your subform and called it on the beforeupdate event of the subform (just as you have for the main form) you'd be fine.

  3. #3
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32
    Yes, I am running the same code on my subform. It works on the subform when I open the subform by itself, but not when I'm entering data into the subform via the main form.

  4. #4
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124
    Have you tried a Debug statement in the BeforeUpdate Event on both form and subform? Forgive my ignorance but what is the variable "Status" referring to?

  5. #5
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Besuchanko,

    How did you set the BeforeUpdate event for your subform when there's no such event for subforms? Curious, I'm using the same audit code you're using and I too have subforms which the code is not collecting the controls from within the subform. When I add a debug.print statement to show the control name and control type, there's nothing recorded in the immediate window from any subform.

    CementCarver

  6. #6
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32
    I got this code off of the internet from here: http://www.fontstuff.com/access/acctut21.htm

    I just followed the instructions from the link. I'm not a programmer, so I wouldn't know how to fix this on my own.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you provide a sample of your database so I don't have to work something from scratch.

  8. #8
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Rpeare,

    I'll have a sample db to upload tomorrow.

    CementCarver

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    please make it something 2007 or earlier!

  10. #10
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32
    Here's a link to my database: https://www.dropbox.com/s/wt5ig2nw6c...%20TRAIL.accdb

    The file is protected, so you'll have to hold down the SHIFT key while opening the file to access the tables, forms, etc. As I mentioned, the audit trail works perfectly when I make changes in the Logistics subform when I open it by itself. However, I actually use it as a subform in the USER INTERFACE form. When I make changes to the Logistics subform from within the USER INTERFACE form, nothing is recorded in the audit table. If anyone can tell me what to do to make it work, I'll be ecstatic.

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the problem is that you're using this reference:

    Screen.ActiveForm.<stuff>

    When you use this you're just passing the MAIN FORM information through your code.

    You would actually have to pass the subform name and you'd have to properly reference the subform controls (sorry I don't have a whole lot of time to mess around with it) but here's a reference to a control on a subform:


    Forms![main form name]![subform control name].Form![control name]

    what you may be able to do is set the activeform (again I don't have time to mess around with it much today)

    Dim frmCurrentForm As Form
    Set frmCurrentForm = logistics_subform

    then cycle through the controls but you'd have to pass the subform name through along with your edit/new and PK field as well.

    The easier method may be to take each subform and create a function within it that's basically the same code and update the references to the subform and remove/update any reference to the main form.





  12. #12
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi besuchanko

    did you get any further with this?, would be great to hear if it had

    Steve

  13. #13
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Steve,

    I've managed to use rpeare's suggestions and have been able to isolate the controls within my subforms, so that when my client amends any data in those forms, the results are sent to the audit table.

    CementCarver

  14. #14
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi cementcarver

    would you be able to post some code, i have been trying rpeare's suggestion, but can't manage to get the subforms to post into the audit table.

    Steve

  15. #15
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Steve,

    Below is the contents of one of my modules. The section below only deals with one control on one of my subforms. But then I've replicated this for each control I have. That was the only way I could figure it out. Enjoy.....CC

    Select Case UserAction
    'existing records
    Case "Edit"
    ' Have created the same case select statements for each control found in the GROWER_TRACEABILITY_DATASHEET
    ' subform.
    ' One = for control TraceCodeNew and/or TraceCodeold
    '
    For Each ctl In Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("GRO WER_TRACEABILITY_DATASHEET").Form.Controls
    TraceCodeNew = Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("GRO WER_TRACEABILITY_DATASHEET").Form.Controls("Tracea bilityCode").Value
    TraceCodeOldvalue = Nz(Forms("frm_EDIT_CURRENT_FULLGROWER").Controls(" GROWER_TRACEABILITY_DATASHEET").Form.Controls("Tra ceabilityCode").OldValue)

    If ctl.Tag = "Audit" Then
    If ctl = Forms("frm_EDIT_CURRENT_FULLGROWER").Controls("GRO WER_TRACEABILITY_DATASHEET").Form.Controls("Tracea bilityCode") Then
    'Debug.Print " Action is = " & UserAction & vbNewLine & "Control Name = " & ctl.Name & vbNewLine & "Control Type = " & ctl.ControlType; vbNewLine & "Control Value = " & ctl.OldValue; vbNewLine & "New Value = " & subcontrolTraceCodeNew; vbNewLine & "Old Value = " & subcontrolTraceCodeOldvalue
    If TraceCodeNew <> TraceCodeOldvalue Then
    With rstAudit
    .AddNew
    ![DateTime] = datTimeCheck
    ![UserName] = strAccountName
    '![FormName] = frmCurrentForm
    ![Action] = UserAction
    ![Record_ID] = MaxMember_id
    ![FieldName] = ctl.ControlSource
    ![OldValue] = ctl.OldValue
    ![NewValue] = ctl.Value
    .Update
    'Debug.Print "Current values " & UserAction
    End With
    'Debug.Print "This is the Spot Now = " & ctl.ControlSource

    End If

    End If
    'nd If
    End If
    Next ctl

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-09-2013, 12:39 PM
  2. Audit Trail - Pull User Name From Table, Office 2010
    By brharrii in forum Programming
    Replies: 2
    Last Post: 06-05-2012, 03:31 PM
  3. audit trail
    By slimjen in forum Forms
    Replies: 1
    Last Post: 10-13-2011, 01:52 PM
  4. Audit Trail for SubForms Not Working
    By PinkNinja in forum Access
    Replies: 6
    Last Post: 01-07-2011, 11:03 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

Tags for this Thread

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