Page 1 of 4 1234 LastLast
Results 1 to 15 of 53
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Audit trail not working on form with subform


    I thought I had this solved as the code was working on my test scenario. However, my test scenario was on just a solo form. My application has a subform and I can't get this audit trail to work. Any suggestions?
    Code:
    Function AuditTrail()
    On Error GoTo Err_Handler
     
        Dim MyForm As Form, C As Control, xName As String
        Set MyForm = Screen.ActiveForm
        'Set date and current user if form has been updated.
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "Changes made on " & Now() & " by " & Environ("USERNAME") & ";"
        'If new record, record it in audit trail and exit sub.
        If MyForm.NewRecord = True Then
            MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
            "New Record """
            MyForm.Lastupdated = Now()
            MyForm.LastUpdatedBy = Environ("USERNAME")
            Exit Function
        End If
     
        'Check each data entry control for change and record
        'old value of Control.
        For Each C In MyForm.Controls
     
        'Only check data entry type controls.
        Select Case C.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                ' Skip Updates field.
                If C.Name <> "Updates" And C.Name <> "LastUpdated" Then
     
                ' ' If control was previously Null, record "previous
                ' value was blank."
                      If (Len(C.OldValue & vbNullString) = 0) And (Len(C.Value & vbNullString) > 0) Then
     
                       MyForm!Updates = MyForm!Updates & Chr(13) & _
                       Chr(10) & C.Name & "--previous value was blank"
     
                    ' If control had previous value, record previous value.
                    ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                        C.Name & "==previous value was " & C.OldValue
                    End If
                End If
            End Select
        Next C
    TryNextC:
    MyForm.Lastupdated = Now()
    MyForm.LastUpdatedBy = Environ("USERNAME")
        Exit Function
     
    Err_Handler:
        If Err.Number <> 64535 Then
            MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
        End If
        Resume TryNextC
    End Function

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what is MyForm? the parent form or the sub on the parent?

    one thing I notice right away: MyForm!Updates You might want to try and replace those combos with: forms(MyForm.name).controls("Updates")

    Another thing too. The code is sporting some inconsistencies:
    1) MyForm.LastUpdated

    2) MyForm!Updates
    if those two are both controls, consider using the same syntax for all references. I have never personally been a fan of the bang character ("!"). I always stick to the period. code is longer but I've never encountered an error using it in any version of Access I've used. Cannot say the same for the exclamation point.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Intellisense does not function when you use the bang (!) either.

  4. #4
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Adam, thanks for the reply. I am using the code from the microsoft kb http://support.microsoft.com/kb/197592 and tweaking it to make it work for me (or not work in this case). I assume MyForm is the active form on the screen based on what I have read. I had this working on a single form but as soon as I tried to apply to a form with a subform, it is not working correctly. I am probably doing something wrong in that I don't know if I should call the module in the subform before update event as well as the main form. I have been doing some searching on the intranet on this topic and I see other posts that have had the same problem with some suggested fixes. But I clearly don't know what I am doing since I can't get it to work.

    Anyway, thanks for your suggestions.

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by RuralGuy View Post
    Intellisense does not function when you use the bang (!) either.
    definitely one reason I like the period!

    Quote Originally Posted by jpkeller55 View Post
    as soon as I tried to apply to a form with a subform, it is not working correctly. .
    the MS code looks pretty standard for any version of access. Again though, WHAT is not working? are you simply seeing nothing happen? does it run error free?

    One of the biggest downfalls with MS KB base coding examples is that they use 99% of them on the NW database example. This can be a problem for some people because NW has functionality in it that most people don't know about.

    Good example - I was looking at it to copy a design idea the other day and I could not open the report I wanted to look at from the db window because they added a line in the report's TAG property that prevents it from being opened unless a filter is applied beforehand. I don't know about you, but the majority of people trying to work Access on their own don't even know what the TAG property is used for!

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I doubt that a SubForm would work with this code since you can not reference a SubForm the same way as you do a MainForm. This code is designed for a single form mode form that archives the records on the MainForm.

  7. #7
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Ok, I realize I didn't post my problem very well so let me rephrase the post.

    I have a database application that our company is using to maintain a bank of questions and responses that we will use in another application for creating a group of questions to be used in a survey of sorts. Certain questions and possible responses will be displayed based on certain criteria. In order to manage our creation of these questions, we are using Access. Basically there are two tables. One table that holds the text of the question (and other attribute fields) and another table related to it that stores the possible responses (and other attribute fields). The table storing the questions is "tblQuestions" and the table storing the responses is "tblQuestResponseSets". There is a one to many relationship between these two tables.

    I have a form called "frmQuestionDetail" (parent) that points to tblQuestions where the user can enter details about the question into the DB (question text, question category, sort order etc...). Attached to this form is a subform called "frmQandRsubform". The main form is displayed at the top of the form and the subform displays at the bottom in datasheet view with the listing of the answers, their order, attributes.

    So what I want to be able to do is store in a field in tblQuestions (field is UPDATES) the audit trail. I also have an UPDATES field in the tblQuestResponseSets that I want to be able to store the audit trail for any changes to responses.

    Right now I have the attached code named as Module1 and on the Before Update Event on the frmQuestionDetail I have =AuditTrail(Form). When I make and update on a record using the frmQuestionDetail, I am not getting the Date/Time stamp to populate nor the username only the change information for the affected field in tblQuestions. If I make an edit to a response on this field, I am seeing absolutely no data in the tblQuestResponseSets in the audit trail field (tblQuestResponseSets.Updates).

    I know there must be a solution to this. I hope this states the problem more clearly. Thanks for taking a look.

  8. #8
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    RG, I thought there might be a solution as I have been searching other sites with the same problem posted but I can't seem to get any of the fixes to work for me. Attached is one such link. http://www.pcreview.co.uk/forums/thread-1157734.php

  9. #9
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    keller,

    your last post reminded me of a small issue that sometimes comes up. Some write code and put the actual field names in the for the control name reference. Note that the only time a control's FIELD = control's NAME by default is when you add controls to a form via the "field list dialog" (autoforms is another example), either by drag and drop or double click. Any other time, the name is called something like "textbox01".

    If nothing else, check that the control on your form that is bound to that UPDATE field is actually called "update" in its NAME property as well. I'm not sure if "update" is a reserved word in Access, but if it is, that might cause some problems as well.

    I'm taking off for now. Hope you get it sorted out!

  10. #10
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Hey Adam, thanks for that bit of advice. It actually turned out that on the subform, I did not have the fields labeled correctly. Once I fixed that it indeed captured the audit trail for both the subform and the edits done on the main form. My only problem that remains is this: The audit trail for the main form is not recording the Date/Time and the userID as part of the audit. It is only printing out what the actual change was.

    For example: it says only: FieldName==previous value was blah blah blah.

    It is supposed to say: Changes made on 9/14/2010 1:50:21 PM by USERNAME; FieldName==previous value was blah blah blah

    This is working correctly on a form that I have the module connected to which is just a single main form with no subform. I am thinking it has something to do with the order of the code lines??

    So here is the resolution to my main problem that I originally posted:
    1. Make sure both the form and subform have Before Update event have the following =AuditTrail(Form)
    2. Make sure both forms have control names that match what is in your code. In this case, my control names on the subform did not match my code.

    Now, just need to figure out why the date/time user part is not printing for the Main form in the audit trail.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may wish to review this procedure that MVP Allen Browne has. http://allenbrowne.com/AppAudit.html I'm pretty sure it works from either a MainForm or a SubForm.

  12. #12
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Forgot to mention one big thing. I did modify the code to the attached to make this work. Changes from original post in red.

    Code:
    Function AuditTrail(MyForm As Form)
    On Error GoTo Err_Handler
     
        Dim C As Control, xName As String
        'Set MyForm = Screen.ActiveForm
        'Set date and current user if form has been updated.
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "Changes made on " & Now() & " by " & Environ("USERNAME") & ";"
        'If new record, record it in audit trail and exit sub.
        If MyForm.NewRecord = True Then
            MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
            "New Record """
            MyForm.Lastupdated = Now()
            MyForm.LastUpdatedBy = Environ("USERNAME")
            Exit Function
        End If
     
        'Check each data entry control for change and record
        'old value of Control.
        For Each C In MyForm.Controls
     
        'Only check data entry type controls.
        Select Case C.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup
                ' Skip Updates field.
                If C.Name <> "Updates" And C.Name <> "LastUpdated" Then
     
                ' ' If control was previously Null, record "previous
                ' value was blank."
                      If (Len(C.OldValue & vbNullString) = 0) And (Len(C.Value & vbNullString) > 0) Then
     
                       MyForm!Updates = MyForm!Updates & Chr(13) & _
                       Chr(10) & C.Name & "--previous value was blank"
     
                    ' If control had previous value, record previous value.
                    ElseIf IIf(IsNull(C.Value), "", C.Value) <> C.OldValue Then
                        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                        C.Name & "==previous value was " & C.OldValue
                    End If
                End If
            End Select
        Next C
    TryNextC:
    MyForm.Lastupdated = Now()
    MyForm.LastUpdatedBy = Environ("USERNAME")
        Exit Function
     
    Err_Handler:
        If Err.Number <> 64535 Then
            MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
        End If
        Resume TryNextC
    End Function

  13. #13
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Thanks RG...I did look at Allen Browne's post but couldn't figure it out! Any idea why my date/time stamp is not showing up on the audit trail?

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This code:
    MyForm.Lastupdated = Now()
    MyForm.LastUpdatedBy = Environ("USERNAME")
    ...is putting those values in different controls on your form. I do not see where these values are going into the MyForm!Updates control on the form.

  15. #15
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    I actually have 3 fields on each table named:
    1. LastUpdated
    2. LastUpdatedBy
    3. Updates

    1 and 2 are being populated correctly to each table.
    3 utilizes the code:
    Code:
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "Changes made on " & Now() & " by " & Environ("USERNAME") & ";"
    to add to the verbiage of the Updates field. 3 is working fine when I use the module on a single Main form. It is also working correctly on the subform. It is just the Main form when used with the subform that it is not printing correctly.

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

Similar Threads

  1. audit Log
    By mwabbe in forum Access
    Replies: 42
    Last Post: 05-30-2011, 07:23 AM
  2. Audit trail question #2
    By jpkeller55 in forum Access
    Replies: 9
    Last Post: 09-13-2010, 05:55 PM
  3. Audit trail question
    By jpkeller55 in forum Access
    Replies: 5
    Last Post: 09-13-2010, 04:12 PM
  4. Filter By Form not working!
    By Freybourne in forum Access
    Replies: 6
    Last Post: 06-22-2010, 09:41 PM
  5. Replies: 1
    Last Post: 09-28-2009, 05:21 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