Connecting Data Access Pages Together

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Brett A. Tanzer, Microsoft Access Program Manager
Microsoft Corporation

January 1999

Overview

Microsoft® Access data access pages are documents that allow users to combine the rich features of a dynamic HTML (DHTML) document with data stored in either a Microsoft Access or Microsoft SQL Server™ database. The tools provided in the data access page design environment make it easy for users with varying degrees of experience to create these documents and deploy them on a local file system, a network server, or a Web server inside their organizations.

Access provides an extensive feature set that allows users to add varying degrees of interactivity to their documents. Authors can create pages that are used for entering and updating data or they can create grouped pages that rely on either specific criteria or on the relationships in the database to organize the data in a hierarchical format. Data access pages can also take full advantage of the Microsoft Office Web Components, each of which adds different analytical functionality to a page. Combining different types of pages together can result in powerful solutions that leverage the best of the various Web technologies along with the power of a relational database.

Because data access pages are based on the DHTML Document Object Model (DOM), authors and developers have access to this expansive feature set and can create documents that use the DOM in many ways. This paper focuses on how you can connect data access pages together, either by using the tools provided in the Microsoft Access design environment, or by manipulating the DOM through scripting technologies, such as Microsoft Visual Basic® Scripting Edition (VBScript) and Microsoft JScript®.

Hyperlinks

Hyperlinks are the standard method used to connect Web pages today. Data access pages provide full support for hyperlinks and a complete set of tools for editing and augmenting them inside the design environment.

The data access page toolbox provides three distinct tools for creating hyperlink controls: the Hyperlink tool, the Bound Hyperlink tool, and the Hotspot Image tool.

Hyperlink tool. A hyperlink is a pointer from text in a document to a Web page or other type of file on the World Wide Web or other shared location. You can customize the hyperlink's target location, display text, and ToolTip (the text that is displayed when the mouse pointer rests on the hyperlink). When clicked, the hyperlink navigates to the targeted document.

Bound Hyperlink tool. Bound hyperlinks provide the same functionality at run time as regular hyperlinks do; however the target location and display text may both be populated from a field in the database to which the page is bound.

Hotspot Image tool. Hotspot images provide similar functionality to hyperlinks, except that the display text is replaced with an image that navigates to the targeted document when clicked.

Both the hyperlink and hotspot image controls use the Insert Hyperlink dialog box to make it easy for you to specify the necessary information. Properties for the bound hyperlink control can be set by selecting the control and setting these properties directly through the control's property sheet. General usage for each of these controls is documented fully in the Help system provided with Access.

Filtering Records Through a Hyperlink

This section focuses on the specific set of features in the Insert Hyperlink dialog box that allow you to use a hyperlink to connect data access pages and simultaneously filter the records displayed in the target data access page.

Suppose you have the data access pages shown in Figures 1 and 2.

Figure 1. The Northwind Customers page, which displays customer contact information in a scrollable page populated from the Customers table in the Northwind Traders sample database.

Figure 2. The Northwind Orders page, which displays a continuous list of the orders stored in the Orders table of the Northwind database.

Ideally, users should have some mechanism to locate to a record of interest in the Northwind Customers page and then navigate to a new page that displays only the orders for the selected customer. The Insert Hyperlink dialog box makes it easy to create a hyperlink that serves this purpose.

You can specify filter criteria in the form of an SQL WHERE clause that is passed to the target page as part of the hyperlink. That criteria, known as a server filter, is used by the target page to filter its recordset before the records are retrieved from the database.

To create this type of hyperlink

1. Open the source data access page in Design view.
2. Click the Hyperlink tool in the toolbox, then click the desired location on your page. The Insert Hyperlink dialog box appears.
3. In the dialog box, click Page in this database under Link to (see Figure 3).
4. Select the target page from the ones listed.
5. In the Text to display box, type the text you want to display for this hyperlink.
6. Enter your filter criteria in the form of a WHERE clause. Put the field that contains the value(s) you want Access to substitute from the source page in brackets (for example, CustomerID='[CustomerID]' will yield CustomerID='ALFKI').

Figure 3. Using the Insert Hyperlink dialog box to create a hyperlink that uses a server filter

As shown in Figure 4, when users run the Northwind Customer page and click the hyperlink, they end up with a view that is filtered based on a value in the current document.

