If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reset Page of Pages in Multipage reports
When we produce laboratory reports there can be 100+ pages that is the
[Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen -- Glen Simple Scientist aka Microbiologist Brisbane Australia |
#2
|
|||
|
|||
Reset Page of Pages in Multipage reports
Hi Glen,
This should help: http://www.mvps.org/access/reports/rpt0013.htm Rob Glen in Australia wrote: When we produce laboratory reports there can be 100+ pages that is the [Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen |
#3
|
|||
|
|||
Reset Page of Pages in Multipage reports
Rob thanks for the web Page I have implemented it as a header but it is still
not working - in fact the control (I called TotalPages) does not appear, yes it is set to Visible.. my code is Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub -- Glen Simple Scientist aka Microbiologist Brisbane Australia "Rob Parker" wrote: Hi Glen, This should help: http://www.mvps.org/access/reports/rpt0013.htm Rob Glen in Australia wrote: When we produce laboratory reports there can be 100+ pages that is the [Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen |
#4
|
|||
|
|||
Reset Page of Pages in Multipage reports
Hi Glen,
Sorry for the delay in responding - I didn't return to the newsgroups later yesterday, and I've not actually used that code before, so I've just done a bit of testing this morning (I'm also in Oz), and it took a little while to get it working. At first I thought it may have been because you are implementing this in the page header, but that's not the problem. The first obvious problem is with your declarations: you have moved the first three Dim statements to within the subroutine code; they must be above any code, and below the Option Compare Database and Option Explicit lines - exactly as shown in the listing on the web page. If you have any existing Option statements and/or code, make sure that there is only one set of Option statements (at the top of your code module - the only things which can appear above these lines are comments) and that the Dim statements for the Grp... entries are above any other subroutines or functions. So, assuming there is no other code (or that any other code is below this), you code needs to start as follows: '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Option Compare Database Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer ....Next, your page header section in the report must be named the same as the name in the Sub declaration. I notice that, by default, mine (in Access 2003) are named PageHeaderSection. You can either change the name of the header section in the report, or change the name of the header section in the Sub declaration - ie. make it Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer). The important thing is that they must match. And last, but certainly not least - you need a control in your report which is bound to Pages, to force the code to run two passes (as discussed in the article); the easiest way is to put a textbox in your page header, set its control source to =[Pages], and set its Visible property to No. And that should do it ... Rob Glen in Australia wrote: Rob thanks for the web Page I have implemented it as a header but it is still not working - in fact the control (I called TotalPages) does not appear, yes it is set to Visible.. my code is Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub Hi Glen, This should help: http://www.mvps.org/access/reports/rpt0013.htm Rob Glen in Australia wrote: When we produce laboratory reports there can be 100+ pages that is the [Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen |
#5
|
|||
|
|||
Reset Page of Pages in Multipage reports
Rob - Getting closer - but not there quite yet !
I have the field BREF, which is the Report Number in the header - reports are sorted by this field - but there are no GROUP headers or Footers or any sorting or grouping on this report. I have a text box in header (no to visible) with control =[Pages] I have a Test box in header Name = TotalPages with blank control my code is below But all I am getting on each report in the TotalPages field is " Pages of" no numbers, so I can only quess there is something wrong with my logic in the code.... What do you think ? '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks Option Compare Database Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub -- Glen IN BRISBANE Simple Scientist aka Microbiologist Brisbane Australia "Rob Parker" wrote: Hi Glen, Sorry for the delay in responding - I didn't return to the newsgroups later yesterday, and I've not actually used that code before, so I've just done a bit of testing this morning (I'm also in Oz), and it took a little while to get it working. At first I thought it may have been because you are implementing this in the page header, but that's not the problem. The first obvious problem is with your declarations: you have moved the first three Dim statements to within the subroutine code; they must be above any code, and below the Option Compare Database and Option Explicit lines - exactly as shown in the listing on the web page. If you have any existing Option statements and/or code, make sure that there is only one set of Option statements (at the top of your code module - the only things which can appear above these lines are comments) and that the Dim statements for the Grp... entries are above any other subroutines or functions. So, assuming there is no other code (or that any other code is below this), you code needs to start as follows: '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Option Compare Database Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer ....Next, your page header section in the report must be named the same as the name in the Sub declaration. I notice that, by default, mine (in Access 2003) are named PageHeaderSection. You can either change the name of the header section in the report, or change the name of the header section in the Sub declaration - ie. make it Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer). The important thing is that they must match. And last, but certainly not least - you need a control in your report which is bound to Pages, to force the code to run two passes (as discussed in the article); the easiest way is to put a textbox in your page header, set its control source to =[Pages], and set its Visible property to No. And that should do it ... Rob Glen in Australia wrote: Rob thanks for the web Page I have implemented it as a header but it is still not working - in fact the control (I called TotalPages) does not appear, yes it is set to Visible.. my code is Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub Hi Glen, This should help: http://www.mvps.org/access/reports/rpt0013.htm Rob Glen in Australia wrote: When we produce laboratory reports there can be 100+ pages that is the [Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen |
#7
|
|||
|
|||
Reset Page of Pages in Multipage reports
Follow-up: in a private email message to me, Glen advised that the problem
was a data-related issue in the report causing the report to crash before completion. The solution and notes on its implementation I gave in this thread work as expected. Rob Rob Parker wrote: Hi Glen, I'm not sure exactly what your problem is. The fact that you're getting the "Pages of " text means that the code is running, but I'm at a loss as to why the arrays GrpArrayPage and GrpArrayPages have no values - they should have. There is nothing obviously amiss with the code. And there is no requirement for there to be any grouping in the report - it will simply treat the field you give it as the field on which to group. I'm assuming that you've got the Force New page setting of your Detail section set to "After Section" to ensure that each record will appear start on a new page. If you'd like me to take a closer look at this, you can send a (stripped-down) copy of your database (just the table/query required for the report, and the report) to me at RME (after removing the obvious - I don't want my email address posted to a newsgroup). If you're using Access 2007, please convert to an earlier version before sending. Rob Glen in Australia wrote: Rob - Getting closer - but not there quite yet ! I have the field BREF, which is the Report Number in the header - reports are sorted by this field - but there are no GROUP headers or Footers or any sorting or grouping on this report. I have a text box in header (no to visible) with control =[Pages] I have a Test box in header Name = TotalPages with blank control my code is below But all I am getting on each report in the TotalPages field is " Pages of" no numbers, so I can only quess there is something wrong with my logic in the code.... What do you think ? '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks Option Compare Database Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub Hi Glen, Sorry for the delay in responding - I didn't return to the newsgroups later yesterday, and I've not actually used that code before, so I've just done a bit of testing this morning (I'm also in Oz), and it took a little while to get it working. At first I thought it may have been because you are implementing this in the page header, but that's not the problem. The first obvious problem is with your declarations: you have moved the first three Dim statements to within the subroutine code; they must be above any code, and below the Option Compare Database and Option Explicit lines - exactly as shown in the listing on the web page. If you have any existing Option statements and/or code, make sure that there is only one set of Option statements (at the top of your code module - the only things which can appear above these lines are comments) and that the Dim statements for the Grp... entries are above any other subroutines or functions. So, assuming there is no other code (or that any other code is below this), you code needs to start as follows: '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Option Compare Database Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer ....Next, your page header section in the report must be named the same as the name in the Sub declaration. I notice that, by default, mine (in Access 2003) are named PageHeaderSection. You can either change the name of the header section in the report, or change the name of the header section in the Sub declaration - ie. make it Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer). The important thing is that they must match. And last, but certainly not least - you need a control in your report which is bound to Pages, to force the code to run two passes (as discussed in the article); the easiest way is to put a textbox in your page header, set its control source to =[Pages], and set its Visible property to No. And that should do it ... Rob Glen in Australia wrote: Rob thanks for the web Page I have implemented it as a header but it is still not working - in fact the control (I called TotalPages) does not appear, yes it is set to Visible.. my code is Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) '************ Code Start ************* ' This code was originally written by James H Brooks. ' It is not to be altered or distributed, ' except as part of an application. ' You are free to use it in any application, ' provided the copyright notice is left unchanged. ' ' Code Courtesy of ' James H Brooks ' Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!BREF If GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1 GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage End If Else Me!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrent '************ Code End ************* End Sub Hi Glen, This should help: http://www.mvps.org/access/reports/rpt0013.htm Rob Glen in Australia wrote: When we produce laboratory reports there can be 100+ pages that is the [Pages] value is preset. Of these reports some are multiple page reports whereas most are 1 page only. I need each report to say in footer Page X of Page N Where for one page report this is Page 1 of 1 And for multipage reports - Page 1 of 3, Page 2 of 3, Page 3 of 3 I can reset [Page] by using a macro to [Page] Value to Expression 1 and evoking this macro in the onformat property of the group header and ForceNewPage property to After Section of the group footer But I can't reset [Pages] as it is a read only property ! How can I get around this problem Many thanks Glen |
Thread Tools | |
Display Modes | |
|
|