Solved

3251 Operation is not supported for this type of object

Posted on 2016-08-16
5
28 Views
Last Modified: 2016-08-17
I have tried to create a function that provides a basic audit trail of changes using the following code. For certain controls (textboxes and comboboxes) I am getting error 3251 Operation is not supported for this type of object. There is no trouble with the Value but it is oldValue that is fouling up and I don't know why. What are the restrictions on accessing the oldValue of a field?


Public Function LogChanges(frm As Form)
    'This function creates a text file in the Logs sub-folder for every day and records changes made to field values in monitored forms
    Dim hFile As Integer
    Dim sLogFileName As String
    Dim ctl As Control
    Dim sKeyIdentifier As String
           
    sKeyIdentifier = " Did: " & Nz(frm.DetailId)
   
    On Error GoTo cLog_Error

    sLogFileName = Format(Now, "yymmdd") & "_" & Environ$("computername") _
        & "_" & Environ$("username") & "_" _
        & Left([CurrentProject].[Name], InStrRev([CurrentProject].[Name], ".") - 1) & ".txt"

    On Error Resume Next ' CREATE THE REQUIRED SUB-FOLDER IF NOT ALREADY CREATED
    MkDir SERVERPATH & "\ChangeLogs\"
    On Error GoTo cLog_Error
   
    hFile = FreeFile
   
    Open SERVERPATH & "\ChangeLogs\" & sLogFileName For Append As #hFile
   
    Print #hFile, Format(Now(), "hh:nn:ss") & ": " & frm.Name & sKeyIdentifier
   
    On Error GoTo cLogLoopError
    For Each ctl In frm   '.Controls
        If ctl.ControlType <> acLabel Then
        If Len(ctl.ControlSource) > 0 Then  'This variable is linked to a table so change should be logged
            If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then
                Print #hFile, String(4, " ") _
                    & Left(ctl.Name, 30) & Space(30 - Len(Left(ctl.Name, 30))) _
                    & "|" & Left(ctl.ControlType, 4) & Space(4 - Len(Left(ctl.ControlType, 4))) _
                    & " |> " & Nz(ctl.OldValue, "")
               
                Print #hFile, String(4, " ") _
                    & Left(ctl.Name, 30) & Space(30 - Len(Left(ctl.Name, 30))) _
                    & "|" & Left(ctl.ControlType, 4) & Space(4 - Len(Left(ctl.ControlType, 4))) _
                    & " >> " & Nz(ctl.Value, "")
            End If
        End If
        End If
NextControl:
    Next ctl
    Set ctl = Nothing

ExitFunction:
    Close #hFile

    On Error GoTo 0
    Exit Function

cLogLoopError:
    If Err.Number = 438 Then Resume NextControl 'Object doesn't support this property or method
   
    Debug.Print ctl.Name & " error: " & Err.Number & " " & Err.Description & " Type: " & ctl.ControlType
    'Stop
    Resume NextControl
    Resume

cLog_Error:
Stop
    If Err.Number = 70 Then Resume ExitFunction
    GoTo ExitFunction

End Function
0
Comment
Question by:Rob4077
5 Comments
 
LVL 3

Accepted Solution

by:
bfuchs earned 300 total points
ID: 41758843
This is what I use, just call it from forms Before Update event,
Public Function SaveHistoryChanges(Optional sTable As String = "", Optional sField As String = "", Optional vValue As Variant = "", Optional lID As Long = 0)
    Dim ssql As String, cNtrl As Control
    If Len(sTable) = 0 Then sTable = Screen.ActiveForm.Name
    If lID = 0 Then lID = Nz(Screen.ActiveForm.ID, 0)
    
    On Error Resume Next
    For Each cNtrl In Screen.ActiveForm.Controls
        If (TypeOf cNtrl Is TextBox Or TypeOf cNtrl Is ComboBox Or TypeOf cNtrl Is CheckBox Or cNtrl.ControlType = 107) Then
            If Nz(cNtrl.Value) <> Nz(cNtrl.OldValue) And cNtrl.Visible = True And cNtrl.Enabled = True Then
                If Not IsNull(cNtrl.OldValue) And Not (TypeOf cNtrl Is CheckBox) Then
                    If Nz(tLookup("ID", "HistoryChanges", "TableName = '" & sTable & "' and FieldName = '" & cNtrl.ControlSource & "' and TableID = " & lID), 0) = 0 Then
                        ssql = "Insert into HistoryChanges (TableName, FieldName, Value, TableID)"
                        ssql = ssql & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.OldValue & "'," & lID & ")"
                        CurrentProject.Connection.Execute ssql
                    End If
                End If
                ssql = "Insert into HistoryChanges (TableName, FieldName, Value, Initial, TableID)"
                ssql = ssql & " Values ('" & sTable & "','" & cNtrl.ControlSource & "','" & cNtrl.Value & "','" & GetInitial & "'," & lID & ")"
                CurrentProject.Connection.Execute ssql
                
            End If
        End If
    Next

End Function

Open in new window

0
 

Author Comment

by:Rob4077
ID: 41758860
Modified it to work for me. Thanks
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 41759104
Personally, I would not output the changes to a text file, but to a database table.  This will allow you to keep all changes in a single table and then query that table for all changes.  As you have it, each users changes will be in a single file, so to track all of the changes to a single datatbase you might have to look at multiple text files.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 41759151
Just to build on Ben's comment a bit, there is a bit more to truly tracking audit changes than just changes in a field.  You can find a complete auditing solution here:

http://allenbrowne.com/appaudit.html

Jim.
0
 

Author Comment

by:Rob4077
ID: 41759191
Thanks everyone for your comments. I will award the bulk of the points to bfuchs but thank you both Jim and Dale for your additional comments. I will modify my auditing as you suggest.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now