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

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

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.

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
   					

3. Save the query as qryAutoLookup and close it.
4. In the Database window, click the qryAutoLookup query to select it.
5. On the Insert menu, click AutoForm.
6. After the auto form is created, click Design View on the View menu.
7. On the View menu, click Code.
8. In the Declarations section of the module, type the following line:

   Dim rs as Recordset
   					

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

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

       Name: OldCategory
       Caption: Old Category Name
    					

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

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

12. Save the form as frmAutoFix and switch the form to Form view.
13. Change the value in the Product Name box.
14. Click Old Category Name. Note that the message box displays the value of the CategoryName field and that no run-time error message appears.
15. Change the value in the Product Name box, and then change the value in the Category box.
16. Click Old Category Name. Note that the message box displays the previous value of the CategoryName field.

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 the form to Form view.
5. Change the value in the Product Name box.
6. Click Old Category Name. Note that you receive the error message mentioned in the "Symptoms" section.