Microsoft KB Archive/202144

= ACC2000: AutoLookup Does Not Work on a Data Access Page =

Article ID: 202144

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202144



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
If you base a data access page on an AutoLookup query, the recordset will not be updateable.



CAUSE
The information on the one-side of the AutoLookup relationship is not updated until the record is committed.



RESOLUTION
To use AutoLookup on a data access page in such a way that the page remains updateable, you must use script. For an example of how to do so, follow these steps:  Open the sample database Northwind.mdb. Create a new data access page based on the Orders table. Be sure to include the OrderID and CustomerID fields.  Add two unbound text boxes to the page, and then set the following properties:   First Label InnerText: Address

First Textbox ID: Address

Second Label InnerText: CityStateZip

Second Textbox ID: CityStateZip  On the Tools menu, point to Macro, and then click Microsoft Script Editor.  Insert the following script directly after the MSODSC control in the HTML Editor, including the Script tag. <SCRIPT language=VBScript> </SCRIPT> </li> On the View menu, point to Other Windows, and then click Script Outline.</li> In the Script Outline pane, expand the CustomerID node, and then double-click onafterupdate.</li>  Add the following line of code to this event: AutoLookup </li> At the bottom of the Microsoft Script Editor, click the Quick View tab, and then click the New Record button on the navigation bar.</li> Type a customer ID into the CustomerID text box (for example, type BERGS ), and then press TAB. Note that the Address and CityStateZip text boxes are automatically populated with the matching customer information.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol> Open the sample database Northwind.mdb.</li> Create a new query that includes both the Customers table and the Orders table.</li> Include all of the fields from the Orders table and the following fields from the Customers table in the query: <ul> CompanyName</li> Address</li> City</li> Region</li> Postal Code</li></ul>

</li> On the View menu, click Datasheet View.</li> Add a new record, selecting a customer name from the Customer column. Note that the fields CompanyName, Address, City, Region, and Postal Code are automatically filled in.</li> On the File menu, click Save, save the query as MyLookup, and then close the query.</li> <li>With the name of this new query (MyLookup) still selected in the Database window, click AutoForm on the Insert menu.</li> <li>Click the new record button at the bottom of the form, and then select an existing Customer from the combo box. Note that the behavior on the form is identical to the behavior in the query. Close the form without saving it.</li> <li>Create a new data access page based on this same query. Note that the data access page is not updateable. You cannot add a new record.</li></ol>

Additional query words: prb auto look up not functioning working function doesn't row fixup fix-up

Keywords: kbdap kbprb KB202144

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.