ACC2000: Error "Operation is not supported" with OldValue Property

ID: Q207836

The information in this article applies to:
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 may receive the following error message:

Run-time error '3251':
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:


    3. 
   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:


    9. 
Dim rs as Recordset
  9. Set the OnCurrent property of the form to the following event procedure:


    10. 
       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:


    11. 
   Name: OldCategory
   Caption: Old Category Name
  11. Set the OnClick property of the command button to the following event procedure:


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



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:


    3. 
   Name: OldCategoryName
   Caption: Old Category Name
  3. Set the OnClick property of the command button to the following event procedure:


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


Additional query words: run time 


Keywords          : kberrmsg kbdta FmsProb MdlRcd QryFixup RltOthr 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999