AuditTrail() Error

[Matt K.]

I am using the AuditTrail function from Allen Browne to track changes to
bound fields on a form. I call the function from the BeforeUpdate Event of
the form. When the event happens I get "Run time Error 3251. Operation not
supported for this type of object." When I debug in the code break of
VBEditor, it appears the problem is with the control's .OldValue...when I
move the mouse over the code that contains '.OldValue' I get the error dialog
again.
Clicking the HELP button on the error dialog offers no help.
That is the basics of it. I can post my code if needed...I've tried to step
through the code in "break mode" but can't seem to figure it out.
Any Takers??

 

[ruralguy via AccessMonster.com]

I've been using Allen's AuditTrail code for years with out a problem. Maybe
you need to post the code around where the debugger stops.

 

[Allen Browne]

Which control is it trying to get the OldValue of?

Is it a control? If it is a Field, not a control (i.e. a field in the
RecordSource, but there is no control with that name on the form), try
adding a text box with that name (hidden if you like.)

What is the RecordSource of this form? Is it a multi-table query? Is this
particular field from a table whose fields are not updatable? If so, the
OldValue property would not apply.

 

[Douglas J. Steele]

In addition, you can't use OldValue with unbound controls.

 

[Matt K.]

Finally had some time to break away from the "weekend chores"...

First, I apologize to Allen: I'm not using your Audit Log Function, rather I
am using a variation of the AuditTrail Function I found in a Microsoft
Knowledgebase article. I do appreciate your reponse though.

Code posted at bottom of post....
OK I'll do my best to describe the situation-

I've narrowed it down to
1) a control with the name of CONTRACTid. (This control was 'dragged' from
the FieldList window to the form and changed to a cbobox.)
2)The control's ControlSource is also the field CONTRACTid.
3)The control is a combobox whose RowSource comes from tblCONTRACTS.
4)This control is on a tab page of the form.
5)The form's RecordSource is indeed a multi-table query; however the field
CONTRACTid is an updateable field...at least I don't ever remember
intentionally making NOT.
In the AuditTrail code I am also skipping over controls on the form that are
not enabled. As a test, I disabled the control CONTRACTid and everything
seems to work fine.

Function AuditTrail()
Dim frmActive As Form
Dim ctlData As Control
Dim strModByWhen As String
Dim strSQL As String
Dim db As DAO.Database
Dim strLastValue As String
Dim strFldName As String

On Err GoTo NextCtl

Set frmActive = Screen.ActiveForm

'Determine who and when
strModByWhen = " by " & fOSUserName() & " on " & Now()

'Check each data entry control for change and record
For Each ctlData In frmActive.Controls

' Only check data entry type controls.
Select Case ctlData.ControlType
Case acTextBox, acComboBox, acCheckBox, acOptionButton
' Skip txt18MonthDate field.
If ctlData.Name = "txt18MonthDate" Then GoTo NextCtl

'Skip unbound and hidden controls (3 is ControlSource)
If ctlData.Properties(3) = "" Or ctlData.Enabled = False Or
ctlData.Visible = False Then GoTo NextCtl

Select Case IsNull(ctlData.Value)
'Check for deleted data
Case True
If Not IsNull(ctlData.OldValue) Then
strFldName = ctlData.Name & " - Data Deleted: Old
value was "
strLastValue = ctlData.OldValue
Set db = CurrentDb
strSQL = "INSERT INTO tblHistory( SURVEYid, FldName,
LastValue, ModByWhen)" & _
" VALUES (" & frmActive!SURVEYid & ", '" &
strFldName & "', '" & strLastValue & "', '" & strModByWhen & "');"
db.Execute strSQL
Set db = Nothing
End If
'Check for new or changed data
Case False
If IsNull(ctlData.OldValue) And Not
IsNull(ctlData.Value) Then
strFldName = ctlData.Name & " - Data Added: "
strLastValue = ctlData.Value
Set db = CurrentDb
strSQL = "INSERT INTO tblHistory( SURVEYid, FldName,
LastValue, ModByWhen)" & _
" VALUES (" & frmActive!SURVEYid & ", '" &
strFldName & "', '" & strLastValue & "', '" & strModByWhen & "');"
db.Execute strSQL
Set db = Nothing
'If control had previous value, record previous value.
ElseIf ctlData.Value <> ctlData.OldValue Then
strFldName = ctlData.Name & " changed from "
strLastValue = ctlData.OldValue
Set db = CurrentDb
strSQL = "INSERT INTO tblHistory(SURVEYid, FldName,
LastValue, ModByWhen)" & _
" VALUES (" & frmActive!SURVEYid & ", '" &
strFldName & "', '" & strLastValue & "', '" & strModByWhen & "');"
db.Execute strSQL
Set db = Nothing
End If
End Select
End Select
NextCtl:
Next ctlData
End Function