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

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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

Open the form again in Design view, and set the ResyncCommand property as follows:

   SELECT Customers.CompanyName,Customers.ContactName, Customers.Address,
   Orders.* FROM Customers INNER JOIN ORDERS ON Customers.CustomerID = 
   Orders.CustomerID WHERE Orders.OrderID = ?

Save the form, and then open it in Form view.
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).
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.

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:

   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 = ?

↑ Back to the top

References

For additional information about AutoLookup, click the article number below to view the article in the Microsoft Knowledge Base:

209672� ACC2000: Example of Row Fix-Up in the Northwind Orders Form

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

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

↑ Back to the top