Scripting the Data Source Control in Data Access PagesThis 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.
Summary: To write script that works with a data access page, you must understand how the page interacts with the underlying object models of Dynamic HTML pages, the Microsoft Office Data Source control, and Microsoft Data Access Components.
This article focuses on the following objects in the Data Source control object model: Section, GroupLevel, DataPage, and ElementExtension. These objects determine the layout, organization, and data binding of a data access page. The article discusses how these objects are integrated with DHTML objects so they can work with the page itself and use ADO objects to work with the data bound to the page.
In addition, the article provides an overview of the following:
- How to write script against the Microsoft Office Data Source control in data access pages
- Architecture of data access pages, which integrate functionality from Dynamic HTML pages, the Data Source control, and Microsoft Data Access Components
- Working with Data Source control events
You'll also find a summary of the new methods and properties that have been added to the Data Source control object model for Access 2002. (28 printed pages)
What is the Data Source Control?
Making Sense of the Object Models Behind Data Access Pages
Data Source Control Objects
The Section Object
The GroupLevel Object
The DataPage Object
The ElementExtension Object
Data Source Control Events
What's New for Access 2002 Data Access Pages?
Data access pages are designed to reproduce many of the features of Microsoft® Access forms and reports in Web pages running in Microsoft Internet Explorer 5.0 or later. Beyond simply reproducing forms and reports, data access pages also provide additional features and benefits that are tailored to performing data browsing, data entry, reporting, and analysis from a Web browser. While Access 2000 or later is required to create data access pages, users of data access pages require only Internet Explorer 5.0 or later, an installation of the Microsoft Office Web Components, and sufficient security permissions to work with the data sources the page is connected to (if working with live data).
Data access pages are built upon several technologies and components, the most important of which is the Microsoft Office Data Source control (MSODSC), part of the suite of components for displaying, editing, and analyzing data called the Microsoft Office Web Components. The Data Source control integrates functionality from the Dynamic HTML (DHTML) Object Model, which is implemented by Microsoft's HTML parsing and rendering engine (MSHTML), and from ActiveX Data Objects (ADO), the programmatic interface to the Microsoft Data Access Components (MDAC).
Figure 1. Data access page components
This article focuses on how the core features of the Data Source control's object model are related to the DHTML and ADO object models, and how to use that knowledge to extend and customize data access pages by writing script. Most of the information presented in this article can be applied to data access pages created in both Access 2000 and Access 2002. Some of the new features provided in Access 2002 data access pages are also described.
Data access page Design View in Access 2002 incorporates many improvements, such as multiple levels of undo, multiselect of page elements, improved property browsing, drop areas for grouping, and the Layout Wizard (which is displayed when you drag multiple fields or an entire table onto a page from the Field List) to make laying out multiple fields easier. For the best design experience, you should create data access pages in Access 2002 with Internet Explorer 5.5 or later installed (some of the improvements in Design View depend on components installed by Internet Explorer 5.5 or later).
NoteData access pages created in Access 2002 can't be opened in Design View in Access 2000. Similarly, once you open and save changes to a data access page created in Access 2000 by opening it in Access 2002 Design View, you can't re-open it in Access 2000. However, pages created in Access 2002 can be opened in Page View in Access 2000, if the user also has the Microsoft Office XP Web Components installed.
This article does not cover working with the Data Source control for the Microsoft Office Chart, Spreadsheet, and PivotTable® components. For more information on these components, see Working with the Office Web Components in the Microsoft Office 2000/Visual Basic Programmer's Guide and Programming Microsoft Office 2000 Web Components books by Microsoft Press.
The primary functions of the Data Source control are to connect to data sources, to build and execute commands against those data sources, and to retrieve and bind the results of those commands to elements on the page. Additionally, the Data Source control keeps track of the record the user is currently working with. For more complex "banded" pages, the Data Source creates hierarchical groupings. For instance, you can display an order form with a subform that displays the order details from a related table, or a sales report that groups data by multiple levels, such as nested groupings by month, region, and sales representative. (For more details on banded pages, see The Layout of a Data Access Page later in this article).
Unlike the other Office Web Components controls, the Data Source control is an ActiveX® control that has no visible interface at run time. When you create a new data access page in Access, it inserts an <OBJECT> tag that defines the Data Source control in the HEAD element of the data access page. The ID attribute of the <OBJECT> tag created for a data access page created in Access is always set to
If you open a data access page in the Microsoft Script Editor from Access 2002 (by opening the data access page in Design View, and then clicking Microsoft Script Editor on the toolbar), you should see the <OBJECT> tag for the Data Source control near the top of the page. Following the CLASSID attribute for the control is an extensive XML definition of properties of the control, such as the ConnectionString property and the ElementExtensions collection. The ElementExtensions collection is used to add custom properties to the data-bound HTML elements in a data access page, and only exists after fields are bound to a page. The ElementExtensions collection is described in greater detail later in this article.
ImportantDo not modify the definition of the <OBJECT> tag for the Data Source control directly in the HTML of a data access page. You should only modify these settings from the page's Design View in Access, or through the methods and properties of the Data Source control object model.
Unlike the Office Web Components Chart, Spreadsheet, and PivotTable controls, the functionality of a data access page is not contained within a single ActiveX control (although a data access page, like any Web page, can incorporate Chart, Spreadsheet, and PivotTable controls).
At its most basic level, a data access page consists of the interaction of the Data Source control with a set of DIV elements that act as containers for the labels, controls and data-bound elements that make up the page. Extending the functionality of a data access page can require you to work with the DHTML Document Object Model, as well as the Data Source control object model. There are two primary reasons why:
- The primary elements that make up a typical data access page are intrinsic HTML controls and data-bound SPAN elements.
- User interactions with the page are often events that occur within the context of an HTML document.
Because the Data Source control is designed to integrate with HTML elements, its object model contains members such as the HTMLContainer property of the Section object that allow your script to access the collections, properties, and methods of the DHTML Document Object Model. This, in turn, enables you to work with the HTML elements that make up a data access page.
In addition to interacting with and integrating portions of the DHTML Document Object Model, the Data Source control encapsulates ADO functionality for working with the recordsets exposed through the page. Because these recordsets can be hierarchical and may contain calculated columns that are generated at run time (also known as shaped recordsets), the encapsulated ADO functionality operates in conjunction with the OLE DB MSDataShape Provider and the OLE DB Cursor Engine, in conjunction with the actual data provider for the data source itself.
While the Data Source control is designed to manage most of the details of working with these data access components for you, the Data Source control object model also includes members that allow you to access ADO objects; for example, the Connection property of the DataSourceControl object lets you access the ADO Connection object for the page's data source, and the Recordset property of the DataPage object lets you access the ADO Recordset object for a particular set of records displayed in a page.
While the Data Source control exposes a variety of collections and objects, the primary set of objects you need to understand and work with when customizing a data access page are:
- The Section object
- The GroupLevel object
- The DataPage object
- The ElementExtension object
While the GroupLevel, DataPage, and ElementExtension objects are accessible from the corresponding collections of the DataSourceControl object, there is no corresponding collection for Section objects. That's because Internet Explorer renders the actual section at run time. Instead of a corresponding collection for Section objects, the Data Source control object model provides a set of properties and methods for accessing a particular section on the page as described later in this article.
The following sections describe how these objects map to the components of a data access page, and how they relate to typical data access page customization tasks.
The names of the basic parts of a data access page in Design View can be somewhat confusing, so it's important to understand what they refer to and how these parts map to the relevant collections and objects of the Data Source control object model.
The underlying architecture of a data access page varies depending on whether a page utilizes hierarchical grouping. There are two basic kinds of data access pages:
- Simple pages – These pages display fields from a single record at a time with no repeated records or hierarchical grouping. While at run time a simple page has no grouping, in the terms of the underlying data model, it does have a single group level (
GroupLevels.Count = 1) and a set of properties associated with that group level. The Employees page in the Northwind Traders sample database is an example of a simple page.
- Banded pages – These pages display multiple records at a time in bands that are repeated down the page. A banded page can either have no hierarchical grouping (essentially a simple page with the DataPageSize property of its single group level set greater than 1), or it can display a hierarchy of related or grouped records. By default, banded pages with grouping display only the first level of hierarchy with lower levels of grouping collapsed beneath expand controls (+ icons). The Review Orders page in the Northwind Traders sample database is an example of a banded page with hierarchical grouping.
For additional information on distinguishing between simple and banded pages programmatically, see The GroupLevel Object later in this article.
The design surface of a data access page can consist of up to four kinds of sections:
- Caption – The first section of a simple page, or the first section of a group level in a banded page, the caption section is most typically used to display column headings for fields displayed in the following header section. However, the caption section can contain any type of control except data-bound controls. When you create a data access page in Design View, the caption section isn't added by default. To add a caption section, right-click a header section, and then click Caption.
- Header – While this section is called the header, it is functionally similar to the detail section of a form or report. The header section is primarily used to display data in data-bound controls and calculated values. A simple page has a single header section that shows one record at a time, whereas a banded page shows multiple records at a time repeating the header section for each record in a grouping level. Banded pages with multiple grouping levels contain nested header sections for each grouping level.
- Footer – The footer section is associated with a header section at the same grouping level. The footer section is typically used to display totals or subtotals for data displayed in the associated header section, although you can also add controls bound to fields and other controls. When you create a data access page in Design View, the footer section isn't added by default. To add a footer section, right-click a header section, and then click Footer. You can't add a footer section to a simple page, or to a banded page that has a single header section. On a page with grouping (pages with a hierarchy of two or more header sections), you can't add a footer section to the lowest (innermost) level in the grouping hierarchy.
- Record Navigation – The record navigation section is the last section of a simple page or a group level in a banded page, and is associated with a header section at the same grouping level. It contains a record navigation control, which is used to move between records, or to add, delete, save, undo changes to, sort, or filter records in the associated header section. The record navigation section can't contain data-bound controls.
At design time, a section can be thought of as a two-dimensional container for laying out the controls and elements of your data access page. When you start creating a data access page in Design View, Access adds a single unbound section that has no particular type. A section starts out as unbound, because at the start of the design process the Data Source control for the page doesn't have any Recordset objects defined in its Recordsets collection to bind to. There are two ways to bind data to a section:
- Dragging fields or tables from the Field List into the section
- Setting the RecordSource property in the section's property sheet
Binding the unbound section by either method makes the section a header section and automatically adds a navigation section associated with the new header section. Both binding mechanisms automatically add Recordset objects to the Recordsets collection of the Data Source control associated with the page. The fields defined by the generated Recordset objects become available for binding controls within the section by setting their ControlSource property to the desired field's name.
If you drag fields or tables from the Field List, Access sets the ID attribute for the control to the same name as the bound field (or generates a unique name if there are duplicate names) and sets the ControlSource property for the control to bind it.
If you bind a section by setting the RecordSource property, and then add controls to the section from the Toolbox, you must specify the ControlSource property for the control manually.
TipYou can see the organization of the data bound to a page (which is called the data model) by displaying the Data Outline. To display the Data Outline, open a data access page in Design View; point to Toolbars on the View menu, and then click Data Outline. You can view and set the properties of the items that make up the data model, such as GroupingDef, PageField, PageRowSource, and Recordset objects, by right-clicking an item in the outline, and then clicking Properties.
ImportantWhen Access binds a section to data, it sets the ID attribute of the <DIV> tag for the section to reflect the tables that the section is bound to. Don't change the ID attribute for a section in the Microsoft Script Editor, because it can interfere with the data binding for a page. Also, if you add other fields to a section in Design View, Access may change the ID attribute of a section. For this reason, you should complete the basic design for your data access page before adding scripts to the page. Otherwise, you'll need to update any <SCRIPT> tags and code that refers to old ID attributes for the section or the controls in that section.
At run time, the Section object functions as an abstraction for the HTML DIV element and the HTML controls and elements that make up a particular section. The Section object provides methods and properties that allow you to work with a section at runtime.
Working with Data-Bound Controls in a Section
When you manipulate the value of a data-bound control in a data access page, you're modifying the underlying 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 (by clicking the Save button on the record navigation control, by navigating off of the record, or by using the Save method of the DataPage object).
Accessing data-bound controls on a simple page
When you create data-bound controls in Design View by dragging a field or table from the Field List onto a data access page, Access sets the ID attribute of the controls to the same names as the bound fields. If you have a simple page, you can use a control's ID attribute to return or set the DHTML value property of the control. For example, in a simple (non-banded) page that contains a control bound to the CategoryName field, the following code will display the control's contents:
Similarly, to change the value of a field, you can use this syntax:
CategoryName.value = newvalue
If you have used a Bound Span control to display a read-only field value, you can't access its contents by using the DHTML value property because the underlying <SPAN> element doesn't support this property. In this case, you must use the DHTML innerText property to access the control's contents:
TipIf you want to access a field that you don't want shown on your data access page, you can create a hidden data-bound control. For the best performance, create a Bound Span control and then set its Visibility property to hidden.
Accessing data-bound controls on a banded page without grouping
If you are working with a banded page without a grouping hierarchy (a page with a single header section and the DataPageSize property of that group level set to a value greater than 1), the same field is displayed multiple times for each record's band in the header section. In this case, you can still refer to the bound control by its ID attribute, but all the values displayed in the section are returned as a zero-based array. For example, if you have a banded page that displays the value of the CategoryName field, you can return the value in the first record's band by using the following syntax.
You can determine the number of values in the array, and hence the number of records displayed in the section, by using the length property. For example:
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 i
TipThis technique is only useful if you need to loop through all of the records on a banded page without grouping. To reliably access a field for the current section, use the techniques described in the following sections of this article.
Accessing data-bound controls on a banded page with grouping
The techniques described above will work for simple pages and banded pages with no nested group levels, although banded pages with nested group levels are more complicated. For example, suppose you have a Category header section with a nested Products header section that contains a ProductName field. If the Products section is initially collapsed, you'll start out with no ProductName controls, so ProductName will be undefined. If the first Category band you expand contains only one record, you'll then have one ProductName control that can be accessed directly as a single value. When you expand the next Category band, you'll then have a ProductName array.
Any code you write that relies on the ProductName field being a single value or an array of values—or just the ProductName field being defined—won't work.
For this reason, there's another way to access the controls in a band: through the current section's HTMLContainer property, which gives you access to the <DIV> element that contains the controls.
To use the HTMLContainer property, start from the Data Source object (which always has an ID attribute of MSODSC on a data access page) using the CurrentSection property to access the current section:
The CurrentSection property returns a Section object for the section of the page that currently has the focus—that is, the last band the user clicked on or tabbed to. From that Section object, use the HTMLContainer property to access the HTML <DIV> element that contains the controls for that band:
Once you've accessed the DIV element for the band, you can use the DHTML children collection to access the controls within that band by using this syntax:
Since the current section can only have one control of each name, this always returns a single value, so you can use syntax like the following without problems:
If you need to access several controls within the current section, you can use a With…End With statement like the following:
With MSODSC.CurrentSection.HTMLContainer .children("LastName").value = "Davolio" .children("FirstName").value = "Nancy" End With
If you want to perform multiple operations on a single control, you can also assign the control to a variable as follows:
Set fldPhone = _ MSODSC.CurrentSection.HTMLContainer.children("phone") fldPhone.value = ...
NoteThe.children("elementname")syntax returns an error if there's no such element. If you're not sure that the element exists, use error handling.
Accessing a section from a control or other element on a page
Frequently, you have access to a control and you need to access other controls and elements from the section it's in. The Data Source control provides the GetContainingSection method for just this purpose:
In this case, element is any variable or expression that returns an element in the section. If you're working on a simple page, you can pass in the ID attribute of a control in the section (not surrounded by quotes), like this:
But if you're working on a banded page, you must pass in the ID attribute of a control along with the index to return a specific control. For example, if you're looping through the sections on the page, use this syntax:
For i = 0 to CategoryName.length-1 Set sec = MSODSC.GetContainingSection(CategoryName(i)) ... Next i
Additionally, if you're writing event procedure code for an element on the page, you can pass in the Me keyword to gain access to the section that contains the element:
<SCRIPT language=vbscript event=onclick for=Command1> <!-- Set sec = MSODSC.GetContainingSection(Me) ... --> </SCRIPT>
And, as yet another alternative, you can also use the DHTML srcElement property of the event object to access the current element and pass that to the GetContainingSection method:
Set sec = MSODSC.GetContainingSection(window.event.srcElement)
Once you have access to the section, you can use the HTMLContainer property of the containing section to access sibling objects from the DHTML children collection:
NoteThese examples show how to use a bound control. Of course, you can also use this technique to access any HTML element within the section's <DIV> element.
The Section object provides the NextSibling, PreviousSibling, NextSection, and PreviousSection properties to let you navigate from section to section. To determine which of these properties to use, you need to understand what a sibling is. Sibling sections refer all of the sections at the same grouping level. So if you want to move only between sections at the same grouping level, use the NextSibling and PreviousSibling properties. The NextSection and PreviousSection properties move between sections regardless of their grouping level, but can't access sections that are collapsed, so you can use these properties to move between all of the visible sections on a page.
The NextSibling, PreviousSibling, NextSection, and PreviousSection properties don't distinguish between what type of section they are navigating to. If you need to determine what type of section you have before acting on it, you can use the Type property of the Section object.
In Table 1 below, the Type property returns one of the listed SectTypeEnum constants, which have the specified literal value.
Table1. SectTypeEnum constants returned by the Type property
TipIf you want to use named constants that are defined for members of the Data Source control object model from script, you can use the Constants property of the DataSourceControl object. For example, the expressionMSODSC.Constants.sectTypeCaptionreturns the literal value of the sectTypeCaption constant.
If you need a starting point for navigating between sections, you can use the FirstSection property of the DataPage object to return the first section on the specified data page. For more information on the definition of a data page see The DataPage Object later in this article.
To test whether a section is expanded, you can use the IsExpanded property of the Section object. To expand or collapse sections, you can use the Expand and Collapse methods of the Section object.
The following code fragment is from the event procedure for the cmdExpandCollapse button on the Review Products data access page in the Northwind Traders sample database. It uses several of the properties and methods described above to loop through all of the sections on the page and expand all of the header sections.
' Access the first DataPage object of the page. Set dap = MSODSC.DataPages(0) ' Access the first section on the page. Set sect = dap.FirstSection ' Using the NextSibling property after accessing the last ' section on the page will set the sect variable to Nothing. While Not sect Is Nothing ' Make sure the section is not expanded and is a header ' section(sectTypeHeader = 2), and if so, expand the section. If Not sect.IsExpanded and sect.Type = 2 Then sect.Expand End If ' Navigate to the next sibling section. Set sect = sect.NextSibling Wend
Additionally, the Section object provides the ChildSection property for accessing the next section below the grouping level of the current section as long as that section is expanded. And, similarly, the ParentSection property returns the next section above the grouping level of the current section. You can set the focus to a specified section, and optionally scroll that section into view, by using the MakeCurrent method of the Section object.
The GroupLevel object represents the set of sections and records at a given level of the data access page hierarchy. As described previously, each grouping level can have up to four different sections: the caption section, header section, footer section, and record navigation section—plus all sections within a grouping level are referred to as siblings.
The GroupLevel object primarily acts as a bridge between the sections in that grouping level and the set of records to which it is bound. In addition to providing properties that let you define grouping itself, such as the GroupOn and GroupInterval properties that determine the interval used to group items of a given data type, the GroupLevel object also provides properties that control how a user can interact with the data in a group level, such as the AllowAdditions and AllowDeletions properties.
ImportantAlthough Access Help lists all of the properties of the GroupLevel object as being read/write, this is only true when you are using Visual Basic® for Applications code to work with a data access page that is open in Design View. At run time, all of the GroupLevel object properties except the DefaultSort and ExpandedByDefault properties are read-only. If you try to set any of the other properties of the GroupLevel object, the Data Source control will return, "Run-time error 26072 – Property cannot be set in this mode."
Grouping in data access pages is very similar to grouping in reports. However, unlike reports, even simple pages that display a flat set of records with no grouping hierarchy have one grouping level. Architecturally, this allows the GroupLevel object to provide a set of properties that are common to both simple and banded pages. Additionally, your code may need to distinguish between the types of pages; for example, the values for data-bound controls are exposed as single values for simple pages and as an array of values for banded pages.
You can use the Count property of the GroupLevels collection and the DataPageSize property of the GroupLevel object to determine whether you are working with a simple page or banded page. The Count property returns the number of group levels, and the DataPageSize property returns or sets the number of records displayed at time in a particular group level. If the Count property and DataPageSize property are both 1, then you are working with a simple page. If either property returns 1, then you are working with a banded page. The following code fragment tests to determine if the page is a banded or simple page before continuing:
If (MSODSC.GroupLevels(0).DataPageSize > 1) Or (MSODSC.GroupLevels.Count > 1) Then ' Code for working with a banded page here. Else ' Code for working with a simple page here. End If
For the most part, working with grouping in data access pages is a design-time activity. Grouping can be determined in a variety of ways in page Design View. For example, one way to determine grouping hierarchy is based on where you drop fields from the Field List. If you drop fields above an existing header section, you create a new section at a level of grouping above existing section. Similarly, if you drop a field below an existing section, you create a grouping below that section. You can also select a field and then click the Promote, Group by Table, or Demote buttons on the Page Design toolbar to create grouping. Additionally, you can work with a set of properties that apply to a group level by right-clicking in the header section of the group level, and then clicking Group Level Properties.
For more information on grouping in data access pages, search Access Help for "group records."
While the name of the DataPage object would seem to imply that it represents the data access page itself, this is not the case. A DataPage object represents one of the sets of records that are bound to the sections that comprise a grouping level. The DataSourceControl object maintains the DataPages collection that contains all of the DataPage objects for the page. A DataPage object exists only at run time, and is only created when necessary, such as when one of the repeated bands within a grouping level is expanded. As a result, on a banded page with two or more grouping levels, the number of DataPage objects in DataPages collection will vary depending on how many bands are expanded.
The following illustration will help you visualize how DataPage objects are associated with the sections on a banded page with two grouping levels.
Figure 2. DataPage objects
This illustration shows a total of three DataPage objects. The topmost grouping level has a single DataPage object (DataPages(0)) which represents a recordset that lists all of the countries and the number of customers there. The expanded bands from the second grouping level (Austria and Denmark) each have their own DataPage object that represent the recordsets for the customers in each country. These DataPage objects are added to the DataPages collection in the order the bands were expanded. Because the bands for the remaining countries are collapsed, no DataPage objects are associated with those bands.
As a general rule, for banded pages you can think of a DataPage object as representing the recordset bound to a set of sections that share a common record navigation control. The topmost node in the grouping hierarchy will have a single DataPage object, and each expanded node within that grouping hierarchy will have its own DataPage object.
A simple page has only one grouping level, and as a result will always have only one DataPage object associated with the single header section of the page.
The DataPage object provides recordset-level operations to the programmer, such as the MoveFirst, MoveLast, MoveNext and MovePrev methods that allow you to move between records in a given grouping level. The Filter and IsFilterOn properties and the ApplyFilter and ToggleFilter methods let you define and apply a client-side filter to a grouping level's records.
TipTo create a server-side filter, use the ServerFilter property of the RecordsetDef object.
The DataPage object also provides a Recordset property that lets you access the ADO Recordset object that is bound to a particular Section object. If you need to perform recordset operations that are beyond those provided for the DataPage object itself, you can use standard ADO code to work with the Recordset object for the section.
The DataPage object also provides a programmatic bridge between GroupLevel objects and Section objects. For example, the name of the recordset definition that is bound to a section is determined by the RecordSource property of the GroupLevel object. The following line of code shows how to access the name of the recordset definition that is bound to a section by using the GroupLevel property of the section's DataPage object.
As described previously in Navigating between sections, the DataPage object also provides the FirstSection property that lets you access the first section in a grouping level, as shown in the following code fragment:
' Access the first DataPage object of the page. Set dap = MSODSC.DataPages(0) ' Access the first section on the page. Set sect = dap.FirstSection
This is useful if you need to loop through all of the sections on a page.
The Data Source control maintains a collection of ElementExtension objects. An ElementExtension object is used to add custom data-related properties to HTML elements on the page, such as bound text boxes, list boxes, and span controls. The ElementExtension object architecture provided the developers of Access with a way to extend the properties of the HTML intrinsic controls on the page without cluttering the HTML.
ElementExtension objects exist primarily to support the data access page Design View, and for developers who want to create data access pages from Visual Basic code. Most of the properties of the ElementExtension object are read-only when accessed from script in the page at run time. To view the ElementExtension properties, you can set in Design View, right-click a control, click Element Properties, and then click the Data tab.
Although Access Help lists all of the properties of the ElementExtension object as being read/write, this is only true when you are using Visual Basic for Applications code to work with a data access page that is open in Design View. At run time, all of the ElementExtension object properties except for the DefaultValue, Format and TotalType properties are read-only. If you try to set any of the other properties of the ElementExtension object, the Data Source control will return, "Run-time error 26072 – Property cannot be set in this mode."
If you set one of the read/write properties of an ElementExtension object at run time, the Data Source control will not automatically repaint the displayed record to reflect this change. To force the record to repaint, retrieve the bookmark for the current record by using the Recordset property of the DataPage object, and set it back to itself. The following code fragment demonstrates how to change the Format property of a control to either "Currency" or "Euro Currency" based on the value selected in a combo box. This code is written as an event procedure for the DHTML onchange event of a combo box named FormatType that has two options: "Euro" or "Currency."
Dim sect Dim strFormat Dim eeUnitPrice ' Get the current section. Set sect = MSODSC.CurrentSection ' Get the selected format from the FormatType ' drop-down box. strFormat = sect.HTMLContainer.children("FormatType").value ' Get the ElementExtension object for the UnitPrice field. Set eeUnitPrice = MSODSC.ElementExtensions("UnitPrice") ' Set the Format property to the selected format, ' and then force the record to repaint by setting ' the Bookmark property for current record back to itself. If strFormat = "Euro" Then eeUnitPrice.Format="Euro Currency" sect.DataPage.Recordset.Bookmark=sect.DataPage.Recordset.Bookmark Else eeUnitPrice.Format="Currency" sect.DataPage.Recordset.Bookmark=sect.DataPage.Recordset.Bookmark End If
NoteThe Access Help topic for the Format property of the ElementExtension object doesn't list the formatting strings you can use to set this property. To view valid strings for this property, open a page in Design View, right-click a control, click Element Properties, and then view the settings available in the drop-down list of the Format property box on the Data tab.
The following code fragment shows how to bind a control to a field from Visual Basic for Applications code running in Access. To set ElementExtension properties for a given element, you must create the ElementExtension object first.
Dim ee As ElementExtension ' Add an element extension for the txtCustomerID control. Set ee = MSODSC.ElementExtensions.Add(ElementID:="txtCustomerID", _ FailIfThere:=False) ' Set the ControlSource property of the extension to point ' to the CustomerID field. ee.ControlSource = "CustomerID"
ImportantThe preceding code sample will only work when creating a data access page from Visual Basic for Applications code. It can't be used to bind a control from script at run time. For information on creating data access pages programmatically from Visual Basic code running in Microsoft Access, see Working with Data Access Pages in the Microsoft Office 2000/Visual Basic Programmer's Guide.
The basic steps for creating an event handler that works with events of the Data Source control are as follows:
- In Access, create or open a data access page in Design View.
- Right-click the page and then click Microsoft Script Editor on the shortcut menu.
- If the Document Outline window isn't currently displayed, navigate to the View menu and point to Other Windows, then click Document Outline.
- Click the Script Outline button in the Document Outline window, and then expand the Client Objects & Events folder.
- Find the MSODSC object in the list of objects (the Data Source control for a data access page is always named MSODSC), and then click the + icon to show the events for Data Source control.
- Double-click the event you want to work with.
- The Script Editor will insert an event handler for the event and position the cursor in the middle. The following example was created for the Focus event:
<SCRIPT for=MSODSC event=Focus language=vbscript> <!-- --> </SCRIPT>
- The Script Editor doesn't automatically insert arguments for events that require them. Because all Data Source control events return the DSCEventInfo object, you need to add an argument to pass that object into your event handler. You must provide this argument even if your handler doesn't work with the properties of the DSCEventInfo object, but in most cases you will be using a Data Source control event so that your code can work with these properties:
<SCRIPT for=MSODSC event=Focus(info) language=vbscript> <!-- Dim sect ' Use the Section property of the DSCEventInfo object to ' access the section that triggered the Focus event. Set sect = info.Section ' Display the name of the section with focus. MsgBox "Section with focus: " & sect.HTMLContainer.id --> </SCRIPT>
You can also create the same event handler as a subroutine by using the following syntax:
Sub MSODSC_Focus(info) Dim sect ' Use the Section property of the DSCEventInfo object to ' access the section that triggered the Focus event. Set sect = info.Section ' Display the name of the section with focus. MsgBox "Section with focus: " & sect.HTMLContainer.id End Sub
All Data Source control events take a single argument, which is a DSCEventInfo object. A DSCEventInfo object is a data structure that returns a set of properties that contain information about the event that was triggered. For consistency, the same structure is used for all events, but only the appropriate properties for each event are returned. (See Table 3, which summarizes the Data Source control events for a listing of the DSCEventInfo object properties supported by each event.)
Table 2 below lists the properties provided by the DSCEventInfo object. Property names followed by "(new)" were added to object model for the DSCEventInfo object in the Microsoft Office XP Web Components.
Table 2. DSCEventInfo object properties
|DataPage||Returns a DataPage object for the section that triggered the event.|
|DisplayAlert (new)||Returns or sets a DscDisplayAlert constant that indicates whether or not an alert will be displayed when the BeforeDelete and BeforeOverwrite events are triggered. This property is read/write.
The DscDisplayAlert constants are:
|Error||Returns an ADO Error object that contains information about the triggered event.|
|PercentComplete (new)||Returns a Long value that represents the completed portion of the current operation. A value of 100 indicates that the operation is complete. This property is supported only by the RecordsetSaveProgress event. This property is read-only.|
|ReturnValue||Returns or sets a Boolean value representing the return value for the specified event. You can cancel the default action for some events by setting this property to False. This property is read/write.|
|Section||Returns a Section object that represents the section that triggered the event.|
|Status (new)||Returns a DscStatusEnum constant that represents the status of the current event. This property is supported only by the AfterDelete event. This property is read-only.
The DscStatusEnum constants are:
Table 3 below summarizes the events provided by the Data Source control. Event names followed by "(new)" are new events that were added to object model for the Data Source control of the Microsoft Office XP Web Components.
In addition to working with Data Source control events, script behind a data access page may also need to work with DHTML events. However, in general, you should only need to script events that are fired when users interact with controls on the page, such as in the onclick event of a button, or the onchange event of a combo box control. Also, as mentioned above in the description for the RecordExit event, you may need to write script for the DHTML onbeforeunload event in situations where you need to notify users before they unload the page. The DHTML Document Object Model does include data-binding events such as onbeforeupdate and onafterupdate events that are exposed through the Data Source control as the BeforeUpdate and AfterUpdate events. For the most reliable interactions, you should use the data-binding events of the Data Source control, instead of the corresponding DHTML Document Object Model events.
In addition to new events for DataSourceControl object, the Microsoft Office XP Web Components provide several new methods and properties for the DataSourceControl object and its child objects. The following tables provide a brief summary of these new methods and properties. (For more information, see Microsoft Script Editor Help or locate the OWCVBA10.chm file, which contains the Microsoft Office XP Web Components Help, and open it directly.)
Table 4. New methods for the DataSourceControl object
|EuroConvert||You can use the EuroConvert method to convert a number to the euro or from the euro to a participating currency. You can also use it to convert a number from one participating currency to another by using the euro as an intermediary (triangulation). The EuroConvert method uses fixed conversion rates established by the European Commission. Returns a Double value.|
|ExportXML||Saves the current recordset as an XML file.|
|Nz||Use this method to return zero, a zero-length string (" "), or another specified value when a value is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression. Returns a Variant.|
|RefreshJetCache||Refreshes the data access page's connection with a Microsoft Access database.|
|SetRootRecordset||Sets the root recordset for the specified DataSourceControl object. Use this method to change the ADO recordset to which a data access page is bound.|
Table 5. New methods for the DataPage object
|ApplyFilter||Filters the record on a data access page based upon the currently selected field. Equivalent in functionality to the Filter by Selection button on the record navigation control.|
|SortAscending||Sorts a field on a data access page in ascending order based on the currently selected field. Equivalent in functionality to the Sort Ascending button on the record navigation control.|
|SortDescending||Sorts a field on a data access page in descending order based upon the currently selected field. Equivalent in functionality to the Sort Descending button on the record navigation control.|
|ToggleFilter||Toggles the state of the current filter that has been applied to the data access page. If the filter is active, calling this method deactivates the filter. Calling this method a second time reapplies the filter. Equivalent in functionality to the Filter Toggle button on the record navigation control.|
Table 6. New properties for the DataSourceControl object
|ConnectionFile||Returns or sets a String value that specifies the Office Database Connection (.odc) or Microsoft Data Link (.udl) file that is used to connect the data access page to a data source. This property is read/write.|
|IsDirty||Returns or sets a Boolean value that indicates whether the contents of a record on a data access page has changed since the last time it was saved. This property is read/write.|
|Offline||Returns a Boolean value indicating whether Microsoft Internet Explorer is in offline mode. This property is read-only.|
|OfflinePublication||Returns or sets a String value that represents the SQL Server publication to use when the data access page is taken offline. This property is read/write.|
|OfflineSource||Returns or sets a String value that represents the data source used when the data access page is offline. This property is read/write.|
|OfflineType||Returns or sets a DscOfflineTypeEnum constant that represents the type of connection used to persist the data when a data access page is taken offline. This property is read/write.
The DscOfflineTypeEnum constants are:
|UseXMLData||Returns or sets whether the data access page will bind to XML data. This property returns a Boolean value and is read/write.|
|XMLDataTarget||Returns or sets a String value that represents the location of the XML data to load or save. This property is read/write.|
|XMLLocation||Returns or sets a DscXMLLocationEnum constant that specifies whether the XML data is to be loaded or saved from an XML data file or an XML data island inside of the current data access page. This property is read/write.
The DscXMLLocationEnum constants are:
Table 7. New properties for the GroupLevel object
|AllowAdditions||Returns or sets a Boolean value that represents whether the user can add records to the specified group level. Set this property to False to prevent users from adding records to a group level. The default value is True. This property is read/write at design time and read-only at run time.|
|AllowDeletions||Returns or sets a Boolean value that represents whether the user can delete records from the specified group level. Set this property to False to prevent users from deleting records from a group level. The default value is True. This property is read/write at design time and read-only at run time.|
|AllowEdits||Returns or sets a Boolean value that represents whether the user can edit records in the specified group level. Set this property to False to prevent users from editing records in a group level. The default value is True. This property is read/write at design time and read-only at run time.|
|AlternateRowColor||Returns or sets a String value that represents the color to use for every other row in the specified group level. This property is read/write at design time and read-only at run time.|
|RecordSelector||Returns or sets a Boolean value that indicates whether or not to display the record selector for the specified group level. This property is read/write at design time and read-only at run time.|
Table 8. New property for the ElementExtension object
|Format||Returns or sets a String value that represents the number formatting for the specified element. This property is read/write.|
For more information about scripting in data access pages, see the following articles:
- Adding Custom Filtering and Search Functionality to a Page
- Binding a Page to a Disconnected Recordset
- Creating a Pop-up Data Access Page
- Customizing the Controls on a Data Access Page at Run Time
- Embedding an Existing Page in a Data Access Page
- Examples of Using Data Access Page Events to Add Custom Functionality
- Passing Parameters to a Data Access Page