Microsoft KB Archive/239886

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

Article ID: 239886

Article Last Modified on 12/12/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q239886



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

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



SUMMARY
AutoLookup (also known as Row Fix-Up) is a process in which Microsoft Access automatically refreshes data from the primary table of a one-to-many relationship in a multiple-table query when the user edits the foreign key field. You can find an example of AutoLookup in the RecordSource property of the Orders form of the sample database Northwind.mdb.

The Microsoft Jet database engine performs AutoLookup automatically as long as the join field from the "one" side is uniquely indexed, and the user is modifying the foreign key field.

However, if you use an Access project (.adp), it is important to note that Microsoft SQL Server and Microsoft Data Engine (MSDE) do not perform AutoLookup when the user modifies the foreign key field in a multiple-table stored procedure.

To approximate the AutoLookup functionality in an Access project, you can use the ResyncCommand property of a form bound to a multiple-table stored procedure. Note that even when you use the ResyncCommand property to simulate AutoLookup, the values from the primary table in the stored procedure is not refreshed until the record is committed. This behavior differs from the update semantics of the Jet database engine, which refreshes values as soon as the foreign key field is modified.

This article demonstrates how to use the ResyncCommand property to simulate AutoLookup when you use a multiple-table stored procedure with a Microsoft Access form.



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.

Creating a Form That Duplicates the AutoLookup Behavior
 Open an Access project connected to the sample NorthwindCS database on MSDE or Microsoft SQL Server.  Create and save the following stored procedure:   CREATE PROCEDURE AutoLookUpTest As  SELECT Customers.CompanyName, Customers.ContactName, Customers.Address, Orders.* FROM Customers INNER JOIN ORDERS ON     Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID LIKE 'a%'  In the Stored Procedures list, click AutoLookupTest, and then click AutoForm on the Insert menu. Save the form as AutoLookUpTest. Open the form in Design view, and set the UniqueTable property of the form to Orders. Open the form in Form view, and change the value in the CustomerID field to "ALFKI", "AROUT", or "ANTON" (Modify the value so that it is different from what is currently displayed).</li> Move to the next record in your form, and then move back to the record that you just modified. Note that the Company Name, Contact Name, and Address text boxes still display information for the CustomerID that you changed, not the information for the new CustomerID that you entered.</li>  Open the form again in Design view, and set the ResyncCommand property as follows: <pre class="fixed_text">  SELECT Customers.CompanyName,Customers.ContactName, Customers.Address, Orders.* FROM Customers INNER JOIN ORDERS ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderID = ? </li> Save the form, and then open it in Form view.</li> Change the value in the CustomerID field to "ALFKI", "AROUT", or "ANTON" (Modify the value so that it is different from what is currently displayed).</li> Move to the next record in your form (you are prompted with a message box), and then move back to the record that you just modified. Note that the Company Name, Contact Name, and Address text boxes now display information related to the CustomerID that you just entered.</li></ol>

Multiple-Field Primary Keys
If the primary key of the "foreign" or "many side" table is made up of multiple fields, you must use the AND clause and parameterize each field within the WHERE clause of the ResyncCommand property. For example, if the foreign table contains a three-field primary key, the SQL statement for the ResyncCommand property would look similar to the following: <pre class="fixed_text">  SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.* FROM Table1 INNER JOIN Table2 ON      Table1.ID = Table2.ForeignID WHERE Table2.PrimaryKeyField1 = ? AND Table2.PrimaryKeyField2 = ? AND Table2.PrimaryKeyField3 = ?

<div class="references_section">