Microsoft KB Archive/295250

= ACC2002: Error Inserting Row into Multi-Table View or Function =

Article ID: 295250

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295250



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

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you insert a row into a multi-table view or function in an Access project (.adp), you receive the following error message:

You can't update the record because another user or application deleted it or changed the value of its primary key.



CAUSE
There is no defined relationship between the tables.



RESOLUTION
Create a relationship between the two tables. To create a relationship, follow these steps:
 * 1) Open the primary table in Design view.
 * 2) On the View menu, click Relationships.
 * 3) Click New.
 * 4) Select the name of the primary table in the Primary Key Table box.
 * 5) Select the name of the related table in the Foreign Key Table box.
 * 6) Under the Primary Key Table box, select the field(s) that make up the primary key in the primary table.
 * 7) Under the Foreign Key Table box, select the field(s) that make up the foreign key in the related table.
 * 8) On the File menu, click Save.
 * 9) Click Yes when Microsoft Access prompts you to continue.

NOTE: You will receive an error during the save operation if data in the related table violates referential integrity (one or more values in the foreign key field do not exist in the primary key field of the primary table). If you want to avoid this, you can click to clear the Check existing data on creation check box before saving.


 * 1) Quit and then restart Microsoft Access.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
 Open an Access project (.adp) file that is connected to a Microsoft SQL Server 2000 database.  Create a new table with the following fields and properties:   Table: ParentTable ---  Field Name: ParentID Data Type: int Allow Nulls: No (unchecked) Identity: Yes

Field Name: ParentName Data Type: varchar Length: 50 Allow Nulls: Yes (checked)

Table Properties: ParentTable -  PrimaryKey: ParentID  Save the table as ParentTable, and then close it.  Create a new table with the following fields and properties: <pre class="fixed_text">  Table: ChildTable ---  Field Name: ChildID Data Type: int Allow Nulls: No (unchecked) Identity: Yes

Field Name: ChildName Data Type: varchar Length: 50 Allow Nulls: Yes (checked)

Field Name: fParentID Data Type: int Allow Nulls: Yes (checked) Table Properties: ChildTable PrimaryKey: ChildID </li> Save the table as ChildTable, and then close it.</li> On the View menu, point to Database Objects, and then click Queries.</li> Click New.</li> In the New Query dialog box, click Design View, and then click OK.</li> In the Add Table dialog box, click the ParentTable and the ChildTable tables, click Add, and then click Close.</li> Join the two tables on ParentID to fParentID.</li>  Select all fields from both tables in the query design grid. When you are finished, the view's SQL should appear as follows: <pre class="fixed_text">  SELECT ParentTable.ParentID, ParentTable.ParentName, ChildTable.ChildID, ChildTable.ChildName, ChildTable.fParentID FROM ParentTable INNER JOIN ChildTable ON ParentTable.ParentID = ChildTable.fParentID </li> Save the view as ParentChildView .</li> On the Query menu, click Run.</li> Add a new record by editing the ParentName and ChildName fields. The ParentID and ChildID Identity fields should be automatically populated.</li> On the Records menu, click Save Record.</li></ol>

Note that you receive the error message:

You can't update the record because another user or application deleted it or changed the value of its primary key.

Then, the newly inserted record disappears, although a record was successfully added to the child table.

Additional query words: pra inf OfficeErrorMessages 10020

Keywords: kberrmsg kbbug kbnofix KB295250

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.