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

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

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.

1. Start Microsoft Access and open the sample database Northwind.mdb.



2. 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.



3. In the Database window, click the qryAutoLookup query to select it.



4. On the Insert menu, click AutoForm.



5. On the View menu, click Design View.



6. On the View menu, click Code.



7. In the Declarations section of the module, type the following line:
   

   
          Dim rs as Recordset 




8. 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 




9. Add a command button to the form and set its properties as follows:
   

   
          Name: OldCategory
          Caption: Old Category Name 




10. Set the OnClick property of the command button to the following event procedure:
    

    
           Private Sub OldCategory_Click()
              MsgBox rs![CategoryName]
           End Sub 




11. Save the form as frmAutoFix and switch to Form view.



12. Change the value in the Product Name box.



13. Click Old Category Name. Note that the message box displays the value of the CategoryName field and that no run-time error message appears.



14. Change the value in the Product Name box; then change the value in the Category box.



15. Click Old Category Name. Note that the message box displays the previous value of the CategoryName field.

MORE INFORMATION

Steps to Reproduce Behavior

1. Follow steps 1 through 5 in the "Resolution" section of this article.



2. Add a command button to the form and set its properties as follows:
   

   
         Name: OldCategoryName
         Caption: Old Category Name 




3. Set the OnClick property of the command button to the following event procedure:
   

   
         Private Sub OldCategory_Click()
            MsgBox Me![CategoryName].OldValue
         End Sub 




4. Save the form as frmOldValue and switch to Form view.



5. Change the value in the Product Name box.



6. Click Old Category Name. Note that you receive the error described in the "Symptoms" section.

Additional query words: run time

Keywords : kberrmsg kbdta FmsProb MdlRcd QryFixup RltOthr
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb