Checkbox OldValue property gives Error 3251

[Monterey_Manzer]

Hi All,

I am having a rather aggravating problem with some of my forms. I've got some data protection code written into the BeforeUpdate form event that gets both the OldValue and Value of each bound control and compares them. However, I'm running into a problem with one of my checkboxes. It throws the Error 3251: "Operation is not supported for this type of object". I know it isn't a problem with checkboxes as I've stepped through all the controls the code cycles through and it performs the comparison just fine with another checkbox. The only thing that jumps out to me is that I have Yes/No data fields from two different tables. I would appreciate any help anyone could offer. By all accounts this should work just fine. The worst part of this error is that, unless I disable my data protection code, the user cannot save any record changes.

Oh, here is the code I am using:

For Each txtCtrl In Me.Controls
        
        If txtCtrl.ControlType = acTextBox Then
        If txtCtrl.Locked = True Then
            GoTo myNext
        End If
            table = "InternsTracker"
            RecID = Me.ID
            'Debug.Print txtCtrl.Name
            oldVal = txtCtrl.OldValue
            newVal = txtCtrl.Value
            If IsNull(oldVal) = True And IsNull(newVal) = False Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            ElseIf IsNull(oldVal) = False And IsNull(newVal) = True Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            ElseIf oldVal <> newVal Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            End If
        ElseIf txtCtrl.ControlType = acComboBox Then
        If txtCtrl.Locked = True Then
            GoTo myNext
        End If
            If txtCtrl.Name = "cobIntYear" Then
                GoTo myNext
            End If
            table = "InternsTracker"
            RecID = Me.ID
            oldVal = txtCtrl.OldValue
            newVal = txtCtrl.Value
            If IsNull(oldVal) = True And IsNull(newVal) = False Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            ElseIf IsNull(oldVal) = False And IsNull(newVal) = True Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            ElseIf oldVal <> newVal Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            End If
        ElseIf txtCtrl.ControlType = acCheckBox Then
            If txtCtrl.Locked = True Then
             GoTo myNext
            End If
            If txtCtrl.Name = "Clearance Status" Then
                table = "PartInfo"
                RecID = Me.SMART_ID
            Else
                table = "InternsTracker"
                RecID = Me.ID
            End If
            oldVal = txtCtrl.OldValue '<----This is where the error occurs
            newVal = txtCtrl.Value
            If oldVal <> newVal Then
                field = txtCtrl.Name
                Call ChangeTrack(table, field, RecID, oldVal, newVal, changeDate, user, Form)
            End If
        End If
myNext:
    Next txtCtrl
    Exit Sub

Thanks,
Ryan

[burrina]

You may have to Declare a Variable: Try and see if it helps.

Public rstInternsTracker As DAO.Recordset
Set rstInternsTracker = CurrentDb.OpenRecordset("InternsTracker", dbOpenDynaset)

[Monterey_Manzer]

I am unclear on how that would help. The controls all already bound to the form's recordSource. Are you suggesting I switch the source of the variable oldVal from the control's OldValue property to the underlying table value?

[burrina]

NO. Too hasty in my Post. Have to think on this a bit!

[burrina]

Okay, try this and let me know what happens.

Temporarily comment out the error handling. When it stops, open the
Immediate Window (Ctrl+G), and enter:
? Ctl.Name
You will then know which field it is struggling with. If this is an
uneditable field and you just want to ignore it, add error handling to
ignore error 3251.

[Monterey_Manzer]

Okay, this one's my fault for not already mentioning it. When this triggers I look in the Locals window and check the properties of my txtCtrl variable. From this I know the field/control that is causing the error. It is the one named "Clearance Status". However, this field is editable on this form. Perhaps the problem is that this field occurs on two forms? However, I have my RecordLock property set to EditedRecord and none of my users were editing the same record. Honestly I'm starting to think the DAO recordset idea wouldn't be the worst work-around solution. It is just annoying since this type of use is exactly what the OldValue property is for!

[June7]

Does the error actually trigger in the ChangeTrack procedure not posted?

[Monterey_Manzer]

June,

The error occurs on the line that invokes txtCtrl.OldValue that I highlighted in the above code. It doesn't make it to the ChangeTrack procedure.

[June7]

Don't see any highlights. Do you mean:

oldVal = txtCtrl.OldValue

Is oldVal declared as a Variant?

You say other checkboxes work? What could be different about this one? What is OldValue property holding when the error triggers?

Do you want to provide db?

[Monterey_Manzer]

Sorry if I misspoke, by highlighted I meant the <--- arrow. Yes the oldVal=txtCtrl.OldValue is where the error occurs and yes oldVal is declared as Variant. I am beginning to wonder if it is something to do with the record the form is editing or something to do with the particular features of the form's recordsource at the time. What leads me to this suspicion is sometimes I can open the form, make changes in field values, and not generate any errors but at other times I cannot edit a single record without having this problem. And when I do it is always the Clearance Status checkbox that is the control causing the error. I have checked the recordsource to see if the records are locked and that does not appear to be the issue. Also, since the OldValue property is read-only that shouldn't be a problem.

Sorry, it's kind of a nightmare scrubbing the DB of sensitive information.

[June7]

Those are reasonable assessments and only you will be able to pursue. Good Luck.

[Monterey_Manzer]

If I figure anything out I will definitely post it here as it seems like a few people have experienced this issue and as yet there are no concrete solutions on any of the many Access forums.

[manonash]

... the field/control that is causing the error. It is the one named "Clearance Status". However, this field is editable on this form. Perhaps the problem is that this field occurs on two forms?

I've had a similar problem recently involving a control having a ControlSource which is a calculated text field defined in a query. The field is complex: it draws text snippets from several tables that are recursively joined. (Think of an employee hierarchy where Joe is Barb's boss, Barb is Bill's boss, Bill is Jerry's boss, Jerry is Jane's boss, etc.) Anyway, suffice to say that I can kind of understand Access choking on an attempt to represent the OldValue property of such a control. It really doesn't have an OldValue, since it's always calculated.

I am able to trap for the error that occurs when the attempt is made to reference the OldValue, BUT I have also used a similar control with the same ControlSource on a different tab page on the same form. (It identifies the record within which all the other data on the tab is detail.) When my loop through the form's controls reaches the second (redundant) control using that same ControlSource, the error trapping fails. It actually kicks the execution focus back up to the method that called for the audit record to be written, where the error trap there catches it and issues a message. But meanwhile, the attempt to write the audit record has failed.

If I removed the ControlSource from either of the redundant textboxes, everything works great with On Error trapping in place; but when the ControlSource is set normally in both of them, I can't get the error trapping to work on the second instance.

My current work-around is to put text in the Tag property of that second control -- the one I need the audit write to skip. You can see how I am doing that below. Also note that I follow my If trap with an On Error trap. That's because it's only on the second instance of the redundant control that the thing blows up. So, I still need the error trapping active for the encounter with the first instance, when error trapping works.

I could, of course, add the "SkipDuringAuditWrite" tag to the first instance of the control as well, in which case I wouldn't need the On Error statement. But I like it active in case some other control somewhere, sometime, causes an error to be thrown that would otherwise make the audit write fail. (I'd rather an imperfect audit record written than none at all.)

        If ctl.ControlSource > "" Then
          If ctl.Tag = "SkipDuringAuditWrite" Then
            GoTo NextControl
          End If
          On Error GoTo NextControl
          
          If .Value <> .OldValue Then