ACC2002: How to Print a Line Number for Each Record on a Data Access Page (307253)

MORE INFORMATION

Unless your page displays all records, you may have to account for navigation through records on the page. You may have to do this to prevent the numbering from being incremented as you scroll through sets of records. In the following list, a "page" refers to the group of records that is displayed when you browse through the data access page. Special circumstances to account for when you are navigating include:

• Moving from one page to the previous page.
• Moving from any page to the last page.
• Moving from any page to the first page.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Creating the Data Access Page

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. In the Database window, click Pages under Objects, and then click New.
3. In the New Data Access Page dialog box, click Design View, and then click OK.
4. If the toolbox is not visible, click Toolbox on the View menu. Make sure that the Control Wizards button in the toolbox is enabled.
5. In the field list, expand the Customers table.
6. Select the following fields from the field list:

   CustomerID
   CompanyName
   ContactName
   ContactTitle

7. At the top of the field list, click Add To Page.
8. In the Layout Wizard, click Tabular, and then click OK.
9. Add a text box to the right of the ContactTitle field.
10. On the View menu, click Properties, and then set the following properties:

    Id: txtCounter
    ReadOnly: True

11. Save the page as dapNumbering.

Using Script to Number Records

1. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
2. On the Edit menu, click Insert Script Block, and then click Client. This inserts SCRIPT tags as follows:

   <script language=vbscript>
      <!--
   
      -->
   </script>
   					

3. Enter the following code between the script tags:

   dim iCounter	' counter used to number records.
   icounter = 1	' intialize the counter when the page opens.
   dim holdcounter	' variable used to trap the last value in the page.
   dim intRowCount	' variable used to count the number of rows on the page.
   					

4. In the Client Objects & Events box, click MSODSC. In the list of events, click DataPageComplete.IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name as follows:

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

   The <I>oEventInfo</I> parameter returns specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script will not work without it.
   
   
5. Enter the following code between the script tags in the DataPageComplete event:

   Dim bandHTML, dscConst, sect, intRowCount
   	
   Set dscConst = msodsc.Constants
   set sect = oEventInfo.DataPage.FirstSection
   		
   ' Determines the number of rows on the current page.
   while not (sect is nothing)
   	if (sect.type = dscConst.sectTypeHeader) then
   		intRowCount = intRowCount + 1
   	End If
   		' moves to the next row
   		Set sect = sect.NextSibling
   wend
   
   ' Determines if you are currently on the last page. 
   if intRowCount < MSODSC.GroupLevels("Customers").DataPageSize and intRowCount > 1 then
   	iCounter = msodsc.DefaultRecordset.RecordCount - (intRowCount-1)
   Elseif intRowCount = 1 then
   	iCounter = msodsc.DefaultRecordset.RecordCount
   Else
   	' Determines if you are on the FirstPage.
      if msodsc.CurrentSection.DataPage.IsButtonEnabled(navbtnMovePrev) = False and _
   msodsc.CurrentSection.DataPage.IsButtonEnabled(navbtnMoveFirst) = false then
   		holdcounter = 0
   		introwcount = 1
   	end if
   	If holdcounter = 0 Then
   		iCounter = 1
   	Else
   		iCounter = holdcounter + 1
   	End If
   end if		
   ' Going back to the First row to number the records.		
   set sect = oEventInfo.DataPage.FirstSection
   while not (sect is nothing)
   	If (sect.type = dscConst.sectTypeHeader) Then
   		set bandHTML = sect.HTMLContainer
   		' Sets the value to the control.
   		bandHTML.Children("txtCounter").Value = iCounter
   		' Increments the counter.
   		iCounter = iCounter + 1
   	End If
   		'Moves to the next row.
   		Set sect = sect.NextSibling
   		
   wend
   ' Stores the value in a variable.
   holdcounter = bandHTML.Children("txtCounter").value 
   
   					

6. Save the data access page.

Accounting for Navigation

As mentioned earlier, if you do not account for navigation, the DataPageComplete event would continue to fire and increment the counter as you moved to previous sets of records. This would also cause incorrect numbering when you move between the first and last page of records.

Moving from One Page to the Previous page

When you move to a previous page, you must subtract the data page size from the first number on the current page. This results in the starting number for the first record that is displayed on the previous page. Retrieve the data page size from the appropriate GroupLevel on the page. To do so, follow these steps:

1. In the Client Objects & Events box, click MSODSC. In the list of events, click BeforePreviousPage.
2. Enter the following code between the script blocks:

   Dim bandHTML, dscConst, sect, intRowCount
   Set dscConst = msodsc.Constants
   set sect = oEventInfo.DataPage.FirstSection
   		
   ' Determine the Number of rows being displayed on the page.
   while not (sect is nothing)
   	if (sect.type = dscConst.sectTypeHeader) then
   		intRowCount = intRowCount + 1
   	End If
   		Set sect = sect.NextSibling
   wend
   
   if intRowCount < MSODSC.GroupLevels("Customers").DataPageSize then
   	holdCounter = holdCounter - (MSODSC.GroupLevels("Customers").DataPageSize + intRowCount)
   Elseif intRowCount = MSODSC.GroupLevels("Customers").DataPageSize Then
   	holdCounter = holdCounter - (MSODSC.GroupLevels("Customers").DataPageSize * 2)
   end if		
   					

3. Save the data access page.