Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page numbers (Access 2002, SP3)

    I have 5 reports, each containing numerous subreports. These reports print as if they were one report from a command button. I would like to be able to have page numbers on each of the 5 reports to run consecutively throughout all 5 reports. Is there a way to determine the total pages of each report even when it is closed so that when report two starts printing it would, through the use of code, determine that report 1named Goal1 contained 4 pages so the beginning page number for report 2 named Goal2 would start with 5.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    Others may have better solutions, but the way I'd approach this would be to define a global integer variable (say myPageCount) and a function that increments myPageCount by one every time it is called and outputs it. (Let's say the function is called myPageCountInc). I'd then place a field in the page footer (or header if you prefer) of each report using the formula =myPageCountInc(), which should output the overall page number. Remember to reset myPageCount to zero before opening the first report (or after opening the last one!).

    What this approach won't give you is the total page count across the 5 reports, so you won't be able to output "Page X of Y".

    Hope this helps,
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    Thanks for taking time to respond. In this instance this will not work since I do need to display Page x of y and they also need to have the functionality to print each report individually. I have discovered if I have each of my reports open and minimized, I can use code such as Reports!Goal1.pages + Reports!Goal2.pages+Reports!Goal3.pages+Reports!Go al4.pages+Reports!Goal5.pages to pull the total pages of each report and by add them together come up with the total pages of all five reports. My next question is what code can be used to open the reports in minimized preview mode? I tried variations of DoCmd.OpenReport "Goal1", acViewPreview, , , acHidden but when I do that the code can not see the report.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page numbers (Access 2002, SP3)

    Even if a report is opened hidden, VBA code should be able to retrieve information from it. To open it minimized, change acHidden to acIcon.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    I tried the following code for my print button:
    Dim G1Pg As Integer
    Dim G2Pg As Integer
    Dim G3Pg As Integer
    Dim G4Pg As Integer
    Dim G5Pg As Integer
    Dim AllPg As Integer
    DoCmd.OpenReport "Goal1", acViewPreview, , , acIcon
    G1Pg = Reports!Goal1.Pages
    DoCmd.Close acReport, "Goal1"
    DoCmd.OpenReport "Goal2", acViewPreview, , , acIcon
    G2Pg = Reports!Goal2.Pages
    DoCmd.Close acReport, "Goal2"
    DoCmd.OpenReport "Goal3", acViewPreview, , , acIcon
    G3Pg = Reports!Goal3.Pages
    DoCmd.Close acReport, "Goal3"
    DoCmd.OpenReport "Goal4", acViewPreview, , , acIcon
    G4Pg = Reports!Goal4.Pages
    DoCmd.Close acReport, "Goal4"
    DoCmd.OpenReport "Goal5", acViewPreview, , , acIcon
    G5Pg = Reports!Goal5.Pages
    DoCmd.Close acReport, "Goal5"
    AllPg = G1Pg + G2Pg + G3Pg + G4Pg + G5Pg
    DoCmd.OpenReport "Goal1", acViewNormal
    DoCmd.OpenReport "Goal2", acViewNormal
    DoCmd.OpenReport "Goal3", acViewNormal
    DoCmd.OpenReport "Goal4", acViewNormal
    DoCmd.OpenReport "Goal5", acViewNormal

    When I stop processing before my reports print, I can go to the immediate window and type ?AllPg and the answer appears. However if I reference the variable AllPg on my report it displays as #Name?

    I can't figure this out.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page numbers (Access 2002, SP3)

    You can *never* refer directly to a VBA variable on a form or report, that is not specific to this situation.

    In the first place, you should declare the page count variables as public variables, at the top of a standard module, not in the On Click event of a button:

    Public G1Pg As Integer
    ...
    Public AllPg As Integer

    You could create a function to retrieve the value of such a variable:

    Public Function GetAllPg() As Integer
    GetAllPg = AllPg
    End Function

    You can then place a text box on a report with control source

    =GetAllPg()

    You can use the Format property of the text box to hide zeros, for example a custom format <code>0;;" "</code>

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    In trying to follow your directions, I created a module containing the following code:
    Public G1Pg As Integer
    Public G2Pg As Integer
    Public G3Pg As Integer
    Public G4Pg As Integer
    Public G5Pg As Integer
    Public AllPg As Integer

    Public Function GetAllPg() As Integer
    GetAllPg = AllPg
    End Function

    Public Function GetG1Pg() As Integer
    GetG1Pg = G1Pg
    End Function

    Public Function GetG2Pg() As Integer
    GetG2Pg = G2Pg
    End Function

    Public Function GetG3Pg() As Integer
    GetG3Pg = G3Pg
    End Function

    Public Function GetG4Pg() As Integer
    GetG4Pg = G4Pg
    End Function

    Public Function GetG5Pg() As Integer
    GetG5Pg = G5Pg
    End Function

    The code in my on click for printing the report remained the same with the exception of having no variables defined.

    In my report for the Goal1 I placed the following code in a textbox: ="Page " & [Page] & " of " & GetAllPg()
    The report prints with #Error in place of the page number. Did I misunderstand your directions.

    Again if I go to the immediate window and type ?GetAllPg() I get the number 12 which is the correct number.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    I closed my database, opened it again and now it works great. Disreguard the problems in my previous posting.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page numbers (Access 2002, SP3)

    Are you sure you placed this code in a standard module, i.e. the type of module you create by clicking New in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor? It works correctly when I do that.

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    Yes, I placed it in a standard module. I have attached a sample of what I did with a very watered down version in order to get it under the 100K limit. It is not working at all now. Perhaps you can see what I have done wrong.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page numbers (Access 2002, SP3)

    You get 0 because your reports don't calculate the number of pages. Put a text box in the report footer of each of the main reports (Goal1, Goal2 and Goal3) with control source

    =[Pages]

    You can hide this text box by setting its Visible property to No. Referring to [Pages] forces Access to calculate the number of pages.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page numbers (Access 2002, SP3)

    That is amazing how such a simple solution solved this problem. Thank you so much.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •