This article was previously published under Q234301
On This Page
SUMMARY
This article shows you how to look up values within data
access pages by using the existing relationships or by using ActiveX Data
Objects (ADO) rather than by using domain functions.
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 data access pages. For example, Internet Explorer does not understand what a
DLookup() function is and 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 within tables that are not bound to the page.
Open the sample database Northwind.mdb or the sample
Microsoft Access project NorthwindCS.adp.
2.
In the Database window, click Pages under Objects, and then click New.
3.
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.
4.
After the page is created, on the View menu, click Design View.
5.
On the View menu, click Field List.
6.
Click the plus signs (+) 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
7.
On the File menu, click Save, and save the page as dapOrdersRel.htm.
8.
On the View menu, click Page View. Note that as you browse through the orders, the appropriate
Company Name appears for each current CustomerID.
Open the sample database Northwind.mdb or the sample Access
project NorthwindCS.adp.
2.
In the Database window, click Pages under Objects, and then click New.
3.
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.
4.
After the page is created, on the View menu, click Design View.
5.
Click the Text Box tool in the toolbox, and add a text box to the right of the
CustomerID text box that already exists.
6.
Select the new text box, and then on the View menu, click Properties.
7.
Click the All tab, and change the ID property to CoName.
8.
Select the label that appears next to the new text box, and
change the InnerText property to Company Name.
9.
On the Tools menu, point to Macro, and then click Microsoft Script Editor.
10.
Using the Script Outline, insert the following script for the Current event of the MSODSC.IMPORTANT: When you create VBScript blocks for MSODSC events, you must add
a parameter to the event name as follows:
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>
<!--
Dim Con
Dim Rst
Set Con = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
' Use the following statement if you are in Northwind.mdb.
'
' The connection string may vary depending upon the location of the
' database and the system database.
'
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " & _
"Files\Microsoft Office\Office\Samples\Northwind.mdb;Jet OLEDB:System " & _
"Database=C:\Program Files\Microsoft Office\Office\System.mdw;User " & _
"ID=Admin;"
' Use the following statement if you are in NorthwindCS.adp.
'
' You must replace "MySQLServer" with the name of your SQL Server
' or MSDE for your data source. You also must replace <username> and <password>
' with the name and password of an account that has permissions to perform this operation on the database.
'
' Con.Open "Provider=MSDataShape.1;Data Source=MySQLServer;" & _
' "User ID=<username>;Password=<strong password>;Initial Catalog=NorthwindCS;Data " & _
' "Provider=SQLOLEDB.1"
Rst.Open "SELECT CompanyName FROM Customers WHERE CustomerID = " & _
chr(39) & Document.All.Item("CustomerID").Value & chr(39), Con
Document.All.Item("CoName").Value = Rst("CompanyName").Value
Rst.Close
Con.Close
-->
</SCRIPT>
NOTE: Your database should be in a Web folder or a network folder
where all users have access.
11.
On the File menu, click Save and save the page as dapOrdersADO.htm.
12.
On the View menu, click View in Browser. If you see a prompt
to save changes, click OK. Note that as you browse through the orders, the appropriate
Company Name appears for each current CustomerID.
Need More Help? Contact a Support professional by Email, Online or Phone.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
Help and Support
Back to the top
