Microsoft KB Archive/248095

= ACC2000: Error When You Add Record to a Form That Is Based on a Stored Procedure =

Article ID: 248095

Article Last Modified on 12/16/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q248095



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

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



SYMPTOMS
When you add a record to a form in a Microsoft Access project (ADP) file, you receive the 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
The form is based on a stored procedure that contains multiple tables.



RESOLUTION
Set the ResyncCommand property of the form to an SQL statement that selects the same fields as the stored procedure, and parameterize the primary key field of the table that is designated as the Unique Table. To use the ResyncCommand property of the form to work around this problem, follow these steps:  Follow steps 1 through 10 of the "Steps to Reproduce Behavior" section later in this article. Click OK to clear the error message returned by Microsoft Access. Open the form in Design view.  Set the ResyncCommand property of the form to the following SQL statement: SELECT Customers.CompanyName AS CompanyName, Customers.ContactName AS ContactName, Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderID = ?  Save the CustomerOrders form, and then close it. Open the CustomerOrders form in Form view.</li> Follow steps 8 through 10 of the "Steps to Reproduce Behavior" section later in this article.</li></ol>

Note that the record is saved successfully without generating an error.

<div class="moreinformation_section">

MORE INFORMATION
The ResyncCommand property allows you to write an SQL statement that Microsoft SQL Server executes to fix up data from the primary table whenever the user modifies a value in a foreign key field and saves the record. The SQL statement must select the same fields from the primary table that are included in the stored procedure.

The SQL statement must also contain a WHERE clause that passes ? as a parameter marker for the primary key column from the primary table in the stored procedure. This is necessary so that Microsoft SQL Server can resync the form to the same record as the one modified in Microsoft Access.

A general rule is that you should set the ResyncCommand property to the same SQL statement as the stored procedure being used as the record source, minus the WHERE clause being used in the stored procedure. Then add a WHERE clause to the SQL statement that parameterizes the primary key columns from the table designated in the UniqueTable property of the form.

Steps to Reproduce Behavior
<ol> Open an Access project connected to the sample NorthwindCS database on Microsoft Data Engine (MSDE) or Microsoft SQL Server.</li>  Create and save the following stored procedure: Create Procedure CustomerOrders As SELECT Customers.CompanyName AS CompanyName, Customers.ContactName AS ContactName, Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID </li> In the Stored Procedures list, click CustomerOrders, and then click AutoForm on the Insert menu.</li> On the View menu, click Design View.</li> Set the UniqueTable property of the form to Orders.</li> Save the form as CustomerOrders.</li> Open the CustomerOrders form in Form view.</li> Move to a new record.</li> Enter the following values.

Note that the other fields are not be editable because they are not in the Orders table, which is currently designated as the Unique Table of the form.</li> On the Records menu, click Save Record.</li></ol>

Note that you receive the error message mentioned in the "Symptoms" section of this article.

<div class="references_section">