When you try to insert a record in a multi-table view, a stored procedure, or a function in a Microsoft Access project (.adp) file, you receive the following error message: When you click Drop Changes in the dialog box, the record disappears. However, the values from the child record in the join are saved with an unexpected foreign key.

This behavior occurs when the following conditions are true:

• The primary key field of the primary table in the query is an identity field.
• The query restricts the records that are returned to exclude some records from the primary table.

To resolve this problem, obtain the latest service pack for Microsoft Office XP. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

There are two additional resolutions for this problem.

Use a Linked Main Form and Subform

The recommended solution for this problem is to use a linked main form and subform to enter data into the related tables. This allows you to enter records into both tables from one location without using a query, and without having to modify the structure of the underlying tables. To create a main form with a linked subform, follow these steps:

1. Create a new form that is based on the related (child) table that is used in the query and include all fields on the form.
2. Save the form, and then close it.
3. Create a new form that is based on the primary table that is used in the query and include all fields on the form.
4. In the Database window, drag the form that you saved in step 2 onto the main form to create a subform.
5. View the properties of the subform and set its LinkChildFields and LinkMasterFields properties to the name of the field or fields that are used to link the tables.

Remove the Identity Attribute from the Primary Table

A second solution to this problem is to set the Identity property of the primary key in the primary table to No. This prevents the error from occurring when you enter data directly into the query; however, you will have to manually enter the primary key and foreign key values directly into the query when you insert records. To set the Identity property of the field to No, follow these steps:

1. Open the primary table in Design view.
2. Click on the primary key field to view its properties.
3. Set the Identity property of the field to No.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
This problem was first corrected in Microsoft Office XP Service Pack 1.

If the query is restricting some records from the primary table, and the primary table's join field is an identity column, Microsoft Access does not retrieve the next valid value for the identity column. Microsoft Access attempts to insert a value one greater than the maximum identity value in the query's resultset. If that value has already been used, Microsoft Access returns a write conflict error when the related table is updated.

Steps to Reproduce the Behavior

1. CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.
   
   
2. Open the sample project NorthwindCS.adp.
3. Close the Main Switchboard form if it appears.
4. On the View menu, point to Database Objects, and then click Queries.
5. Click New.
6. In the New Query dialog box, click Design View, and then click OK.
7. In the Add Table dialog box, click the Categories and the Products tables, click Add, and then click Close.
8. On the View menu, point to Show Panes, and then click SQL.
9. Type or paste the following SQL statement into the SQL pane:

   SELECT 
      Categories.CategoryID, 
      Categories.CategoryName, 
      Products.ProductID, 
      Products.ProductName, 
      Products.CategoryID AS fCategoryID
   FROM         
      Categories INNER JOIN Products
   ON 
   Categories.CategoryID = Products.CategoryID
   					

10. On the File menu, click Save, and then save the view as CatProducts.
11. On the Query menu, click Run. Note that the view is populated with data where the CategoryID fields are equal.
12. On the Records menu, click Data Entry.
13. Enter a new record by typing values into the CategoryName and the ProductName fields. Note that the CategoryID and the ProductID identity fields are populated automatically with the value 1, which has previously been used.
14. On the Records menu, click Save Record.

Note that you receive the error message that is mentioned in the "Symptoms" section of this article.