You receive an error message when you add a record to a subform in an Access 2002 Project (291091)

CAUSE

This behavior occurs when all of the following conditions are true:

The back-end database is stored on Microsoft SQL Server 7.0.
The RecordSource property of the subform is set to an SQL Select statement that selects fields directly from the underlying table, for example,
```
   SELECT * FROM <Products>
						
```
where <Products> is the name of a table in the database.
The table has an Identity field set as its primary key.

RESOLUTION

There are two possible workarounds for this problem.

Set the RecordSource Property of the Subform to a Table or a View

This behavior does not occur if the RecordSource property of the subform is set to the actual name of the underlying table or to a view that selects the proper fields from the underlying table. You can also set the RecordSource property to an SQL SELECT statement that selects all the fields from a view that is based on the underlying table.

Upgrade to Microsoft SQL Server 2000

This behavior does not occur if the database is stored on Microsoft SQL Server 2000 (including SQL Server 2000 Desktop Engine). You can avoid the problem by upgrading your SQL Server to Microsoft SQL Server 2000.

For more information about the benefits of upgrading to Microsoft SQL Server 2000, see the following Microsoft Web site:

http://www.microsoft.com/technet/community/events/sql2000/tnt1-04.mspx

MORE INFORMATION

When inserting records into a Microsoft SQL Server database from an ADP, Microsoft Access tries to reselect the newly inserted record to verify that it was inserted correctly. To do this, Microsoft Access calls the @@IDENTITY function to determine the Primary Key value of the newly inserted record so that it knows which record to retrieve. Microsoft Access then reselects the record based on that value.

If the database is stored on Microsoft SQL Server 2000, the value of the Identity column is returned to Microsoft Access, and Microsoft Access successfully reselects the record based on that value. However, if the database is stored on Microsoft SQL Server 7.0 or MSDE 1.0, the value of the Identity column is not returned to Microsoft Access, and Microsoft Access fails to reselect the record. When Microsoft Access cannot locate the newly inserted record, it warns you that even though the record was successfully added, it cannot be displayed within the form.

Steps to Reproduce the Behavior

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.

Open the sample project NorthwindCS.adp, and verify that it is connected to the NorthwindCS database on Microsoft SQL Server 7.0 or Microsoft Data Engine (MSDE) 1.0.
Close the Main Switchboard form if it appears.
On the View menu, point to Database Objects, and then click Forms.
In the Database window, click the Product List form, and then click Design.
On the View menu, click Properties to view the property sheet of the form.

Set the following form properties as indicated:

   Record Source: SELECT * FROM Products
   Allow Edits: Yes
   Allow Additions: Yes
   Navigation Buttons: Yes

On the File menu, click Save, and then close the form.
In the Database window, click the Categories form, and then click Open.
Click the subform, and then browse to a new record.
Enter data into the ProductName and UnitPrice fields.
On the Records menu, click Save Record.

Note that you receive the following error message:
The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.