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

• Microsoft Access 2000

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

1. Open an Access project connected to the sample NorthwindCS database on MSDE or Microsoft SQL Server.



2. 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%' 

3. In the Stored Procedures list, click AutoLookupTest, and then click AutoForm on the Insert menu.



4. Save the form as AutoLookUpTest.



5. Open the form in Design view, and set the UniqueTable property of the form to Orders.



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



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



8. 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 WHEREOrders.CustomerID = ? 

9. Save the form, and then open it in Form view.



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



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

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

REFERENCES

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

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

Additional query words:

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto