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


The information in this article applies to:

  • Microsoft Access 2000
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:
  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.

STATUS

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

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

REFERENCES

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

213844 ACC2000: WITH CHECK OPTION Clause Is Not Enforced Within a View

Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbbug KbClientServer kberrmsg kbpending KB287627
©2004 Microsoft Corporation. All rights reserved.