Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

3251 Operation is not supported for this type of object

Posted on 2016-08-16
5
Medium Priority
?
68 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 1200 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

704 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