Figure 4. The Northwind Orders page filtered by using a value specified on the source page

Notes

• In order for this technique to work, the target document must reside on an http:// server and be accessible through an http:// URL.

• This technique applies to all three control types described earlier.

• Data stored in a Memo field in an .mdb file or in a ntext field in an .adp file can't be used as filter criteria.

• The bracketed field name must be enclosed in single quotation marks if the field has a string data type (that is, a Text field for an .mdb file and a text, char, or varchar field for an .adp file). Quotation marks are not required for fields with numeric or date data types.

• You can add multiple clauses to the WHERE clause. For example:

  CustomerID='[CustomerID]' AND ShipVia=[Shipper]
  

Window Management and Linked Pages

By using hyperlinks, you can build many powerful solutions without writing any scripting code or editing any HTML. However, by creating advanced HTML pages that use frame sets or using the window-management methods provided by the DHTML DOM through script, you can create many more powerful scenarios.

Frame Sets

On the Web today, frame sets are a common user-interface convention that enables you to combine functionality packaged inside individual Web pages into a single view. Although the data access page design environment in Access does not natively support HTML frame set editing, other Office applications, such as Microsoft Word and Microsoft FrontPage®, do. Since data access pages are HTML documents, they can be easily hosted inside any frame set. For details about creating the frame set document itself, see the documentation provided with your HTML frame set editor.

This section discusses the operation of data access pages inside the frame set, and what specific tasks are required to enable your data access pages to function properly inside a framed environment.

Suppose you have a very simple frame set (shown in Figure 5) that contains two frames, one named Left and one named Right.

Figure 5. Simple frame set

This frame set is defined by the following HTML code:

<HTML>
<HEAD>
<TITLE>Northwind Customer Information</TITLE>
</HEAD>
<FRAMESET COLS=300,* >
<FRAME NAME=LEFT src="Northwind Customers.htm" scrolling=yes>
<FRAME NAME=RIGHT src="Northwind Orders.htm " scrolling=no>
</FRAMESET>
</HTML>

Note that the SRC attribute of each frame specifies the data access page to load, and that each frame has a name specified. Suppose you want to enable users to locate a particular customer in the Northwind Customers data access page hosted in the frame named Left, then allow them to click a hyperlink that opens the Northwind Orders data access page in the frame named Right, filtering the data appropriately, as shown in Figure 6.

Figure 6. Data access pages in a frame set that displays filtered information passed between frames through a hyperlink

To build this type of page

1. Create a frame set to host your data access pages.
2. In Access, create both the source and target data access pages by using data access page Design view.
3. In the source page, create a hyperlink designed to pass filter criteria to the target page, as described earlier.
4. Select the hyperlink control, and open its property sheet.
5. Click the Hyperlink tab of the property sheet.
6. In the Target property box, enter the name of the frame in the frame set that you want the target document to load in (for example, "Right" in the frame set shown in Figure 5).
7. Save your pages.
8. Open your frame set in Microsoft Internet Explorer 5.0. Click the hyperlink in the source document and confirm that the results are as expected.

In addition to using the name of a frame inside the frame set document, Internet Explorer allows you to choose from a list of predefined values for the Target property that define additional behavior. Full details can be found in the Internet Client SDK at http://support.microsoft.com/kb/177877. Table 1 provides a summary of these values.

Table 1. Possible values for Target property of a hyperlink

Notes

• As stated earlier, data access page Design view in Microsoft Access does not support frame set editing.
• Users cannot open a document that contains a frame set, or an embedded frame, inside the Access environment in Design or Page view.
• Documents that pass filter information between frame sets must do so through an http:// URL.
• This technique works for documents hosted in frame sets or documents that use embedded frames (created by using the <IFRAME> tag).

Window-Management Methods

You may want to create a multiwindowed environment instead of a frame set for a variety of reasons, such as wanting to present a more traditional UI, wanting to use a modal window to enforce application flow, or wanting to use a modeless window that is always available as part of the application. You can accomplish all of these tasks by using several scriptable methods exposed by the DOM. This section covers those methods and their usage at a high level. Full details and samples can be found in the Internet Explorer Client SDK at http://support.microsoft.com/kb/177877.

The DOM supports three methods that allow you to manage windows as part of your application:

