Programming Data Access Pages
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.
Roy Leban, Access Program Management
Microsoft Corporation
January 1999
Contents
Introduction Getting Started Data Access Page Events Data Access Page Objects Dealing with Data Taking Control Tips and Tricks Next Steps
Introduction
Use the power of Visual Basic®, Scripting Edition (VBScript) to add interactivity to your data access pages.
With data access pages in Microsoft Access 2000, you can create Web views of your database in minutes. When you want to customize your page beyond what the data access page designer allows, add interactive buttons, or simply add a little pizzazz, you'll find that it's easy to add scripts or edit your HTML by using the Microsoft Script Editor, which is integrated into Access.
For example, you can add scripts that perform sophisticated actions based on the data currently being shown, use transitions to create multimedia effects, or create pages that harness the power of the Microsoft Office Spreadsheet, Chart, or PivotTable® components.
What This Document Is
This document highlights how to use the Script Editor and some of the key things you'll need to know in order to add scripts to your data access pages. Script examples are provided for some of the script functions in data access pages.
What This Document Isn't
This document is intended to help people who know Microsoft Visual Basic®, Visual Basic for Applications (VBA), Microsoft Visual Basic Scripting Edition (VBScript), or Microsoft JScript® to add scripts to data access pages. If you're just getting started with programming, or you are new to scripting Web pages, you'll probably want to start with a good book about VBScript. Similarly, if you want to use some of the more sophisticated HTML or dynamic HTML (DHTML) features, you'll want to pick up a book about HTML and DHTML. And, you'll probably want to refer to the definition of the object model for Internet Explorer, in Chapter Three of Inside Microsoft Visual Basic, Scripting Edition, available from the Partial Books node in the MSDN Library table of contents.
How to Read This Document
This document starts off with a basic description of the Script Editor, VBScript, and debugging. The most important sections are divided into two topics—discussing data access page events and objects. Since the subjects are somewhat intertwined, you may want to skim both sections before reading them in depth. The following sections deal with data connections, data access page-specific controls and the Microsoft Office Web Components. Finally, there are some tips and tricks that you'll be able to make use of once you're up and running. The last section, "Next Steps," contains cross-references to other information you may find useful.
Getting Started
What Is the Microsoft Script Editor?
The Microsoft Script Editor is an HTML and script editing system and debugger. You can use the Script Editor to edit any data access page that you create in Access 2000. To open the Script Editor, simply right-click any object in a page, then click Microsoft Script Editor on the shortcut menu. The Script Editor will be launched and will display the HTML source code for your page:
Figure 1. Microsoft Script Editor
The standard windows in the Script Editor are as follows:
| Window | Description |
|---|---|
| HTML Outline | Shows an outline of the HTML tags in your document. |
| Toolbox | Shows the HTML-intrinsic objects and a few special tags that you can insert in your document through drag and drop. |
| Script Outline | Shows an outline of the scriptable objects in your document. Clicking the + icon next to an object shows the events that are available for that control. |
| Document | Shows syntax-colored HTML for your document. You can click the Quick View tab at the bottom to view the page within the Script Editor. The Design tab does not apply to data access pages. |
| Project Explorer | Shows all the data access pages you have open in the Script Editor. |
| Properties | Shows properties for the document or currently selected object. |
**Note **The HTML Outline, Toolbox, and Script Outline windows are normally shown together in a tabbed window.
At the top of your source code, you will see an Access object icon:
This icon represents the Data Source control (MSODSC) for your data access page, which will be described in detail below.
**Caution **It is important that you do not delete or move this object.
Creating an Event Handler
Follow these simple steps to create a button and add an event handler:
In Microsoft Access, create or open a data access page in Design view.
Click the Command Button tool in the toolbox, then click the place on your page where you want the button to appear. If you have wizards turned on in the toolbox, click Cancel when the Command Button Wizard appears. The wizard is useful when you want to use one of the predefined button types, but that's not what we're doing here.
Figure 2. Creating a button
Rename the button to whatever you want.
Right-click the button and then click Microsoft Script Editor on the shortcut menu (or press ALT+SHIFT+F11).
Find the newly created button in the Script Outline window that appears on the left.
Figure 3. Script Outline
Click the + icon to show the events for buttons.
Double-click the Onclick event.
Figure 4. Onclick event
The Script Editor will insert an event handler for the event and position the cursor in the middle:
<SCRIPT for=Command0 event=onclick language=vbscript> <!-- --> </SCRIPT>Add code to the handler. For example:
<SCRIPT for=Command0 event=onclick language=vbscript> <!-- MsgBox "here" --> </SCRIPT>Click Access in the Windows® taskbar and switch the page to Page view.
Click the button.
That's it! You've created your first script.
Manipulating an Object from a Script
Follow these simple steps to add a field and create some code that manipulates the field.
Switch the page to Design View.
Drag a field from the field list to the fixed-positioning section grid.
Switch back to the Script Editor by clicking the Script Editor on the Windows taskbar.
Change the handler you inserted above to:
<SCRIPT for=Command0 event=onclick language=vbscript> fieldname.value = somevalue </SCRIPT>Whenever you see text in italics in a code sample, it means that you should substitute whatever name or value is appropriate. In this case, substitute your field name and a desired value where fieldname and somevalue appear. For example:
<SCRIPT for=Command0 event=onclick language=vbscript> CityName.value = "Kalamazoo" </SCRIPT>Click Access in the Windows taskbar and switch the page to Page view.
Click the button and notice that the field changes. If you close the data access page and view the table, you'll see that the value has changed there as well.
We'll go into more details about manipulating fields and other objects later in this document.
**Important **You don't need to save your page before switching between the Script Editor and Access. Any changes you make in either the Script Editor or Access will automatically appear in the other application when you switch between them. However, if you're viewing them side-by-side, you'll need to activate each application in order for the changes you've made in the other application to appear.
VBScript vs. Visual Basic and VBA
VBScript is very similar to Visual Basic and VBA, but there are some important differences. This is particularly important if you copy code from Visual Basic or VBA to your data access page. For example:
- There are no object constants in VBScript. In VBA, objects can have associated constants. These aren't available in VBScript. Whenever you need an object constant value, you must either use a literal value or you must declare a constant yourself with the appropriate value (by using
Const name = value). To find an object constant value, look in the Microsoft Script Editor Object Browser. The Office Web Components contain a constants object that can be used for this purpose (for example,chartN.constants.chChartTypePiehas the value18). - Dim statements can't have types.
- Argument lists in Sub and Function procedure definitions can't have types.
- Many of the built-in functions are not available. To find the functions that are available, use the Script Editor Object Browser to view the GlobalObj interface of the Microsoft VBScript Global library.
A good guideline to follow is that VBScript is a strict subset of VBA. If something doesn't work in VBA, it won't work in VBScript. The opposite is not true—just because something is in VBA, that doesn't mean it's in VBScript.
VBScript vs. JScript
If you're used to using VBA when you're creating an Access application, then you'll probably want to use VBScript in your data access page. VBScript is the preferred language for several reasons:
- The Visual Basic family of languages was designed with the goal of being easy to understand.
- VBScript is a subset of VBA, so knowledge of Visual Basic or VBA can be immediately applied to VBScript.
- The Visual Basic family of products has over 3 million developers and hundreds of books have been written about these products.
- The ability to format dates, numbers, and currency is built into the VBScript language.
- VBScript has better Component Object Model (COM) integration. Many COM objects return information in the form of a collection. VBScript has built-in support for iterating through collections.
- VBScript is case-insensitive, which eliminates errors caused by incorrect capitalization.
However, if you've been using Microsoft JScript or Netscape JavaScript in your Web pages, or if you're a C programmer who's more comfortable with JScript or JavaScript syntax, feel free to use them in your data access pages.
If you want to use JScript instead of VBScript, you need to change the defaultClientScript property setting in the Script Editor document Properties window from VBScript to JavaScript. If you use JScript, remember that it is case-sensitive.
**Caution **The version of the Script Editor included with Access 2000 does not insert arguments for event handlers. This means that VBScript handlers for events that take arguments will not be created properly and your handler will not be called as expected. To fix this problem, you'll need to add the arguments by hand. For example, if you insert a handler for the Current event of the Data Source control, you'll see:
<SCRIPT for=MSODSC event=Current language=vbscript>
Since the Current event takes an argument that provides a DSCEventInfo structure, you'll need to edit the handler so it reads:
<SCRIPT for=MSODSC event=Current(info) language=vbscript>
The Script Editor Object Browser
The Script Editor Object Browser is similar to the Visual Basic Editor Object Browser you're used to.
Figure 5. The Script Editor Object Browser
To open the Object Browser, press F2. The first time you open the Object Browser, you'll need to choose which object libraries appear in it. To do this, click the Select Current Packages/Libraries button:
Next, click the Add button and select the object libraries you want to appear in the Object Browser. At a minimum, you'll want to select the following:
- Microsoft HTML Object Library
- Microsoft Office Web Components 9.0
- Microsoft Office Web Components Function Library
- Microsoft VBScript Global
You may also find the following libraries useful:
- Microsoft Jet and Replication Objects 2.1 Library
- Microsoft ActiveX® Data Objects Recordset 2.1 Library
**Caution **You must carefully use the information provided by the Script Editor Object Browser and the popup prompts that appear when you type a period ( .) in an expression. The Script Editor does not indicate that some functions can return either a single object or an array of objects. In such cases, the Script Editor will either show the return type as a generic object or as a single object, even though the return type could be an array of objects.
Debugging a Script
To debug your scripts, you need to turn on the debugging options in the Script Editor. On the Tools menu, click Options, click Debugging, and select the Just-In-Timedebugger, Just-In-Time debugging, and Attach to programs running on this machine check boxes. Once you've done this, any error in an executing script will bring up a dialog box, shown below, asking if you want to debug. Click Yes to switch to the Script Editor Debugger.
Figure 6. Debugger dialog
To use breakpoints, you must run your page in Internet Explorer by clicking the Start button on the Script Editor's Standard toolbar. You can set breakpoints before or after you launch Internet Explorer. Make sure that you save the page before clicking the Start button so that the latest copy of your page will be available to Internet Explorer. (Unlike Access, Internet Explorer doesn't automatically synchronize with the copy you're editing in the Script Editor.)
In VBA, it's common to use the MsgBox function to provide information for debugging purposes. In data access pages, you must be careful about doing that. Because the Data Source control is running in a separate thread from Internet Explorer, using the MsgBox function can change the order in which events occur. A better solution is to create a debug log, as discussed in the section Tips and Tricks later in this document.
Since the Script Editor is a separate application from Internet Explorer, the debugger isn't as tightly integrated with the editor as the VBA debugger and editor are. For example, the Immediate and Watch windows aren't active unless you're stopped at a breakpoint or an error. This means, for example, that you can't put your windows side-by-side, set up a watch variable and see it change while your script is executing unless you break into the debugger and single-step through the script.
**Note **Don't forget to close the debugger when you're done. Otherwise, you'll get a proliferation of open debugger windows, because each entry into the debugger opens up a new copy of the Script Editor.
**Caution **Clicking the Stop button in the Script Editor will not stop an executing script—it only stops debugging. If the page is running in Access, it does the same thing as the Start button at that point. If you were stopped at an error, you can stop execution by clicking the Stop button, and then clicking No to the dialog in Access. If you click Stop when Internet Explorer is running the page, Internet Explorer (not just the script) will terminate.
Using the Watch Window
Because the object model can be hard to figure out and some portions of the object model are available only at run time (so they're not shown in the Object Browser), the Watch window in the Script Editor Debugger can be a very useful tool. To use it, set a breakpoint in an event handler, then launch Internet Explorer from the Script Editor. When the breakpoint is triggered, type in the names of the variables you want to inspect. The example below shows an expanded DSCEventInfo structure passed into the DataPageComplete event handler.
Figure 7. Watch window
Error Handling
One way to make sure that your Web pages work better is to use VBScript error handling for defensive programming. To turn VBScript error handling on, use:
On Error Resume Next
To turn VBScript error handling off, use:
On Error Goto 0
After an error has occurred, the Err object may be queried to determine if an error occurred. The Err object will be valid until an Err.Clear statement is executed or another error occurs. For example:
On Error Resume Next
. . .
If (Err.Number <> 0) Then
. . .
Err.Clear
End If
On Error Goto 0
Another common way to use error handling is to assume a default value or action if an error occurs. For example, the following code sets value to be x/y or 0 if y is 0.
On Error Resume Next
value = 0
value = x/y
Err.Clear
On Error Goto 0
**Note **The On Error Resume Next statement applies only to the current script handler. When the handler returns, normal error handling is resumed.
Data Access Page Events
Scripts for data access pages are written in event handlers, which are attached to objects in the page. The event handler is similar to a subroutine or function that is called when the event occurs. The objects you're most likely to use are:
The document object (document) is the Internet Explorer object for the Web page. It is not a visible object.
The DataSourceControl object is the data provider for the bound controls on your data access page. It always has the ID attribute
MSODSCand is not a visible object.**Note **Data Source controls created by other Office applications, such as Microsoft Excel, will have different ID attributes.
The sections are the visible structure of the Web page, as defined in the data access page designer. Sections are visible but normally serve as a design area only and have a transparent background.
You can add any objects you want within the sections or anywhere else on the page. These objects may be HTML-intrinsic controls (for example, a Button control) or ActiveX controls (for example, the Office Spreadsheet Component). These objects may be visible or hidden.
This section contains some basic information about some of the Internet Explorer objects.
The document Object
The document object provides events and functions related to the Internet Explorer document model. The events that you're most likely to use are:
onclick—This event occurs whenever the user clicks in the window.
oncontextmenu—This event occurs whenever the user right-clicks in the window (or presses the Windows shortcut [context] menu key). This event returns a Boolean value. To indicate that you've handled the event, return False.
<SCRIPT event=oncontextmenu for=document language=vbscript> ' Replacement context menu code here window.event.returnValue = false ' Cancels regular context menu </SCRIPT>onhelp—This event returns a Boolean value. To indicate that you've handled the event, return False.
<SCRIPT event=onhelp for=document language=vbscript> ' Replacement F1 help code here window.event.returnValue = false ' Cancels regular help </SCRIPT>
**Note **The onhelp event occurs in Internet Explorer only when the F1 key is pressed. If you view your data access page in Access, pressing F1 always opens the Microsoft Access Help system. In either case, clicking a Help menu item will bring up the appropriate application help.
onrowenter—This event occurs after the current record changes. You can use this event to perform any processing that you want to occur before the fields are populated.
onrowexit—This event occurs before the current record changes (because of record navigation, deletion, or page termination). You can use this event to perform record-level validation. If the validation fails, setting the returnValue property to False will prevent the current record from changing. For example:
<SCRIPT event=onrowexit for=document language=vbscript> If (validationFails) Then MsgBox "Validation Failed." window.event.returnValue = False End If </SCRIPT>
The Data Source Control
The Data Source control is the data provider for the page. It is a standard data source object, as defined by Internet Explorer. All Data Source control events take a single argument, which is a DSCEventInfo structure. For consistency, the same structure is used for all events, but only the appropriate fields for each event are supplied. This structure contains the following items:
- dataPage—The data access page for the event. In nested pages, the data access page may refer to a nested group of bands.
- section—The section for the event.
- returnValue—The value used to prevent an event action from proceeding.
- error—An error value, when applicable.
The Data Source control events are:
- BeforeCollapse(DSCEventInfo)—This event occurs before a section is collapsed. The section is the section containing the Expand control. Cancel it by setting
info.returnValueto False. - BeforeExpand(DSCEventInfo)—This event occurs before a section is expanded. The section is the section containing the Expand control. Cancel it by setting
info.returnValueto False. - BeforeFirstPage(DSCEventInfo)—This event occurs before the Record Navigation control navigates to the first page. Cancel it by setting
info.returnValueto False. - BeforeLastPage(DSCEventInfo)—This event occurs before the Record Navigation control navigates to the last page. Cancel it by setting
info.returnValueto False. - BeforeNextPage(DSCEventInfo)—This event occurs before the Record Navigation control navigates to the next page. Cancel it by setting
info.returnValueto False. - BeforePreviousPage(DSCEventInfo)—This event occurs before the Record Navigation control navigates to the previous page. Cancel it by setting
info.returnValueto False. - Current(DSCEventInfo)—This event occurs after a section becomes the current section, either when the page is opened, a section is expanded or collapsed, or the user clicks in a section.
- DataError(DSCEventInfo)—This event occurs when a data error is encountered.
- DataPageComplete(DSCEventInfo)—This event occurs when all data binding is completed after a page open, navigation, or expansion.
You can also use the Data Source control to access the logical structure of your data access page, as you'll see below.
Sections
Access uses the <DIV> tag to create the design grid surfaces for data access pages. If you look in the HTML code, you'll see something that looks like this:
<DIV class=MSOShowDesignGrid id=HeaderCategories
style="BACKGROUND-COLOR: transparent; HEIGHT: 2.5in;
POSITION: relative; WIDTH: 6in">
. . .
</DIV>
The ID attribute is the name of the section and will be named according to the binding of the section. You cannot change the name of the section.
When you have a banded page, you'll have multiple instances of the same section. You can only have one handler for each section event, which will be called when that event occurs for any instance of that section.
**Caution **Under some situations, Access may change the name of a section. For example, if you create a section with a field from one table, then drag in a field from a second table that has a one-to-many link from the first table, Access will change the name of the section. For this reason, you're better off if you've completed the basic design of your data access page before you start adding scripts. Otherwise, you'll need to update any scripts headers and code that refer to the old section name.
The DHTML events that occur for sections are as follows:
- ondatasetcomplete—This event occurs after the Data Source control does all data binding when a page is loaded.
- ondataavailable—This event occurs after the ondatasetcomplete event but before the page is rendered.
- ondatasetchanged—This event occurs when a record is updated or the page is scrolled.
- onfocus—This event occurs when a section gets the focus because the user clicked in it or tabbed into it.
The Record Navigation Control
The Record Navigation control has a single event, named ButtonClick. This event takes a single argument, named NavButton. The ButtonClick event occurs when a user clicks a button in a Record Navigation control. You can use this event to alter the action of the Record Navigation control while using the standard Record Navigation control interface.
Data Access Page Objects
When you're writing an event handler, you can manipulate any of the objects in the page. In addition to the objects themselves, you can use the Data Source control to access objects that define how the page is mapped to the data in the database.
Working with Data-Bound Controls
The default ID attribute for data-bound controls is the name of the field (for example, Command0, Command1, and so on); for the control's corresponding label, the ID attribute is Fieldname_label. You can change the ID attribute at any time, but remember that you'll have to update any events or code that you've already written to reflect the new ID value.
When you manipulate a data-bound field, you're manipulating the database field. Changes that you make take effect immediately in the data access page, and, just like when the user makes a change, the value is saved back to the database when the record is committed.
**Important **In Internet Explorer 5.0, objects are referenced by ID attribute, rather than by name. When you create an object, the data access page designer gives it a default name and a matching ID attribute value. After the object is created, you can change the name and ID attribute separately, but it is recommended that you change them to the same value. A bound object's name is usually the name of the bound field, but it may not be if there are multiple fields with the same name in different tables, or if you bind the control to data after you create it.
If you have a simple page, you can reference your objects simply by their ID attribute values. For example, in a nonbanded page, the following code will display the contents of your CategoryName field:
MsgBox CategoryName.value
Similarly, to change the value of a field, you can use:
CategoryName.value = newvalue
Bound HTML controls, whether used to display text or HTML, are represented by a <SPAN> tag in HTML, not an intrinsic field. Therefore, to get the value of a bound HTML control, you need to use one of the following
CategoryName.innerText
–or–
CategoryName.innerHTML
**Important **It is important to remember that you can access only database fields represented by data-bound controls on the page. If you want to access a field that you don't want shown, you must create a hidden data-bound control. For the best performance, you should create a hidden bound HTML control.
You may see one or both of the following syntaxes used to access database fields on data access pages
document.all.FieldName.value
–or–
document.all("FieldName").value
These are similar to the syntax used in Access and are necessary when accessing the contents of a data access page from VBA. However, in VBScript, using either syntax will slow down your script and cause your page to take longer to download.
Working with Banded Pages
If you have a banded page, you'll have more than one field with the same ID attribute value, which can be accessed as an array in your script. For example, the following code displays the value of the CategoryName field in the first band. Note that the array is zero-based.
MsgBox CategoryName(0).value
You can find the number of bands by using the length property. For example:
CategoryName.length
The following code will loop through all of the currently shown CategoryName fields:
For i = 0 To CategoryName.length-1
MsgBox CategoryName(i).value
Next
Using the HTMLContainer Object to Access Controls
The techniques described above will work for simple pages, but banded pages and pages with nested bands can get more complicated. For example, suppose you have a Category band with a nested Products band that contains a ProductName field. If the Products band is initially collapsed, you'll start out with no ProductName controls, so ProductName will be undefined. If the first Category band you expand has only one ProductName field, you'll then have one ProductName control that can be accessed directly. When you expand the next Category band, you'll now have a ProductName array. Any code you write that relies on the ProductName field being a control or an array of controls, or even just the fact that the ProductName field is defined, won't work.
For this reason, there's another way to access the controls in a band—through the current section's HTMLContainer object, which contains the controls. We start out with the Data Source control for the page:
MSODSC
and use it to access the current section, which is the section of the page that currently has the focus (the last section the user clicked in or tabbed to):
MSODSC.CurrentSection
The CurrentSection property returns a Section object that points to the Internet Explorer HTMLContainer object for the DIV that contains the controls:
MSODSC.CurrentSection.HTMLContainer
Once you've got the DIV, you can access the controls that are children of the DIV:
MSODSC.CurrentSection.HTMLContainer.children("fieldname")
Since each DIV has only one field of each name, this always returns a control, so you can use constructs like the following without problems.
Caution
This isn't true with option groups, because all of the controls in the option group will have the same ID attribute.
MSODSC.CurrentSection.HTMLContainer.children("phone").value
If you need to access a lot of controls, you may find it convenient to do the following:
Set cs = MSODSC.CurrentSection.HTMLContainer
cs.children("fieldname").value = ...
You can also use one of the following
Set field = _
MSODSC.CurrentSection.HTMLContainer.children("phone")
field.value = ...
–or–
With MSODSC.CurrentSection.HTMLContainer.children("phone")
.value = ...
End With
The latter syntax is the most efficient because it doesn't use an extra variable, but it can be awkward if you have multiple objects or fields that you're referencing. Note that this syntax can't be used if you are doing something like this:
Set field1 = _
MSODSC.CurrentSection.HTMLContainer.children("phone1")
Set field2 = _
MSODSC.CurrentSection.HTMLContainer.children("phone2")
field1.value = field2.value
However, you might want to optimize performance in this situation by using the syntax like this:
With MSODSC.CurrentSection.HTMLContainer
Set field1 = .children("phone1")
Set field2 = .children("phone2")
End With
field1.value = field2.value
**Note **The .children("name") syntax returns an error if there's no such control. If you're not sure that the control exists, you must use error handling.
Frequently, you have an object and you need to get the section it's in. The Data Source control provides a function for just this purpose:
MSODSC.GetContainingSection(element)
You can use the HTMLContainer object of the containing section to access sibling objects of a given object:
MSODSC.GetContainingSection(element)._
HTMLContainer.children("fieldname")
If you're writing code in an event handler for a section, you'll use:
DSCEventInfo.section.HTMLContainer.children("fieldname")
**Note **These examples show how to use a field name because that's what you'll want a lot of the time, but you can, of course, use this technique to access any object within the DIV.
"Walking" the Page
There are a number of scenarios in which you will need to deal with multiple bands or multiple controls at the same time. To do this, you'll need to "walk" through the bands or controls on the page.
For example, you might want to do something with all the bands in a banded page. To do this, add a DataPageComplete event handler to the Data Source control, then use the passed-in data access page to find all the applicable bands. The code below does this to create bands of alternating white and green like old-style computer paper had. Note the inspection of the type of the section.
<SCRIPT event=DataPageComplete(info) for=MSODSC language=vbscript>
Dim theSection
Dim bandNum
Dim theStyle
' info.DataPage is the "page" that is complete.
' For nested pages, this can be a set of nested bands.
Set theSection = info.DataPage.FirstSection
bandNum = 0
' Following code creates alternating white and
' green bands like old computer paper.
' Loop through all bands in info.DataPage.
Do
set theBand = theSection.HTMLContainer
' Check type of section before colorizing.
Select Case theSection.type
Case 1 ' Caption section
theBand.style.backgroundColor = "white"
Case 2 ' Header section
If (bandNum Mod 2) = 0 Then
theBand.style.backgroundColor = "white"
Else
theBand.style.backgroundColor = "C0FFC0"
End If
bandNum = bandNum + 1
Case 4 ' Navigation section
theBand.style.backgroundColor = "white"
End Select
' Get the next section and exit if there isn't one.
Set theSection = theSection.NextSibling
If theSection Is Nothing Then Exit Do
Loop
</SCRIPT>
Another thing you might want to do is access all the controls in a given band. To expand upon the above example, you might want the text in the currently selected band to be shown in red instead of the default color. The following code uses a SetTextColors subroutine to restore any colorization we've previously done and to set the colorization of the current section.
<SCRIPT language=vbscript>
Dim lastContainer
</SCRIPT>
<SCRIPT event=Current(info) for=MSODSC language=vbscript>
If Not IsEmpty(lastContainer) Then
SetTextColors lastContainer, "black"
End If
Set lastContainer = MSODSC.CurrentSection.HTMLContainer
' If we want to restore the color, we need to save it here.
SetTextColors lastContainer, "red"
</SCRIPT>
The SetTextColors subroutine recursively scans the children of the section and sets the color of any appropriate objects that it finds.
<SCRIPT language=vbscript>
Sub SetTextColors(container, textColor)
For i = 0 To container.children.length-1
Set obj = container.children(i)
' BoundHTML controls and labels are displayed
' via SPANs. To identify them, we must check
' the className. For INPUTs and TEXTAREAs,
' we can just check the tagName.
If (obj.className = "MSOBoundHTML") Or _
(obj.className = "MSTheme-Label") Or _
(obj.tagName = "INPUT") Or _
(obj.tagName = "TEXTAREA") Then
obj.style.color = textColor
End If
' The following code deals with nested children.
If (obj.children.length <> 0) Then
SetFieldColors obj, color
End If
Next
End Sub
</SCRIPT>
If you want to access a given control in all currently shown bands and don't care about the structure of the page, you can use the previous technique. You can also use the control's parentElement property to access the sibling controls of the current control:
For i = 0 To CategoryName.length-1
MsgBox CategoryName(i).value
Set cs = CategoryName(i).parentElement
MsgBox cs.children("Description").value
Next
The following code scans all the NetProfit fields in the page and changes the color of all negative numbers to red.
<SCRIPT event=Current(info) for=MSODSC language=vbscript>
For i = 0 to NetProfit.length-1
If (NetProfit(i).value < 0) Then
NetProfit(i).style.color = "red"
End If
Next
</SCRIPT>
This script is triggered on MSODSC.Current so that it can properly format all NetProfit fields as the user scrolls through the records. However, this script assumes you'll have multiple NetProfit fields. It won't work properly on a page that has only one NetProfit field, because Internet Explorer treats the controls as an array only when there are two or more. When there's just one control, it's referenced directly. The following, more general, scripts will work no matter how many NetProfit fields there are.
<SCRIPT event=Current(info) for=MSODSC language=vbscript> On Error Resume Next
n = NetProfit.value
If (Err.Number = 0) Then ' There is 1 NetProfit field.
NetProfit.style.color = "red"
Else
n = NetProfit.length-1
If (Err.Number = 0) Then ' There are 2 or more fields.
On Error Goto 0
For i = 0 to n
If (NetProfit(i).value < 0) Then
NetProfit(i).style.color = "red"
End If
Next
End If
End If
</SCRIPT>
If you want to do more sophisticated manipulation of the bands or controls on your page, you can walk through everything on the page by starting with:
MSODSC.object.DataPages(0).FirstSection
From this starting point, you can use the ParentSection, ChildSection, NextSection and PreviousSection properties to find the corresponding sections of the page. In any section, the HTMLContainer object contains the controls.
You can get information about the grouping levels and settings of the page from:
MSODSC.object.GroupLevels.length
MSODSC.object.GroupLevels(0) ...
**Caution **When a section is collapsed, it is hidden rather than deleted. This means that the bands and controls will still exist in the document and will be accessible from your code, even though they're not visible. Be careful not to assume that a given control is visible.
Source Elements and Event Bubbling
Whenever you're in an event handler, you can reference the object that triggered the event by using:
window.event.srcElement
Using the srcElement property to reference the object that triggered an event can be very convenient, for example, when you're using common code for several event handlers. It is important to note that the event triggering the event and the event handling the event are not necessarily the same object. This is because of a feature known as "event bubbling." Simply put, when an event is triggered for an object, all objects containing that object also get a chance to handle the event. For example, if you have an onclick event handler on your document, it would handle any clicks on any object within the document. If you want to use window.event.srcElement for any object that is a container (for example, a BoundHTML object), you must check the tagName property or the ID attribute to verify that the object is the one you want. For example, the following code brings up a message box with the URL of any image in the document when it is clicked on.
<SCRIPT for=document event=onclick language=vbscript>
If (window.event.srcElement.tagName = "IMG") Then
MsgBox "ID = " & window.event.srcElement.src
End If
</SCRIPT>
If you've handled an event and you don't want it to bubble up to containing objects, you can cancel the bubbling of an event in your event handler by setting the cancelBubble property to True:
window.event.cancelBubble = true
The Data Source control provides a function named GetContainingSection to allow you access the section that contains any object. Using this function is particularly convenient when you have an event that occurs on an object in a banded page, or when you have a routine that is called for multiple objects. For example:
Set theSection = _
MSODSC.GetContainingSection(window.event.srcElement)
If you need a specific object in the hierarchy, you can use the parentElement property and/or children fields of the srcElement property (or the containing section) to find it:
window.event.srcElement.parentElement
theSection.children.length ' # of children
theSection.children(0) ' first child
**Caution **When a bound HTML control is known to contain exactly one element (for example, an IMG), you might think that only that element can trigger an event such as onmouseover or onclick. However, the edge pixels of the image are considered to be in the containing SPAN, not in the IMG, so you need to make sure to handle this case properly.
The Current Section
The current section is roughly equivalent to the current record in a form or datasheet, and is generally the last section the user clicked in or tabbed to. The current section is also set at a few other times:
- When a page is opened, the current section is set to the first section.
- When a section is expanded, the current section is set to the first nested section.
- When a section is collapsed, the current section is set to the section that was collapsed.
Whenever the current section is changed, the MSODSC.Current event occurs. To access the current section from script, use:
MSODSC.CurrentSection
Dealing with Data
Determining What Records Are Shown
Given a section (such as MSODSC.CurrentSection), you can find out which table record it is showing by using:
theSection.HTMLContainer.RecordNumber
If you want to find the range of records currently shown in a group of sections, you can use:
first = theSection.HTMLContainer.RecordNumber
If (tempSection.DataPage.GroupLevel.DataPageSize = 1) Then
last = first
Else
last = tempSection.HTMLContainer.parentElement._
parentElement.parentElement.parentElement._
lastChild.lastChild.lastChild.lastChild.RecordNumber
End If
The multiple parentElement and lastChild property references above are intended to get from the section to the containing table and back down to the next section. The parent elements, in order, are: the cell (<TD>), the row (<TR>), the table section (<TBODY>), and the table (<TABLE>).
The number of available records is:
theSection.DataPage.Recordset.RecordCount
Changing a Page's Connection String
There are several reasons you might want to change a data access page's connection string from a script. To change the string, all you have to do is modify the ConnectionString property of the Data Source control. The only caveat is that you either have to create the entire connection string yourself or you have to modify the existing connection string to switch connections between databases that are equivalent (for example, from Microsoft Jet to Microsoft Jet). Here are some routines to reconnect a data access page that is connected to a Microsoft Jet database:
<SCRIPT language=vbscript>
Const intro = "Data Source="
Function GetConnectedDatabase
str = MSODSC.ConnectionString
n = InStr(1, str, intro) + Len(intro) - 1
connect2 = Right(str, Len(str) - n)
n = InStr(1, connect2, ";")
GetConnectedDatabase = Left(connect2, n - 1)
End Function
Sub ReconnectDataAccessPage(todb)
str = MSODSC.ConnectionString
n = InStr(1, str, intro) + Len(intro) - 1
connect1 = Left(str, n)
connect2 = Right(str, Len(str) - n)
n = InStr(1, connect2, ";")
pathname = Left(connect2, n - 1)
connect2 = Right(connect2, Len(connect2) - n + 1)
newstr = connect1 & todb & connect2
MSODSC.ConnectionString = newstr
End Sub
Sub AskForReconnect
msg1 = "This page is currently connected to "
msg2 = ". Please specify a new database to connect " &_
"to or click Cancel to keep this connection."
msg3 = "Database Connection"
oldDB = GetConnectedDatabase
newDB = InputBox(msg1 & oldDB & msg2, msg3, oldDB)
If (oldDB <> newDB) and (newDB <> "") Then
ReconnectDataAccessPage newDB
End If
End Sub
</SCRIPT>
To change your connection, you can do something like this:
ReconnectDataAccessPage "\\myserver\myfiles\Northwind.mdb"
This will reconnect your data access page to a copy of Northwind on \\myserver\myfiles. When the reconnection happens, any errors (such as fields or tables not matching your database) will produce error dialog boxes, just as they do when a page loads. The reconnection happens immediately. If the current record hasn't been saved already, it won't be saved. To save a record first, do the following before reconnecting:
MSODSC.CurrentSection.DataPage.Save
If you want to create a page that automatically always uses a relative path for a database, you can do the following:
<SCRIPT language=vbscript>
' This script executes inline at page load time
dbname = GetConnectedDatabase
' Get file name of database.
n = InStrRev(dbname, "\")
dbname = Right(dbname, Len(dbname) - n)
' Get path name of Web page.
path = window.location.pathname
path = Left(path, InStrRev(path, "\"))
If (Left(path,1) = "/") Then
path = Right(path, Len(path) - 1)
End If
' Reconnect to the database with a relative path.
ReconnectDataAccessPage(path & dbname)
</SCRIPT>
Note that this will only work with local files and UNC paths—it won't work with an http: URL because you can't access a database through HTTP.
**Important **Any script like this should be at the bottom of your Web page just before the closing </HTML> tag so that it will be executed after the Data Source control has been created but before the page is displayed.
With a little more work, you can create a Web page that prompts for a database or allows you to target multiple databases.
Using ActiveX Data Objects (ADO) to Access a Different Table
It's easy to use ADO code to access any table in the bound database. For example, the following code opens a second recordset on the database by using the Connection property of the Data Source control. The following script updates the EmployeeName field whenever the EmployeeID field changes.
<SCRIPT for=employeeID event=OnChange language=vbscript>
Set rs = CreateObject("ADODB.Recordset")
sqlSelect = _
"SELECT * FROM Employees WHERE EmployeeID = '" & _
EmployeeID.value & "'"
rs.Open sqlSelect, MSODSC.Connection
If rs.EOF Then
EmployeeName.value = "(Unknown Employee)"
Else
EmployeeName.value = FetchValue(rs, "EmployeeName")
End If
rs.Close
Set rs = Nothing
</SCRIPT>
The FetchValue custom function used above needs to be defined because fetching a value from the recordset might return either a value (a string in this case) or a null. This function is similar to the Nz function built into VBA.
<SCRIPT language=vbscript>
Function FetchValue(rs, field)
tempVal = rs.Fields.Item(field).value
If (IsNull(tempVal)) Then
FetchValue = ""
Else
FetchValue = tempVal
End If
End Function
</SCRIPT>
Adding records to other tables is even easier because you can simply execute an SQL statement to do the work. You may find this convenient if you need to create an auxiliary record or records when the page is committed. For example, the following code saves the current record, then creates a linked record in the Order Details table to go with it:
<SCRIPT event=onclick for=ConfirmButton language=vbscript>
MSODSC.DataPages(0).Save
cmd = "INSERT INTO [Order Details] (OrderID,ProductID)" & _
"VALUES (" & OrderID.value & "," & _
ProductID.value & ")"
MSODSC.Connection.Execute cmd
</SCRIPT>
Using ADO to Access a Different Database
Data access pages can access only a single database, but sometimes there are situations where you want to access a second database. When this is the case, you can use standard ADO code to open a connection and access the database. For example:
MyConnectString = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"Local Identifier=1033;" & _
"Data Source=data source;User ID=userid;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=catalog;" & _
"Data Provider=SQLOLEDB.1"
Set myConnection = CreateObject("ADODB.Connection")
myConnection.Open myConnectString
Taking Control
The Data Source control provides a number of methods that you can use to control the data access page. With these, you can create your own Record Navigation control or Expand control.
Creating a Custom Record Navigation Control
The simplest way to create a custom Record Navigation control is to use the Command Button Wizard to create replacement buttons for you. However, sometimes you'll want to do something slightly different when navigation occurs, such as do some calculations or bring up a dialog box. In these cases, you can use the following methods to perform the actions of the Record Navigation control.
MSODSC.CurrentSection.DataPage.MoveFirst
MSODSC.CurrentSection.DataPage.MovePrevious
MSODSC.CurrentSection.DataPage.MoveNext
MSODSC.CurrentSection.DataPage.MoveLast
MSODSC.CurrentSection.DataPage.MoveLast
MSODSC.CurrentSection.DataPage.DeleteRecord
MSODSC.CurrentSection.DataPage.Save
MSODSC.CurrentSection.DataPage.Undo
Note that these methods will return errors if the requested action isn't currently available (for example, you try to use the MovePrevious method when you're on the first record). If you want to enable or disable your buttons when they're not available, you'll have to check the currently shown records, as described previously, and also check if the current recordset is updateable. The following routine checks if a given section is updateable:
Sub SectionIsUpdateable(theSection)
SectionIsUpdateable = False
On Error Resume Next
' Banded pages aren't updateable.
If (MSODSC.object.GroupLevels(0).DataPageSize > 1) or _
(MSODSC.object.GroupLevels.count > 1) Then Exit Sub
' Now check if recordset itself is updateable.
Set rs = theSection.DataPage.Recordset
If Err.Number <> 0 Then Exit Sub
If Not rs.Supports(adUpdate) Then Exit Sub
If rs.Properties("Unique Table") = "" Then Exit Sub
SectionIsUpdateable = True
End Sub
You can also modify an existing Record Navigation control by setting its properties to eliminate buttons that you don't want. Or, you can change the text that appears in the default Record Navigation control by using the following line:
TableNameNavigation.RecordsetLabel = "Record |0 (of |2)"
TableNameNavigation is the name of the Record Navigation control for the section for the specified table. Look in the Script Outline window to find the name of your Record Navigation control. The |0 and |2 in the RecordsetLabel property will be replaced with the current record number and the number of records, respectively.
Another common thing you may want to do is to hide the Record Navigation control if it's not needed, which will be the case if you have a banded page that isn't showing more records than the value stored in the DataPageSize property.
<SCRIPT language=vbscript>
' HideNavControlIfNotNeeded checks if all the records
' are being shown and hides the navigation control
' if so. It is passed a RecordNavigation Control object.
Sub HideNavControlIfNotNeeded(navctrl)
On Error Resume Next
nShown = MSODSC.DataPages(0).GroupLevel.DataPageSize
nAvail = MSODSC.DataPages(0).Recordset.RecordCount
If (nShown >= nAvail) Then
navctrl.style.visibility = "hidden"
End If
End Sub
</SCRIPT>
To complete this operation, you'll probably want to hide the Record Navigation control buttons that don't apply in banded pages, such as Save and Undo.
Creating a Custom Expand Control
You can replace the Expand control with one or two buttons by using the following functions:
MSODSC.CurrentSection.Expand
MSODSC.CurrentSection.Collapse
To test if the current section is expanded, use:
MSODSC.CurrentSection.IsExpanded
Note, however, that you can't delete the existing Expand control when you insert your own controls—you need to hide the Expand control instead. If you delete the Expand control, the Data Source control will fully expand your subsections to ensure that the data is accessible (the Data Source control can't determine if you're providing an alternate way to expand the sections).
Sometimes, you'll want the Expand control (or your custom expand button) to appear only when there are subrecords. Unfortunately, there's no way to tell until the expansion is attempted. You can work around this limitation by using a query instead of a table as the record source for your page. Simply add to the query a field that contains a count of the number of subrecords. Then, use the value stored in this field to hide and show the Expand controls. If your outer level is banded, you'll have to do this by walking the page when the MSODSC.Current event occurs, with something like the following:
<SCRIPT event=Current(info) for=MSODSC language=vbscript>
For i = 0 to CountField.length-1
Set s = MSODSC.GetContainingSection(CountField(i))
Set e = s.HTMLContainer.children("TableExpand")
If CountField(i).value = 0 Then
e.style.visibility = "hidden"
Else
e.style.visibility = "visible"
End If
Next
</SCRIPT>
Tips and Tricks
If you want to manipulate a control that isn't shown, you can add the control to the page and set its Visible property to Hidden in the control's property sheet in the data access page designer.
Optimizing Your Use of the Script Editor
You probably won't find yourself using the auxiliary windows of the Script Editor quite as much (except for the Script Outline or HTML Outline windows) as you do the Visual Basic Editor. You can reclaim some screen real estate by overlapping the windows. You may also find it handy to dock the Find window so that it is always shown.
Figure 8. Script Editor screen with docked Find window
Creating a Debug Log
A common debugging technique is to create a "debug log" that records information while the program is running. Later, you can inspect the log to determine why the program didn't work as expected. When your page is running in Internet Explorer, it's easy to create a debug log in a separate window. The following script creates two routines that you can call from your code to provide debug output.
<SCRIPT language=vbscript>
Dim debugWindow
Sub StartDebug
Dim id ' Deliberately unassigned variable.
Set debugWindow = window.open("about:blank", id, _
"menubar=no,status=no,toolbar=no," & _
"resizable=yes,scrollbars=yes," & _
"left=20,top=20,width=400,height=300")
debugWindow.document.open("text/plain")
debugWindow.document.clear
debugWindow.document.bgColor = "FFFFC0" ' Lt yellow.
debugWindow.document.writeln("Debug Log for " & _
document.title)
End Sub
Sub SendDebug(str)
debugWindow.document.writeln (str)
End Sub
</SCRIPT>
Once you've defined these procedures, you can use them in your code:
StartDebug
...
SendDebug "debug text"
If you want to use HTML code in your debug output, replace the "text/plain" on the document.open with "text/html" and make sure everything you call SendDebug with is valid HTML code.
Using Common Code
If you have subroutines or functions that are shared among multiple pages, you can use a special form of the <SCRIPT> tag to include the shared code:
<SCRIPT language=vbscript src=sharedcode.vb></SCRIPT>
The shared code can also contain variable and constant definitions as well as code to execute immediately when the file is loaded, but it cannot contain HTML code.
Performing Multiple Functions
If you want to perform multiple actions when a given event fires, your code may become hard to read. To simplify this, create routines in separate script blocks and call them from the event handler. For example:
<SCRIPT>
Sub DoFieldValidation(info)
...
End Sub
Sub HiliteCurrentRecord(info)
...
End Sub
</SCRIPT>
<SCRIPT for=MSODSC event=Current(info) language=vbscript>
DoFieldValidation info
HiliteCurrentRecord info
</SCRIPT>
Writing General Purpose Scripts
If you're writing scripts that you want to work in multiple pages, you may need to know details about how the page was set up. To check if the current page is a banded page, use:
(MSODSC.object.GroupLevels(0).DataPageSize > 1) or _
(MSODSC.object.GroupLevels.count > 1)
To check if the current page is a data-entry page, use:
(MSODSC.object.DataEntry)
Accessing Arrays
All of the arrays that define your data access page are zero-based rather than the one-based arrays you may be used to. If there are n entries in an array, the array indexes are 0 to n-1. For example, to iterate through the GroupLevels collection, you would use:
For i = 1 to MSODSC.object.GroupLevels.count – 1
... MSODSC.object.GroupLevels(i) ...
Next
Determining If Internet Explorer or Access Is Hosting the Page
Sometimes, you need to know if your page is running in Access or Internet Explorer. For example, if you create a secondary window by using window.open when your page is running in Access, the window will open in Internet Explorer and you won't have permission to write to it. The following test can be used to determine if your page is running in Access:
(document.location.protocol = "accdp:")
Requiring Internet Explorer 5.0 on Other Pages
Data access pages require Internet Explorer 5.0, but if you're creating a Web site that has a mix of pages, the other pages will run in other browsers. Since this may be confusing for users, you may want to make other pages say that they require Internet Explorer 5.0 as well. This code will do that:
<SCRIPT language=vbscript>
Const strNeedsIE5 = _
"<B>This Web site requires Internet Explorer 5</B>"
IsIE5 = False
Set nav = document.parentWindow.navigator
If (nav.appName = "Microsoft Internet Explorer") Then
strVers = nav.appVersion
vers = CInt(Mid(strVers, InStr(strVers, "MSIE ") + 5))
If (vers >= 5) Then
IsIE5 = True
End If
End If
If (Not IsIE5) Then
document.body.innerHTML = strNeedsIE5
End If
</SCRIPT>
If you want a standard-looking error message, you can use the following (which you need to enter all on one line without the returns):
Const strNeedsIE5 = "<style>a:link {font:9pt/12pt verdana;
color:red}a:visited {font:9pt/12pt verdana; color:blue} </style>
<TABLE style=""background-color:white""><TR><td id=""tableProps""
width=70 valign=top align=center><img src=""
res://shdocvw.dll/pagerror.gif""></TD><TD valign=center
width=400><h1 id=""textSection1"" style=""font:14pt/16pt
verdana; color:#4e4e4e"">This page cannot be properly
displayed. </h1><font style=""font:9pt/12pt verdana;
color:black""><br>This Web site requires features
available in Microsoft Internet Explorer
version 5 or later.<hr size=1 color=""blue"">
You can download the latest version of
Microsoft Internet Explorer from
<a href=""http://www.microsoft.com/ie"">
http://www.microsoft.com/ie</A>
or by contacting your system
administrator.
</FONT></TD></TR></TABLE>"
You can put the code in a shared include file so that it's automatically executed in all of your pages.
Persisting Variables
If you want the value of a variable to persist between the invocations of an event handler, you must declare the variable in a script block outside the event handler. For example:
<SCRIPT language=vbscript>
Dim persisted
persisted = 0
</SCRIPT>
<SCRIPT event=onclick for=Command0 language=vbscript>
persisted = persisted + 1
</SCRIPT>
<SCRIPT event=onclick for=Command1 language=vbscript>
MsgBox persisted
</SCRIPT>
If you want a value to persist between invocations of the data access page or to persist when the page is reloaded, you'll need to use Internet Explorer 5.0 persistence behaviors or cookies.
Setting the Mouse Pointer
There are times when you want to override Internet Explorer's default mouse pointer (cursor). For example, if you have a label or BoundHTML control that you want to respond to mouse clicks, you may want the pointer to be an arrow or finger over that control. To do this, you need to set the cursor property of the object's style property:
object.style.cursor = "hand" ' Browser hand.
object.style.cursor = "default" ' Arrow.
object.style.cursor = "auto" ' Let Internet Explorer
' figure it out.
In banded pages, you'll need to set this property on an event such as Current or DataPageComplete for it to work properly.
Creating Customized Hyperlinks
There are times when you may want hyperlinks that are programmatically generated rather than static—for example, if you want to conditionally navigate to different pages depending on field values or if you want to supply a server filter that can't be defined statically. To do this, you'll need to do the hyperlink navigation yourself. First, the following routine will make a field look like a hyperlink:
<SCRIPT language=vbscript>
' MakeFieldLookLikeLink makes the specified field
' object(s) look like links and shows a browser hand
' when the mouse pointer moves over them.
Sub MakeFieldLookLikeLink(field)
On Error Resume Next
n = field.length-1
If (Err.Number <> 0) Then ' There is 1 (or no) field
field.style.cursor = "hand"
field.style.color = document.linkColor
field.style.textDecorationUnderline = True
Else
For i = 0 to n
field(i).style.cursor = "hand"
field(i).style.color = document.linkColor
field(i).style.textDecorationUnderline = True
Next
End If
End Sub
</SCRIPT>
Next, the following script will call the routine above whenever the current record changes, which ensures that any newly shown fields in a banded page will be set up properly:
<SCRIPT for=MSODSC event=Current(info) language=vbscript>
MakeFieldLookLikeLink(productName)
</SCRIPT>
Finally, you have to add an onclick event handler for the field. The code below navigates to a second page that is chosen based on whether the product is in stock. In either case, it supplies a page argument that supplies a server filter for the product that was clicked on.
<SCRIPT for=productName event=onclick language=vbscript>
If stockCount.value > 0 Then
pageName = "product.htm"
Else
pageName = "outofstockproduct.htm"
End If
window.location.href = _
pageName & "?serverFilter=" & _
"""productName='" & productName.value & "'"""
</SCRIPT>
If you want to open a new window for the hyperlink, you can use window.open instead of setting window.location.href .
**Note **When you use the page's search parameter (following the "?") for the server filter, the filter string will usually be visible to your users. If you don't want that to be the case, you can use cookies, behaviors, or persistent variables in a frame set to pass the server filter.
Creating OK and Cancel Buttons for Data-Entry Pages
If you want to create a data-entry page that looks like a dialog box, you'll probably want to delete the navigation control and add OK and Cancel buttons. The following scripts perform the desired action.
<SCRIPT event=onclick for=OKButton language=vbscript>
MSODSC.CurrentSection.DataPage.Recordset.Update
window.close
</SCRIPT>
<SCRIPT event=onclick for=CancelButton language=vbscript>
window.close
</SCRIPT>
If you want your page to allow for the entry of multiple records, you'll want Save and Done buttons instead, as follows:
<SCRIPT event=onclick for=SaveButton language=vbscript>
MSODSC.CurrentSection.DataPage.Recordset.Update
MSODSC.CurrentSection.DataPage.Recordset.AddNew
</SCRIPT>
<SCRIPT event=onclick for=DoneButton language=vbscript>
MSODSC.CurrentSection.DataPage.Recordset.Update
window.close
</SCRIPT>
In this case, you'll probably want to add an Undo button as well to allow people to undo when they make a mistake in data entry.
**Caution **If you use these scripts and your page was opened directly by the user rather than through a script, the
window.close
statement will cause a message to be shown to the user asking if he or she really wants to close the window. Internet Explorer does this to help guard against malicious scripts. For this reason, you should use this style of data-entry page only when you're opening the page from a script, for instance in response to an Edit button on another page.
You may also want to use OK or Cancel buttons on a data-entry page that is not opened in a separate window. In this case, you want the OK and Cancel buttons to return the user to the page he or she came from. There are two ways to do this. The first example loads the page that caused the current page to be opened, while the second one is equivalent to clicking the Back button in Internet Explorer. In most cases, the effect is the same except for the state of the Back button after execution.
<SCRIPT event=onclick for=CancelButton language=vbscript>
window.navigate(document.referrer)
</SCRIPT>
<SCRIPT event=onclick for=CancelButton language=vbscript>
window.back
</SCRIPT>
Note that window.close and window.back will not function when your data access page is hosted in Access, and window.navigate will cause a new Internet Explorer window to open.
Self-Selecting Fields
When the user tabs into a field in a form, the contents of the field are selected. If you'd like this same behavior in your data access pages, you can add the following script to each text field (substitute the actual field name for Field below).
<SCRIPT for=Field event=onfocus language=vbscript>
Field.select
</SCRIPT>
Creating Dependent Fields
When you have a control that is valid only in certain situations, it's reasonable to disable (gray out) or hide the control when it's not applicable. To do this properly, you need to use both a Current event handler, which is triggered whenever you change records, and an OnChange event handler, which is triggered whenever the appropriate field changes.
You'll need to always enable or disable or show or hide the control rather than counting on the current state of the control to be what you expect. If you don't do this, your code may not work properly when you change records. For example:
<SCRIPT language=vbscript>
Sub FixEnabling
' Enabling/disabling a field
flag = (PaymentType.value = "Credit Card")
' True means disabled; False means enabled
CardNumber.disabled = flag
CardNumber_Label.disabled = flag
' Hiding/showing a field
If (Dependents.value > 0) Then
DependentNames.style.visibility = "visible"
Else
DependentNames.style.visibility = "hidden"
End If
End Sub
</SCRIPT>
<SCRIPT for=MSODSC event=Current(info) language=vbscript>
<!--
FixEnabling
-->
</SCRIPT>
<SCRIPT for=MSODSC event=OnChange language=vbscript>
<!--
FixEnabling
-->
</SCRIPT>
Type Conversions
The value stored in a control on a data access page is always a string, even if the field it is bound to contains a numeric value. Normally, VBScript will convert a string to a number or a number to a string automatically. But sometimes you'll need to force the conversion. For this, you can use the CInt function****(for Integer values), the CSng function (for Single values), the CDbl function (for Double values), or the CLng function (for Long values). For example:
' Note: Field1.value is 5.
' Field2.value is 6.
a = Field1.value + Field2.value ' Result is 56.
b = CInt(Field1.value) + (Field2.value) ' Result is 11.
You can also use the CStr function to convert any numeric, Boolean, or Date value into a String value.
Field Formatting
In cases where you have a string representing a currency amount, you can use the CCur function to convert the String value to a Currency value and the FormatCurrency function to convert the Currency value back to a properly formatted currency****string. The following example uses a hidden data-bound NetProfit field mirrored by an unbound VisibleProfit field to provide better formatting for a Currency value.
<SCRIPT event=Current(info) for=MSODSC language=vbscript>
If (UnitPrice.value = 0) Then
VisiblePrice.innerText = "--"
Else
VisiblePrice.innerText = _
FormatCurrency(UnitPrice.value,2,-1,-1,-2)
End If
</SCRIPT>
<SCRIPT event=OnChange for=VisiblePrice language=vbscript>
str = VisiblePrice.innerText
If (str = "--") Then
UnitPrice.value = 0
Else
UnitPrice.value = CCur(VisiblePrice.innerText)
End If
</SCRIPT>
You can also use the FormatDateTime and CDate functions for date formatting.
Drop-Down Lists and List Boxes
Drop-down lists and list boxes have both a visible value (specified by the ListDisplayField property) and an actual or bound value (specified by the ListBoundField property), which come from different fields in a recordset (specified by the Recordset property). For example, a drop-down list bound to the PizzaTopping table might have a visible value of "Pepperoni" (from the ToppingName field) while the bound value is 7 (from the TopppingID field). To access the visible value, use:
theControl.value
To access the bound value, use:
theControl.children(theControl.selectedIndex).innerText
Creating Lookup Fields
The ability to create Lookup fields is a built-in feature of forms that is not provided in data access pages. Fortunately, it's easy to simulate. First, you need to create a drop-down list box control or a list box control linked to the lookup table field (not the field in the table you're going to bind to) by using either the Combo Box Wizard or the List Box Wizard or the control's property sheet. Once you've created the drop-down or list box control, drag in the remainder of the fields from the lookup table. These looked-up values won't be editable, because they are dependent on the lookup field value. There are two ways you can display the fields, as bound HTML controls or as text box controls. If you want them to look like other controls, drag in regular fields and set the Disabled property of the controls to True to prevent them from being selected. Otherwise, use bound HTML controls. You can then add any fields you want from your bound table.
Now you're ready to get the Lookup field working. To do this, simply add a script to the OnChange event of the drop-down or list box control that commits the record. When the data access page commits the record, it refreshes all linked fields, which gives you the Lookup field effect. To commit the record, use a single line:
MSODSC.CurrentSection.DataPage.Save
However, there's one catch. The OnChange event occurs before the drop-down or list box control has changed its value. The browser does this so that the script has the opportunity to cancel the change. Therefore, if you save the record, you'll be saving the record before the value is set rather than afterwards. To fix this, you use the setTimeout method, which delays the save for 1 millisecond, just enough time for the Current event handler to return and for the value to be set. For example:
<SCRIPT for=control event=OnChange language=vbscript>
setTimeout "MSODSC.CurrentSection.DataPage.Save", 1
</SCRIPT>
Changing the Binding of a Page
Another common feature of forms is having an unbound form that is bound to data programmatically. While you can't do that with data access pages, you can change how the page is bound by adding inline script before the </HTML> tag. This code will execute after the controls on the page are created but before the page is bound to data. This means that you can change the connection string, the bound recordset, the filters and server filters, and even the fields that elements are bound to.
Using an On-the-Fly Server Filter
The current server filter string for a loaded page, if any, is:
n = MSODSC.RecordsetDefs.count-1
MSODSC.RecordsetDefs.item(n).ServerFilter
The string will be a Null value if there is no server filter currently applied. To apply a new server filter at run time, simply assign a filter string to the ServerFilter property. To turn off an existing server filter, set the ServerFilter property****to a Null value. As discussed above, you can assign a server filter before the page binds to data by assigning the server filter in script that is executed inline right before the </HTML> tag.
For banded pages, you can set a ServerFilter property for each of the levels of the page, starting with 0 for the innermost level and MSODSC.RecordsetDefs.count-1 for the outermost band.
You need to use caution when assigning expressions to the ServerFilter property because assigning an invalid expression to it will cause error messages to be shown to your users and may make your page unusable. If you are allowing users to enter a server filter, or if you are creating a server filter based on user-supplied text, you may want to do some error checking on the contents—for example, to make sure that no quotation marks are missing and literal quotation marks are escaped (doubled) or that a date is in the proper format.
Filters vs. Server Filters
Filters and server filters are very similar. The main difference between them is where the filter is applied. A filter is applied on the client machine, which means that it is very quick to apply and doesn't require the page to be reloaded. On the downside, the client has to download all the data in order to filter it. In contrast, server filters are applied by modifying what is requested from the server, which means that the filtering is much faster. If you're filtering a large number of records into a small number of records, you'll probably need to use a server filter. The disadvantage of using a server filter is that any change to the server filter causes the page to be refreshed.
The other big difference between them is how the filter and server filter are exposed. Both can be manipulated programmatically, but the Filter By Selection feature is available as a button on the navigation control. If you want to manipulate the filter programmatically, you'll probably want to remove the Filter By Selection and Toggle Filter buttons from the Record Navigation control so that users can't alter the filter you're using. Although a server filter can be passed into a page by specifying the filter as a parameter to the page, you'll want to apply the server filter programmatically if you don't want your users seeing the server filter as part of the URL displayed by Internet Explorer.
Programmatically, the filter string is:
MSODSC.DataPages(0).Filter
In addition to setting the filter string, you must turn the filter on or off explicitly:
MSODSC.DataPages(0).IsFilterOn = True or False
Note that the indexing for filters through the DataPages collection (0 is outermost) is reversed from the indexing of the server filters through the RecordsetDefs collection (0 is innermost).
Providing a Context (Shortcut) Menu
In a conventional application, context menus (also known as shortcut, pop-up, or right-click menus) are an important part of the user interface. Context menus can also be important when you're building a Web-based application using data access pages.
By default, right-clicking anywhere in an Internet Explorer window will show the browser's context menu, but you can override this behavior for the entire document or for appropriate objects in order to provide your own context menu. To do this, you handle the oncontextmenu event for the desired objects, and then bring up a context menu by using the ActiveX PopupMenu control.
First, you'll need to add an instance of the Internet Explorer PopupMenu control to your data access page. If you already have the control installed, you could add it by using the data access page designer, but that won't provide the CODEBASE attribute needed for the control to be downloaded automatically from the microsoft.com Web site. The following HTML code provides the CODEBASE attribute:
<OBJECT ID="PopupMenu" WIDTH=1 HEIGHT=1
TYPE="application/x-oleobject"
CLASSID="clsid:7823A620-9DD9-11CF-A662-00AA00C066D2"
CODEBASE="http://activex.microsoft.com/controls/iexplorer/iemenu.ocx#Version=4,70,0,1161">
If you have a banded page, it's important to put the object outside the fixed-positioning section grids of your page to avoid getting multiple instances of the control in a banded page. In the data access page designer, note that the PopupMenu control won't show anything except selection handles.
The following script brings up a menu when the user right-clicks in the document:
<SCRIPT event=oncontextmenu for=document language=vbscript>
PopupMenu.Clear
PopupMenu.AddItem "Select..."
PopupMenu.AddItem "Select All"
PopupMenu.PopUp
' Inhibit standard context menu.
window.event.returnValue = False
</SCRIPT>
You can also specify the contents of the menu by using parameters to the <OBJECT> tag, but that doesn't allow you to have different context menus in different locations in your page.
The following code implements the Click event handler, which performs the menu functions.
<SCRIPT language=vbscript>
Dim lastSelection
LastSel = ""
</SCRIPT>
<SCRIPT event=Click(index) for=PopupMenu language=vbscript>
' Get RecordsetDefs collection for outermost level.
outer = MSODSC.RecordsetDefs.count-1 ' (0 is innermost)
Set defs = MSODSC.RecordsetDefs.item(outer)
Select Case index
Case 1 ' Select...
sel = InputBox("Enter product:", "Select", lastSel)
If (sel <> "") and (sel <> lastSel) Then
lastSel = sel
sf = "ProductName LIKE '%" & sel & "%'"
defs.ServerFilter = sf
End If
Case 2 ' Select All
defs.ServerFilter = ""
End Select
</SCRIPT>
This example uses a single context menu for the entire page, but you can easily attach separate menus to different portions of the document (such as the inner and outer bands), either by attaching different handlers to different objects or by using window.event.srcElement to determine where the mouse was clicked, then generating an appropriate menu for the clicked object. For example, the script above could have checked if the click was in a <DIV> tag for a band and could have then modified the associated server filter instead of the outermost filter. If you do something like this, the code that brings up the context menu should determine the context and save it for the code in the Click event handler.
Manipulating Data Access Pages from Access
Sometimes, you may find it useful to manipulate a data access page from VBA in Access. For example, you can use DoCmd.OpenDataAccessPage to open a page. To reference objects within the page, you'll need to use the all property to access them. For example:
theDataPage.all("MSODSC")
Supporting Arrow Keys for Record Navigation
It's relatively easy to provide alternate functions for the arrow keys—for example, to allow users to navigate between records by using the arrow keys. All you need to do is intercept the key events before Internet Explorer handles them and perform the appropriate record navigation. There are three keyboard-related events, but you must use onkeydown. The following script does this, while making sure that the arrow keys continue to work in input boxes and text areas. When the event is handled, window.event.returnValue is set to False to tell Internet Explorer not to process the event.
<SCRIPT event=onkeydown for=document language=vbscript>
Const leftKey = 37
Const upKey = 38
Const rightKey = 39
Const downKey = 40
tagKind = window.event.srcElement.tagName
If tagKind <> "INPUT" And tagKind <> "TEXTAREA" Then
On Error Resume Next
Select Case window.event.keyCode
Case leftKey
MSODSC.CurrentSection.DataPage.MovePrevious
window.event.returnValue = False
Case rightKey
MSODSC.CurrentSection.DataPage.MoveNext
window.event.returnValue = False
Case upKey
MSODSC.CurrentSection.DataPage.MoveFirst
window.event.returnValue = False
Case downKey
MSODSC.CurrentSection.DataPage.MoveLast
window.event.returnValue = False
End Select
End If
</SCRIPT>
Integrating Microsoft Office Web Components into Your Page
The Microsoft Office Web Components provide a way for you to integrate spreadsheet, charting, and PivotTable list functionality into your data access page. In general, you'll create visible components, which allow users to view and manipulate the data, but you can also create hidden Spreadsheet objects that allow you to use the power of Excel to manipulate your data without your users seeing a spreadsheet. You can create and manipulate these objects at run time in your data access pages. For example, you can insert values into or extract values from a Spreadsheet object, change the sorting on a PivotTable object, or change the chart style of a ChartSpace object.
For information about what you can do with the Office Web Components, consult the object models for the components, the Microsoft Office Web Components Visual Basic Help (C:\Program Files\Microsoft Office\Office\1033\Msowcvba.chm), and Chapter 12, "Using Web Technologies," in the Microsoft Office 2000/Visual Basic Programmer's Guide.
**Caution **Changes you make in an Office Web Component object in Design view, either directly in the object or in its property sheet, will be saved with the data access page; changes you make while viewing the page through either the user interface or through script, and in either Access Page view or in Internet Explorer, are not saved.
Next Steps
Information on the Web
The Microsoft Web site (www.microsoft.com/) has a wealth of information that you'll find useful—so much that it's not possible to provide a useful list. The following are some places to start looking:
Microsoft Developer Network (MSDN) Online, located at http://msdn.microsoft.com/default.asp.
Microsoft Scripting Technologies, located at http://msdn.microsoft.com/en-us/library/ms950396.aspx.
White Papers
For more information about working with data access pages, see "Deploying Data Access Pages on the Internet,""Creating Secure Data Access Pages," and "Connecting Data Access Pages Together."
Books
There are hundreds of books about building Web sites and creating scripts. Here are a few that you may find useful:
Microsoft Office 2000/Visual Basic Programmer's Guide. Microsoft Press, 1999.
Lomax, Paul. Learning VBScript. O'Reilly & Associates, 1997.
Mara, Mary Jane. VBScript Sourcebook. John Wiley & Sons, 1997.
Flanagan, David. JavaScript: The Definitive Guide. O'Reilly & Associates, 1998. (This book has a very good Internet Explorer 4.0 object model guide, which will presumably be updated for Internet Explorer 5.)
Sample Applications
Northwind Traders Direct is a simple e-commerce application that uses data access pages, frames, and cookies.
--------------------------------------------
© 1999 Microsoft Corporation. All rights reserved.
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, ActiveX, JScript, Microsoft Press, PivotTable, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.