ACC2000: AutoLookup Does Not Work on a Data Access Page (202144)

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:

1. Open the sample database Northwind.mdb.
2. Create a new data access page based on the Orders table. Be sure to include the OrderID and CustomerID fields.
3. 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
   					

4. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
5. Insert the following script directly after the MSODSC control in the HTML Editor, including the Script tag.

   <SCRIPT language=VBScript>
    <!--
     Sub AutoLookup
      Dim x
   
      x = MSODSC.DefaultRecordset.Fields.Item("CustomerID") 
   
      'Check to make sure the CustomerID field is not null.
      If IsNull(x) = False Then
       'Select all Customer records with matching
       'CustomerID values and create a  recordset
       Dim rs
       Dim conn
       Dim sql
   
       Set conn = MSODSC.Connection
       Set rs = CreateObject("ADODB.recordset")
   
       sql = "SELECT Customers.CustomerID, Customers.CompanyName, "
       sql = sql & "Customers.Address, Customers.City, Customers.Region, "
       sql = sql & "Customers.PostalCode "
       sql = sql & "FROM Customers "
       sql = sql & "WHERE (((Customers.CustomerID)='" & x & "'));"
   
       'Open the recordset.
       rs.Open sql,conn,3,3
   
       'Set the Address1 and Address2 textboxes to the
       'appropriate values for the current record.
       Document.All.Item("Address").Value = rs("Address")
       Document.All.Item("CityStateZip").Value = rs("City") & _
           ", " & rs("Region") & " " & rs("PostalCode")
   
       'Close the current recordset.
       rs.Close
      Else
       'Set the Address1 and Address2 textboxes to empty strings.
       Document.All.Item("Address").Value = ""
       Document.All.Item("CityStateZip").Value = ""
      End If
     End Sub
    -->
   </SCRIPT>
   					

6. On the View menu, point to Other Windows, and then click Script Outline.
7. In the Script Outline pane, expand the CustomerID node, and then double-click onafterupdate.
8. Add the following line of code to this event:

   AutoLookup
   					

9. At the bottom of the Microsoft Script Editor, click the Quick View tab, and then click the New Record button on the navigation bar.
10. 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.