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

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.
Follow steps 8 through 10 of the "Steps to Reproduce Behavior" section later in this article.

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

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

Open an Access project connected to the sample NorthwindCS database on Microsoft Data Engine (MSDE) or Microsoft SQL Server.

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

In the Stored Procedures list, click CustomerOrders, and then click AutoForm on the Insert menu.
On the View menu, click Design View.
Set the UniqueTable property of the form to Orders.
Save the form as CustomerOrders.
Open the CustomerOrders form in Form view.
Move to a new record.
Enter the following values.
Field Value
CustomerID ALFKI
EmployeeID 3
OrderDate 12/31/1999
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.
On the Records menu, click Save Record.

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

REFERENCES

For more information about the ResyncCommand property, click Microsoft Access Help on the Help menu, type ResyncCommand in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about using the ResyncCommand property in a form, click the article number below to view the article in the Microsoft Knowledge Base:

239886 ACC2000: How to Simulate AutoLookup with a Stored Procedure in Access Client/Server