ACC2000: Error Message "The Data Was Added to the Database But the Data Won't Be Displayed..." When You Add Records to a Subform

When you add new records to a subform, you receive the following error message, but the records is still added to the underlying table:

You receive this error message if all of the following conditions are true.

• You are using an Access project (ADP) with Microsoft SQL Server 7.0 as the back-end database
• The subform's RecordSource property is a simple SELECT statement such as:
  Select * from Products
• The table has an Identity field as Primary Key to act as an AutoNumber.
• You are adding new records to the subform.

Create a view by using Access, the OSQL Utility, or Enterprise Manager. Then change the form's record source to use the view instead of the SELECT statement. For an example of how to do this in Access, follow these steps:

1. Open the sample project NorthwindCS.adp.
2. Press CRTL+G to open the Immediate window.
3. Type the following statement in the Immediate window to create the view:
   CurrentProject.Connection.Execute "CREATE VIEW vwProductsView AS SELECT ProductID, ProductName, UnitPrice FROM Products WITH CHECK OPTION", False,False
4. Change the form's RecordSource property to vwProductsView.

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

When the new record is inserted, Access needs to reselect the same record back to ensure it is added to the table. When the back-end is a Microsoft SQL Server 2000 database, Access correctly calls "Select @@IDENTITY" to verify the value of the PrimayKey (PK) of the record just added. The PK value returned is then used to reselect the newly inserted record.

If the back-end is SQL Server 7.0, the "Select @@IDENTITY" is not called. The PK value searched for is 0 (zero), and therefore the newly inserted record is not found.

Steps to Reproduce the Behavior

1. Open the sample project NorthwindCS.adp.
2. On the File menu, click Connection. Change the connection to the Northwind database on SQL Server 7.0.
3. Create a main form that is based on the Categories table.
4. Create a subform that is based on the Products table.
5. Change subform's RecordSource property to Select * from Products.
6. Open the main form in Form view, and then add a new record to the subform.

For additional information about a similar error message, click the article number below to view the article in the Microsoft Knowledge Base: