Microsoft KB Archive/307253

= ACC2002: How to Print a Line Number for Each Record on a Data Access Page =

Article ID: 307253

Article Last Modified on 9/27/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q307253



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
By using the RunningSum property on a report, you can display a line number for each record. This article shows you how to use script and DataSourceControl events to do the same thing on a data access page.



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 &quot;page&quot; 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
 Start Microsoft Access, and then open the sample database Northwind.mdb. In the Database window, click Pages under Objects, and then click New. In the New Data Access Page dialog box, click Design View, and then click OK. If the toolbox is not visible, click Toolbox on the View menu. Make sure that the Control Wizards button in the toolbox is enabled. In the field list, expand the Customers table. Select the following fields from the field list:

CustomerID

CompanyName

ContactName

ContactTitle

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

Id: txtCounter

ReadOnly: True

</li> Save the page as dapNumbering .</li></ol>

Using Script to Number Records
<ol> On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li>  On the Edit menu, click Insert Script Block, and then click Client. This inserts SCRIPT tags as follows: </li>  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. </li> 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.

</li>  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(&quot;Customers&quot;).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(&quot;txtCounter&quot;).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(&quot;txtCounter&quot;).value

</li> Save the data access page.</li></ol>

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: <ol> In the Client Objects & Events box, click MSODSC. In the list of events, click BeforePreviousPage.</li>  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(&quot;Customers&quot;).DataPageSize then holdCounter = holdCounter - (MSODSC.GroupLevels(&quot;Customers&quot;).DataPageSize + intRowCount) Elseif intRowCount = MSODSC.GroupLevels(&quot;Customers&quot;).DataPageSize Then holdCounter = holdCounter - (MSODSC.GroupLevels(&quot;Customers&quot;).DataPageSize * 2) end if                         </li> Save the data access page.</li></ol>

<div class="references_section">