Microsoft KB Archive/285119

= HOW TO: Look Up Values in a Data Access Page in Access 2002 =

Article ID: 285119

Article Last Modified on 9/27/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q285119



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 234301.

IN THIS TASK
SUMMARY
 * Using Existing Relationships to Look up Data
 * Using ADO Recordsets to Look up Data



SUMMARY
In most Microsoft Access objects, such as forms and reports, you can use a domain function to easily retrieve data from sources other than the record source of the object.

Domain functions are intrinsic functions built into Microsoft Access. Because data access pages are ultimately viewed by using a Web browser (such as Microsoft Internet Explorer), domain functions do not work in a data access page. For example, Internet Explorer does not understand what a DLookup function is, and therefore does not know how to execute the function from within a page. For this reason, you must use a different approach to look up data in tables that are not bound to the page.

This article shows you how to look up values within a data access page by using the existing relationships or by using ActiveX Data Objects (ADO), rather than by using domain functions.

back to the top

Using Existing Relationships to Look up Data
 Open the sample database Northwind.mdb or the sample Microsoft Access project NorthwindCS.adp. In the Database window, click Pages under Objects, and then click New. In the New Data Access Page dialog box, click AutoPage: Columnar, click Orders in the Choose the table or query (or view) where the object's data comes from box, and then click OK. After the page is created, click Design View on the View menu. If the field List is not displayed, click Field List on the View menu.  Click the plus sign (+) next to Tables until you see a tree structure like the following, and then drag the CompanyName field to the page:   Tables Orders Related Tables Customers </li> On the File menu, click Save, and then save the page as dapOrdersRel.htm.</li> On the View menu, click Page View.</li></ol>

Note that as you browse through the orders, the appropriate Company Name appears for each current customer ID.

back to the top

Using ADO Recordsets to Look up Data
<ol> Open the sample database Northwind.mdb or the sample Access project NorthwindCS.adp.</li> In the Database window, click Pages under Objects, and then click New.</li> In the New Data Access Page box, click AutoPage: Columnar, click Orders in the Choose the table or query (or view) where the object's data comes from box, and then click OK.</li> After the page is created, click Design View on the View menu.</li> Click the Text Box tool in the toolbox, and then add a text box to the right of the CustomerID text box that already exists.</li> Click in the new text box, and then on the View menu, click Properties.</li> Click the All tab, and then change the ID property to CoName .</li> Click the label that appears next to the new text box, and then change the InnerText property to Company Name.</li> On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> In the Object list, click MSODSC, and in the Event list, click Current.</li> In the Property window, scroll down to the defaultClientScript property, and click VBScript in the list.</li>  Insert the following script:IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name as follows:

<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>

The <I>oEventInfo</I> parameter returns specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script will not work without it.

<SCRIPT Event=Current(oEventInfo) For=MSODSC Language=VBScript>

</SCRIPT> NOTE: Your database should be in a Web folder or a network folder where all users have access.

</li> On the File menu, click Save, and then save the page as dapOrdersADO.htm.</li> Open the page in your browser.</li></ol>

Note that as you browse through the orders, the appropriate company name appears for each current customer ID.

back to the top

Additional query words: OfficeKBHowTo inf

Keywords: kbdap kbdapscript kbhowto kbhowtomaster KB285119

-

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

© Microsoft Corporation. All rights reserved.