Microsoft KB Archive/291091

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

Article ID: 291091

Article Last Modified on 3/9/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q291091





Moderate: Requires basic macro, coding, and interoperability skills.

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



SYMPTOMS
When you add a new record to a subform in a Microsoft Access project (ADP), 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.



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  where  is the name of a table in the database.  The table has an Identity field set as its primary key.

<div class="resolution_section">

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

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in Microsoft Access 2002.

<div class="moreinformation_section">

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.

<ol> 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.</li> Close the Main Switchboard form if it appears.</li> On the View menu, point to Database Objects, and then click Forms.</li> In the Database window, click the Product List form, and then click Design.</li> On the View menu, click Properties to view the property sheet of the form.</li>  Set the following form properties as indicated: <pre class="fixed_text">  Record Source: SELECT * FROM Products Allow Edits: Yes Allow Additions: Yes Navigation Buttons: Yes </li> On the File menu, click Save, and then close the form.</li> In the Database window, click the Categories form, and then click Open.</li> Click the subform, and then browse to a new record.</li> Enter data into the ProductName and UnitPrice fields.</li> 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.

</li></ol>

Additional query words: pra OfficeErrorMessages 10021

Keywords: kberrmsg kbtshoot kbprb kbpending KB291091

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.