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