Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Audit Trail / Form Error: "Object doesn't support this property or method"

  1. #1
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26

    Post Audit Trail / Form Error: "Object doesn't support this property or method"

    Here is the problem I am having (and I am under the gun!!):



    I have made a database for data entry. I added an audit trail. Now I am receiving an error message.

    Error Msg Text: "Object doesn't support this property or method"
    Error trigger: The error appears 1) AFTER I have updated (either an edit or new) data on the main form of "Companies" and WHEN I then click the tab to enter the subform of "Occupations"
    Note: audittrail is recording the changes made in the main form.

    The Audit trail SQL:

    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()
        
        'Populates username from lookup
        Dim rst2 As ADODB.Recordset
        Set rst2 = New ADODB.Recordset
        Dim strSQL As String
        strSQL = "Select UsernameDesc from Lookup_Username WHERE Username = '" & Environ("ComputerName") & "'"
        
        If Not IsNull(DLookup("Name", "MSysObjects", "Name='Lookup_Username' And Type In (1,4,6)")) Then
            rst2.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
                If rst2.EOF Then
                    strUserID = Environ("ComputerName")
                Else
                    If IsNull(rst2![UsernameDesc]) Then
                        strUserID = Environ("ComputerName")
                    Else
                        strUserID = rst2![UsernameDesc]
                    End If
                End If
        Else
            strUserID = Environ("ComputerName")
        End If
            'strUserID = Environ("ComputerName")
            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
    For the subform:

    Code:
    Sub AuditChangesSub(IDField As String, UserAction As String)
        On Error GoTo AuditChangesSub_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()
     
        
        'Populates username from lookup
        Dim rst2 As ADODB.Recordset
        Set rst2 = New ADODB.Recordset
        Dim strSQL As String
        strSQL = "Select UsernameDesc from Lookup_Username WHERE Username = '" & Environ("ComputerName") & "'"
        If Not IsNull(DLookup("Name", "MSysObjects", "Name='Lookup_Username' And Type In (1,4,6)")) Then
            rst2.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
                If rst2.EOF Then
                    strUserID = Environ("ComputerName")
                Else
                    If IsNull(rst2![UsernameDesc]) Then
                        strUserID = Environ("ComputerName")
                    Else
                        strUserID = rst2![UsernameDesc]
                    End If
                End If
        Else
            strUserID = Environ("ComputerName")
        End If
        
        'strUserID = Environ("ComputerName")
        
        Select Case UserAction
            Case "EDIT"
                For Each ctl In Screen.ActiveControl.Parent.Controls
                    If ctl.Tag = "Audit" Then
                        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                            With rst
                                .AddNew
                                ![DateTime] = datTimeCheck
                                ![Username] = strUserID
                                ![FormName] = Screen.ActiveControl.Parent.Form.Name
                                ![Action] = UserAction
                                ![RecordID] = Screen.ActiveControl.Parent.Form(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.ActiveControl.Parent.Form.Name
                    ![Action] = UserAction
                    ![RecordID] = Screen.ActiveControl.Parent.Form(IDField).Value
                    .Update
                End With
        End Select
    AuditChangesSub_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    AuditChangesSub_Err:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Resume AuditChangesSub_Exit
    End Sub
    I really dont know where the problem is with this! I've used this audit trail SQL before with no issues. MSDN says:
    "The "object doesn't support this property or method" error occurs when you try to use a method or property that the specified object does not support."
    It then suggests:
    "

    • Use the Object Browser to determine what members (properties and methods) are available for the selected class (object).
    • Use the IntelliSense feature in the Visual Basic Editor. When you type a period (.) after a property or method in the Visual Basic Editor, a list of available properties and methods is displayed.
    • Use Word Visual Basic for Applications Help to determine which properties and methods can be used with an object. Each object topic in Help includes a page that lists the properties and methods for the object. Press F1 while in the Object Browser or while in a module to display the appropriate Help topic.
    • Use the TypeName function to determine the type of object returned by an expression. The following example displays "Range" because the Content property returns a Range object."


    I am not sure what they mean by "properties and methods" other than the literal meanings. So this Object Browser method is no help.
    IntelliSense is useless as the VBA has been used many times without issues.
    Word Visual Basic for Applications Help also no help as it goes back to "properties and methods" "can be used with an object" - again I understand the literal meanings but I do not know what THE ERROR is refferring to when it comes up, it doesn't open the code editor to show me WHAT is wrong.
    The TypeName feature, same problem. I can't test something I can't find.

    Any help would be great, the database is a large part of my first project after a major promotion so I really don't want to screw this up!

    Thank you guys, you are awesome!!

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,237
    Do a Compile on the code, does it show you which line has the issue?

    Or put a breakpoint at the beginning of the code and step through(F8) the lines to see which one causes the error.

    So after you save the record on the main form, when you attempt to open the subform it gives the error? Does the subform ever open?

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    Does the error point to a particular line of code?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    I tried a compile and I receive no alerts. No line of code is referred to by the error message either
    The sub form does open. If I click back and forth from form to subform, there is no error.
    Only if I edit data in the main form, then I receive this error when opening the subform.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,237
    Explain the last part, so you can enter data in Main and hit Save and get no error? But if you then click or enter the subform, you get the error? Of if you open the main form and add or edit nothing, you can then click on subform with no error?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    I have a suggestion:
    While in the editor, go to Tools>Options>General Tab>Error Trapping and set it to "Break on All Errors" and try your form again.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    For Bulzie - the subform is within the form as a second tab "Occupations", the main tab "Companies" is opened automatically.
    The error happens after I edit data in the Companies tab, when I select the Occupations tab.

    For RuralGuy - OK, did that and yes it selected a point in the code:

    Code:
     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
    Still unclear how this breaks it. This code means "If newvalue <> oldvalue Then" - which seems like a strange place to break at!
    :$

  8. #8
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    For Bulzie - the subform is within the form as a second tab "Occupations", the main tab "Companies" is opened automatically.
    The error happens after I edit data in the Companies tab, when I select the Occupations tab.

    For RuralGuy - OK, did that and yes it selected a point in the code:

    Code:
     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
    Still unclear how this breaks it. This code means "If newvalue <> oldvalue Then" - which seems like a strange place to break at!

    NEW INFO: This error occurs when I switch from one tab to the other regardless of if I have edited the form since opening the form.

    :$

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    It is breaking in the Main Form code. Where in your Main Form code do you invoke the "AuditChanges" procedure?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    If I understand you correctly, you have a Tab Control on your Main Form. Is that correct?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  11. #11
    Datamulcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    This form was imported from another DB made by an analyst who is no longer here.
    The form contains two 'Pages' Company and Occupation. Maybe these are not proper subforms, I am not adept at forms (obviously...)

    In the main form the audit is called by the tag "audit" upon the entire form, and the below code on the before update of the form:

    Code:
      Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.NewRecord Then
            Call AuditChanges("telkey", "NEW")
        Else
            Call AuditChanges("telkey", "EDIT")
        End If
    End Sub
    However, there is other event coding left from the previous admin:

    On Current:
    Code:
    Private Sub Form_Current()
    'execute the locking/unlocking of records for editing
    
    Call LockUnlockRecord
    
    End Sub
    On Load:
    Code:
    Private Sub Form_Load()
    'open frmData_Entry_Company to a new record
    
    DoCmd.GoToRecord acDataForm, "frmData_Entry_tabbed_Company_Occupation", acNewRec
    
    End Sub
    I don't know if these would have any effect..

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    Are these "pages" actually a tall form that you need to page up and down on or is it a Tab Control? If the Forms BeforeUpdate event is executing it would indicate that something has changed in the record to which the Main Form is bound.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    Any chance you could zip up the db and post it?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,864
    Isn't it likely that when looping through the controls, finding the ones that have the tag property set to the required value, that one of them doesn't support the .OldValue property?
    AFAIK, that property is only supported for textboxes and option buttons.
    Last edited by Micron; 04-06-2017 at 01:31 PM. Reason: added info
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,888
    FWIW, the code in the post #1 seems to be from Martin Greens audit trail. His original code did not support subforms if I remember correctly.
    I haven't seen it used with subforms, but there are many iterations/versions out there.

    I agree with Allan -attaching a copy of the db to a post would be helpful. You may have to zip it to minimize size.

    Can you post a few records from your audittrail table to show these sorts of things being logged?
    I have been helping another poster who is using a version of Martin Green audit trail, it had been modified to record the form name, but it has no code for processing the subform.

    The Access TabControl does have an oldValue property.

    I would like to see exactly how the AuditChanges are being called from form and subform events.

    After re-reading the thread, was it you that added the audit trail code, or was it in this application previously?
    Have you or others used the AuditChanges to deal with subform controls?
    Last edited by orange; 04-08-2017 at 07:35 AM.

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

Similar Threads

  1. Replies: 12
    Last Post: 08-07-2014, 10:28 AM
  2. Replies: 4
    Last Post: 01-27-2012, 02:16 AM
  3. Replies: 6
    Last Post: 11-18-2011, 03:46 PM
  4. Replies: 5
    Last Post: 08-05-2009, 04:07 PM
  5. Replies: 2
    Last Post: 02-28-2009, 03:31 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
  •  
Tech Forums: Microsoft Office Forums