Microsoft KB Archive/287627

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

Article ID: 287627

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q287627





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

Advanced: Requires expert coding, interoperability, and multiuser skills.



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

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.



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



RESOLUTION
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: <ol> Open the sample project NorthwindCS.adp.</li> Press CRTL+G to open the Immediate window.</li> Type the following statement in the Immediate window to create the view:

CurrentProject.Connection.Execute &quot;CREATE VIEW vwProductsView AS SELECT ProductID, ProductName, UnitPrice FROM Products WITH CHECK OPTION&quot;, False,False

</li> Change the form's RecordSource property to vwProductsView.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

MORE INFORMATION
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 &quot;Select @@IDENTITY&quot; 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 &quot;Select @@IDENTITY&quot; 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.

<div class="references_section">