Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 207836 - Last Review: July 15, 2004 - Revision: 1.1
ACC2000: Error "Operation Is Not Supported" with OldValue Property
This article was previously published under Q207836
Moderate: Requires basic macro, coding, and interoperability skills.
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.
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.
- 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.
- After the auto form is created, click Design View on the View menu.
- On the View menu, click Code.
- In the Declarations section of the module, type the following line:
- 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
- Add a command button to the form and set its properties as follows:
Name: OldCategory
Caption: Old Category Name
- Set the OnClick property of the command button to the following event procedure:
Private Sub OldCategory_Click()
MsgBox rs![CategoryName]
End Sub
- Save the form as frmAutoFix and switch the form to Form view.
- Change the value in the Product Name box.
- Click Old Category Name. Note that the message box displays the
value of the CategoryName field and that no run-time error message
appears.
- Change the value in the Product Name box, and then change the value in the Category box.
- Click Old Category Name. Note that the message box displays the
previous value of the CategoryName field.
Steps to Reproduce Behavior
- Follow steps 1 through 5 in the "Resolution" section of this article.
- Add a command button to the form and set its properties as follows:
Name: OldCategoryName
Caption: Old Category Name
- Set the OnClick property of the command button to the following event procedure:
Private Sub OldCategory_Click()
MsgBox Me![CategoryName].OldValue
End Sub
- Save the form as frmOldValue and switch the form to Form view.
- Change the value in the Product Name box.
- Click Old Category Name. Note that you receive the error message mentioned in the "Symptoms" section.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate