Solved

3251 Operation is not supported for this type of object

Posted on 2016-08-16
5
53 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

740 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