• open method—Opens a new window and loads the document specified in the sURL parameter. Returns a reference to the new window object. Use this reference to manipulate properties and methods of the new window object.
• showModalDialog method—Creates a dialog box that displays the HTML document specified in the sURL parameter. The dialog box displayed is modal, meaning it retains the input focus until the user closes it.
• showModelessDialog method—Creates a modeless dialog box that is layered in front of the browser window or the HTML application. The modeless dialog box is displayed even when the user switches the input focus to the browser or HTML application window.

open method

You traditionally use the open method to open a new window that displays a new document when you want to manipulate properties, methods, and events in the target document from script residing in the source document. Of the three window-management methods, this is the only one that will allow you to reference objects in the target document from "local" script.

This method is particularly useful when you would like to dynamically set server filters, sort orders, and so on, in the target page.

The following script opens the Northwind Orders page from the Northwind Customers page and sets the field used in the sort order to ShipDate.

<SCRIPT Language=VBScript>
<!--
Sub OpenPage()
   Dim newWin
   Dim myDSC

   Set newWin=window.open("Northwind Orders.htm",null, _
      "height=200;width=200;status=no,toolbar=no,menubar=no," _
      & "location=no")

   Set myDSC=newWin.document.all.MSODSC
   myDSC.defaultRecordset.sort="ShipDate"
End Sub
-->
</SCRIPT>

showModalDialog and showModelessDialog methods

The showModalDialog and showModelessDialog methods are used to open a dialog box window and affect its modality. This can be useful when you want to force the user of your application to take an action before continuing or, conversely, if you want to open a dialog box that is always available. These methods allow you to control the appearance and behavior of the dialog box at a very granular level. These methods do not allow you to access objects in the dialog box from script in the document that opened the dialog box. You can, however, set the returnValue property for the dialog box, which, in turn, is passed out of the call that invoked the dialog box.

In the following example (shown in Figure 7), a page that displays Orders and OrderDetails (the source page) opens a modal dialog box (the target page) that is bound to the OrderDetails table. The target page is opened in data-entry mode and is titled Products.

Figure 7. A data access page connected to another data access page that's presented in a modal dialog box

The script in the source page does the following:

• Opens a modal dialog box that contains the data access page Products.
• Passes in the primary key value (OrderID) in the vArguments parameter of the showModalDialog method call. This value is used in the foreign key field when individual items are added to the selected customer order in the source page.
• Inspects the return value from the showModalDialog call to see if it equals True. If so, it refreshes the page to update the visible records.

The script in the target page does the following:

• When the user adds line items and clicks the Order It button, the script uses methods of the data access page to save the record and navigate to a new one. Note that adding an item sets the returnValue property for the window to True. The foreign key (OrderID) value used when adding this record is accessed by using the dialogArguments property of the window object in the dialog box.
• When the user clicks the Close button, the script executes the close method of the window object.

The script used in the source and target pages is as follows:

• Source page (Orders and Order Details)

  <SCRIPT id=clientEventHandlersVBS language=vbscript>
  <!--
  Sub Command0_onclick
     newDlg=window.showModalDialog _
        ("http://yourservername/nwindorders/Products.htm", _
        document.all.OrderId.value, _
        "dialogWidth:22;dialogHeight:22;center:yes")
  
     If newDlg = True Then
        window.location.reload()
     End If
  End Sub
  -->
  </SCRIPT>
  

• Target page (Products)

  <SCRIPT id=clientEventHandlersVBS language=vbscript>
  <!--
  Sub Command0_onclick
     window.OrderID.value = window.dialogArguments
     window.MSODSC.CurrentSection.DataPage.Save()
     window.MSODSC.CurrentSection.DataPage.NewRecord()
     window.returnValue = True
  End Sub
  
  Sub Command1_onclick
     window.close()
  End Sub
  -->
  </SCRIPT>
  

Where to Go from Here

We have examined only a few of the techniques you can use to create rich and interesting applications with data access pages. Extending these documents with script, frames, and dialog boxes are only some of the options available to you. For more information about extending data access pages, see the following resources:

• Internet Explorer 4.01 Refresh of the Internet Client SDKhttp://support.microsoft.com/kb/177877.
• "Programming Data Access Pages."

---------------------------------------------

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, this paper should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, FrontPage, JScript, Microsoft Press, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.