Microsoft KB Archive/184664

= ACC: Error "Operation Is Not Supported" with OldValue Property =

Article ID: 184664

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q184664



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you run code that uses the OldValue property of a control on a form, you receive the following error message:

Run-time error '64535':

Operation is not supported for this type of object.



CAUSE
Your form is based on an AutoLookup query that is based on more than one table, and there is a one-to-many relationship between two of the tables.

When you change the data in one field of a record, the Microsoft Jet database engine saves the entire record instead of saving only the field that you modified. When you change the data in any field on the "many" side of the relationship, the data in the foreign key field is also saved again. The Microsoft Jet database engine must then requery the fields from the "one" side of the relationship; this ensures that they contain the data corresponding to the value that you just saved in the foreign key field. Once the Microsoft Jet database engine has requeried the field, trying to access the value of the OldValue property of a control bound to that field results in a run-time error because, at this point, the OldValue property is no longer valid.



RESOLUTION
Use the RecordsetClone property of the form to retrieve a control's previous value. In the following example, the OnCurrent property of a form is set to an event procedure that creates a recordset; the recordset is a copy of the form's underlying record source. The procedure uses the Bookmark property of the recordset to find the record that corresponds to the current record on the form.

 Start Microsoft Access and open the sample database Northwind.mdb.  Create the following query and base it on the Categories table and the Products table:

      Query: qryAutoLookup ---      Type: Select Query Join: Categories.[CategoryID] <-> Products.[CategoryID]

Field: ProductID Table: Products Field: Product Name Table: Products Field: CategoryID Table: Products Field: CategoryName Table: Categories

Save the query as qryAutoLookup and close it.  In the Database window, click the qryAutoLookup query to select it. On the Insert menu, click AutoForm. On the View menu, click Design View.</li> On the View menu, click Code.</li>  In the Declarations section of the module, type the following line:

<pre class="fixed_text">      Dim rs as Recordset </li>  Set the OnCurrent property of the form to the following event procedure:

Private Sub Form_Current Set rs = Me.RecordsetClone rs.Bookmark = Me.Bookmark End Sub </li>  Add a command button to the form and set its properties as follows:

<pre class="fixed_text">      Name: OldCategory Caption: Old Category Name </li>  Set the OnClick property of the command button to the following event procedure:

Private Sub OldCategory_Click MsgBox rs![CategoryName] End Sub </li> Save the form as frmAutoFix and switch to Form view.</li> Change the value in the Product Name box.</li> Click Old Category Name. Note that the message box displays the value of the CategoryName field and that no run-time error message appears.</li> Change the value in the Product Name box; then change the value in the Category box.</li> Click Old Category Name. Note that the message box displays the previous value of the CategoryName field.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Follow steps 1 through 5 in the "Resolution" section of this article.</li>  Add a command button to the form and set its properties as follows:

<pre class="fixed_text">     Name: OldCategoryName Caption: Old Category Name </li>  Set the OnClick property of the command button to the following event procedure:

Private Sub OldCategory_Click MsgBox Me![CategoryName].OldValue End Sub </li> Save the form as frmOldValue and switch to Form view.</li> Change the value in the Product Name box.</li> Click Old Category Name. Note that you receive the error described in the "Symptoms" section.</li></ol>

Additional query words: run time run time error 64535

Keywords: kberrmsg kbprb KB184664

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.