Microsoft KB Archive/100156

From BetaArchive Wiki
Knowledge Base


Article ID: 100156

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q100156

Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

In Microsoft Access reports, page numbering always starts with the number 1. However, you may want your report to have page numbering that starts with some number other than 1. This article describes two methods for defining custom starting-page numbers. Both methods provide custom page numbers; however, the second method uses Visual Basic for applications and can therefore include error checking. This method is more complex, but avoids the possibility that a nonnumeric value will be entered when you are prompted for a starting number.

MORE INFORMATION

Method 1

  1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0)
  2. Create a new report based on Employees table.
  3. Add any fields that you want by dragging them from the fields list.
  4. If they aren't yet shown on the report, click Page Header/Footer on the View menu to add a page header and footer.
  5. In Microsoft Access 7.0 and 97:

    On the Insert menu, click Page Numbers, and then click OK.

    In Microsoft Access 1.x and 2.0:

    Create a new unbound text box in the header or footer section.
  6. Click to select the new field, and then on the View menu, click Properties.
  7. Change the ControlSource property to read as follows:

          ="Page " & [Page] + [Enter a Starting Page Number] - 1

Print or preview the report. Note that you are prompted for a starting page number. Each page will be numbered consecutively starting with this number.

Method 2

This part of the article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

  1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0).
  2. Create a new module and type the following code:

          '******************************************************
          'Declarations Section of Module
          '******************************************************
          Option Explicit
          Global PageChoice As Integer
    
          '===========================================================
          'Create the following GetPageChoice() function in the Module
          '===========================================================
          'This function is called in the OnOpen property of the Report.
          Function GetPageChoice ()
             Dim choice As String
             Do
                choice = InputBox("Enter a Starting Page Number:"," _
                              Number Report", "1")
                If Not (IsNumeric(choice)) Then
                   MsgBox "Value Entered is not a Number."
                End If
             Loop While Not (IsNumeric(choice))
             PageChoice = CInt(choice)
          End Function
    
          '==============================================================
          'Create the following ReturnPageChoice() function in the Module
          '==============================================================
          'This function is called by the text box that will contain the
          ' pagenumber.
          Function ReturnPageChoice (pgnumber As Integer)
             ReturnPageChoice = PageChoice + pgnumber - 1
          End Function
                            
  3. Compile the module, and then save it as basPages.
  4. Open the Summary Of Sales By Quarter report in Design view.
  5. On the Edit menu, click Select Report, and then on the View menu, click Properties to display the property sheet. Change the OnOpen property to read as follows:

          OnOpen: =GetPageChoice()
  6. Select the PageNumber text box in the footer by clicking it. Change the ControlSource property to read as follows:

          ="Page " & ReturnPageChoice(Page).

Print or preview the report. Note that you are prompted for a starting page number. Each page will be numbered consecutively starting with this number.

REFERENCES

For more information about the Page property, search for Page Numbers, and then Page, Pages Properties using the Microsoft Access 97 Help Index.

For more information about looping structures, search for loops using the Microsoft Access 97 Help Index.

Keywords: kbhowto kbprogramming kbusage KB100156