Re: Building Access Applications "Inventory"

This still makes no sense to me why this works as a linked database but if
i copy i get this error
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:
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.

Back to the top

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



.

Relevant Pages

  • Re: More query questions re: keywords
    ... I left out a closing quote mark in the query after the word "Active". ... As far as your database problem, I am shooting in the dark. ... > & it gave me an error message that I needed. ... > table in Excel (to keep it free from any possible corruption) and imported it ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: Export Delimited
    ... Database or objects is read-only. ... T558c Table (this is the name of a query) ... My error message: The Microsoft Jet database engine could not find the ...
    (microsoft.public.access.externaldata)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)