Oldvalue throwing error 3251 inconsistently

[glgcag1991]

Checking OldValue of bound comboboxes on multiple forms. Some are throwing error 3251 (Operation is not supported for this type of object). I can't find any consistency for this but it just started happening. It is an Access 2003 database and we have been using it for years without problem. I have compacted and repaired both the client db and the data db- no change. Here is the basics of the audit record I'm trying to create on before_update of the combobox fields:

Private Sub cboType2Key_BeforeUpdate(Cancel As Integer)
    If Not Nz(Me.cboType2Key.OldValue, "") = "" Then
        sAuditNote = sAuditNote & (append what has changed here)
    End If
End Sub

It errors on the "If Not Nz(. . ." line whether the value is null or not. If I take out the OldValue the error goes away. Any ideas?

Thanks!

 

[Andrzejek]

If you type in your code:

[tt]Me.cboType2Key.[/tt]

Does the intelisense give you [tt]OldValue[/tt] as one of the property to choose from?

Have fun.

---- Andy

 

[glgcag1991]

Yes, it is available via Intellisense.

 

[Andrzejek]

If your cboType2Key holds numbers and not text, you may try:

[tt]If Not Nz(Me.cboType2Key.OldValue, [blue]0[/blue]) = [blue]0[/blue] Then[/tt]

Have fun.

---- Andy

 

[glgcag1991]

Didn't change anything. Even in debug mode if I just hover over the OldValue, it gives me the 3251 error. Something to do with the OldValue property is causing this.

 

[Andrzejek]

Is this particular combo an unbound control? .OldValue only works for bound controls.
Quote from here

Have fun.

---- Andy

 

[glgcag1991]

Yes, I did a bunch of research before posting and that was one of the things that came up. It is a bound combo. Also, I found: the OldValue is a string as well as there are issues with forms bound to multitable recordsets.

I have found a bunch of posts on various forums about this issue but none have an actual resolution. It is very bizarre!

 

[glgcag1991]

So, a little more information:

When I create a new record and open the form, I get the OldValue errors. (Keep in mind when I create a new record, the only thing that exists is the ID on the form and the user has to create all the rest of the info once the form is open.) If I close the form immediately after creating a new record, having entered nothing, then reopen it, I can edit everything without any OldValue code causing errors.

It seems to be only after the initial record creation that the OldValue code is causing problems.

I hope this gives a little more insight into the problem.

 

[PHV]

So, what about this ?

Private Sub cboType2Key_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
    If Trim(Me!cboType2Key.OldValue & "") <> "" Then
        sAuditNote = sAuditNote & (append what has changed here)
    End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glgcag1991]

This doesn't work either. For now I just commented out the audit code and I'll have to come up with something different. Weird! Not sure why it just stopped working!

Thanks guys!

 

[MajP]

I believe if the field is in an uneditable state you cannot check the oldvalue and will get this error. I still find it hard to believe that PHV's code does not work if in fact it only occurs on a new record. Are you sure you implemented his code? You could simply trap the error and check for 3251. Or you could simply do a dlookup and see if there is an existing value in the table.

 

[glgcag1991]

MajP,

The field is editable (I have an "Edit" button on the form that changes the field state for users if they have rights). I have tried PHV's code and it is never coming up as a new record. Looking at how the form is opened, I see that the original designer created a table named the same as the form and after adding a new record, saves the ID to the table and joins to the actual data table on this. For this reason, I think it NEVER sees it as a new record.

I also saw something about multitable joins as a recordsource for the form possibly being the problem. The weird thing is that the comboboxes that are giving me trouble are indeed from another table, but there are other comboboxes on the form that are also from other tables but don't fail in the audit code that calls OldValue. Strange indeed!

 

[MajP]

adding a new record, saves the ID to the table and joins to the actual data table on this

Yeah, it is likely that has not occured yet and the record is in an uneditable state. This is likely why the problem is intermittent. Likely a timing issue. What if you add doevents prior to your code?