Daryl said:Hi, I've a report using customer number as Group to force new page. Each
customer may have more than 1 page, maybe 2, 3.....
I would like to show the page number and number of pages for each customer's
report page. How can I do it?
I think you missed the point of the article, resetting Page
to 1 is trivial. The reason the article is not "easy" is
because it describes how to calculate and display the Pages
per group. It is a little tricky so it'll take some
homework to get a grip on how it works.
--
Marsh
MVP [MS Access]
Daryl said:I found a easier way to show the page numbe for each group at (which is from
the "reference link" in your link):
http://office.microsoft.com/en-us/access/HP051874011033.aspx
However, how do I show the number of page for each group? Thanks.
(or maybe say, how to count the number of page for each group)
"Marshall Barton" ???
I know your point, Marshall. And I've read the link below as well about
methods to make it work:
http://www.mvps.org/access/reports/rpt0013.htm
however, the result is "blank" in my report, although I followed step by step.
However, do i check where I got wrong? Thanks.
Daryl Au
"Marshall Barton" 來函：
I think you missed the point of the article, resetting Page
to 1 is trivial. The reason the article is not "easy" is
because it describes how to calculate and display the Pages
per group. It is a little tricky so it'll take some
homework to get a grip on how it works.
--
Marsh
MVP [MS Access]
Daryl said:I found a easier way to show the page numbe for each group at (which is from
the "reference link" in your link):
http://office.microsoft.com/en-us/access/HP051874011033.aspx
However, how do I show the number of page for each group? Thanks.
(or maybe say, how to count the number of page for each group)
"Marshall Barton" ???
Daryl Au wrote:
Hi, I've a report using customer number as Group to force new page. Each
customer may have more than 1 page, maybe 2, 3.....
I would like to show the page number and number of pages for each customer's
report page. How can I do it?
I would use a collection instead of a table, but other than
that the general idea in this article demonstrates how to do
that:
http://support.microsoft.com/kb/841779/en-us
Marshall Barton said:I think you missed the point of the article, resetting Page
to 1 is trivial. The reason the article is not "easy" is
because it describes how to calculate and display the Pages
per group. It is a little tricky so it'll take some
homework to get a grip on how it works.
--
Marsh
MVP [MS Access]
Daryl said:I found a easier way to show the page numbe for each group at (which is from
the "reference link" in your link):
http://office.microsoft.com/en-us/access/HP051874011033.aspx
However, how do I show the number of page for each group? Thanks.
(or maybe say, how to count the number of page for each group)
"Marshall Barton" ???
Daryl said:Here below is the codes I put into "PageFooter_Format" (whihc is copied from
the webpage but change the "Me.!Salesperson" to "Me![cust_no]"):
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
Private Sub PageFooter_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![cust_no]
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!ctlGrpPages = "Group Page " & GrpArrayPage(Me.page) & " of " &
GrpArrayPages(Me.page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
Do I have anything wrong?
"Daryl Au" ???I know your point, Marshall. And I've read the link below as well about
methods to make it work:
http://www.mvps.org/access/reports/rpt0013.htm
however, the result is "blank" in my report, although I followed step by step.
Daryl Au said:Here below is the codes I put into "PageFooter_Format" (whihc is copied from
the webpage but change the "Me.!Salesperson" to "Me![cust_no]"):
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
Private Sub PageFooter_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![cust_no]
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!ctlGrpPages = "Group Page " & GrpArrayPage(Me.page) & " of " &
GrpArrayPages(Me.page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
Do I have anything wrong?
Thanks so much
Daryl Au
"Daryl Au" ??:
I know your point, Marshall. And I've read the link below as well about
methods to make it work:
http://www.mvps.org/access/reports/rpt0013.htm
however, the result is "blank" in my report, although I followed step by step.
However, do i check where I got wrong? Thanks.
Daryl Au
"Marshall Barton" ??:
I think you missed the point of the article, resetting Page
to 1 is trivial. The reason the article is not "easy" is
because it describes how to calculate and display the Pages
per group. It is a little tricky so it'll take some
homework to get a grip on how it works.
--
Marsh
MVP [MS Access]
Daryl Au wrote:
I found a easier way to show the page numbe for each group at (which is from
the "reference link" in your link):
http://office.microsoft.com/en-us/access/HP051874011033.aspx
However, how do I show the number of page for each group? Thanks.
(or maybe say, how to count the number of page for each group)
"Marshall Barton" ???
Daryl Au wrote:
Hi, I've a report using customer number as Group to force new page. Each
customer may have more than 1 page, maybe 2, 3.....
I would like to show the page number and number of pages for each customer's
report page. How can I do it?
I would use a collection instead of a table, but other than
that the general idea in this article demonstrates how to do
that:
http://support.microsoft.com/kb/841779/en-us
Daryl said:Here below is the codes I put into "PageFooter_Format" (whihc is copied from
the webpage but change the "Me.!Salesperson" to "Me![cust_no]"):
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
Private Sub PageFooter_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![cust_no]
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!ctlGrpPages = "Group Page " & GrpArrayPage(Me.page) & " of " &
GrpArrayPages(Me.page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
Do I have anything wrong?
The steps per http://support.microsoft.com/kb/841779/en-us works fine for
renumbering the group pages. Also, by putting conditional statements in the
OnPrint event for the page footer I can make visible controls in the page
footer if it's page 1 of 1, and on the last page only if the group requires
more than one page. So, this part is working great.
(I gave up on the array method described at
http://www.mvps.org/access/reports/rpt0013.htm -- can't figure out the
"subsrcript out of range" error).
At the same time, however, I would like to make visible/invisible certain
controls in the page header also depending upon the number of pages in the
group and whether it's the first or last page, but cannot figure out a way
to do this. Referencing controls in the page footer which store the
(re-numbered) page values for the group doesn't work if I try to use those
values in the page header.
oops ... sorry, Marsh. I thought you had seen the code at
http://support.microsoft.com/kb/841779/en-us.
Following that link, I created a table in the FE:
Table Name: CategoryGroupPages
Field Name: DapID
Data Type: Long
Indexed: Yes (No Duplicates)
Field Name: PageNumber
Date Type: Number
Field Size: Long
PrimaryKey: DapID
< then, the following procedures in the report module:>
Option Compare Database
Option Explicit
Dim DB As Database
Dim GrpPages As Recordset
Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [CategoryGroupPages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("CategoryGroupPages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when a new group starts.
Page = 1
End Sub
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![PageNumber]
End If
End Function
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![PageNumber] < Me.Page Then
GrpPages.Edit
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![DapID] = Me![DapID]
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
End Sub
<end code>
Three text boxes are created in the page footer:
Text box: -----------------------------
Name: GroupXY
ControlSource: =GetGrpPages()
Visible: No
Text box: ------------------------------
Name: ReferToPages
ControlSource: =Pages
Visible: No
Text box: ------------------------------
Name: Text154
ControlSource: ="Page " & [Page] & " of " & [GroupXY]
Visible: Yes
Text box: ------------------------------
Name: Text190
ControlSource: =Page
Visible: No
<To make certain controls in the page footer visible/invisible, I used the
following procedure (using just one control as an example):>
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
Select Case Me!GroupXY 'GroupXY is total pages for the Group
Case Is = 1 'Group requires single page
Me!Line32.Visible = True
Case Is > 1 'Group requires multiple pages
Select Case Me!Text190 'Designates which page we're on
Case Is <> Me!GroupXY 'Not the last page
Me!Line32.Visible = False
Case Is = Me!GroupXY 'Is the last page
Me!Line32.Visible = True
End Select
End Select
End Sub
<end code>
......... If within the page header's OnPrint (or OnFormat) event I try to
reference the text boxes in the page footer (GroupXY, ReferToPages, Text154,
and Text190), the result is not as expected -- the group page numbers
displayed in the page header section do not match the numbers displayed in
the page footer.
I thought maybe I could use the same process in the page header section for
re-numbering pages that I used in the page footer section, but this would
entail ForceNewPage *Before* the Group section (as well as after it) --
which would be undesirable.
Daryl said:Here below is the codes I put into "PageFooter_Format" (whihc is copied from
the webpage but change the "Me.!Salesperson" to "Me![cust_no]"):
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
Private Sub PageFooter_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![cust_no]
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!ctlGrpPages = "Group Page " & GrpArrayPage(Me.page) & " of " &
GrpArrayPages(Me.page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
Do I have anything wrong?
I have never done it that way, but it looks like it should
work. Does it do what you want?
.... this works fine at the end of the Format event and is much more elegantIf Me.Pages > 0 Then
Me!Line32.Visible = (Me.Page = Me!GroupXY)
End If
Marshall Barton said:oops ... sorry, Marsh. I thought you had seen the code at
http://support.microsoft.com/kb/841779/en-us.
Yes I have studied it. I even referred the part in the
PageFooter Format event.
Following that link, I created a table in the FE:
Table Name: CategoryGroupPages
Field Name: DapID
Data Type: Long
Indexed: Yes (No Duplicates)
Field Name: PageNumber
Date Type: Number
Field Size: Long
PrimaryKey: DapID
< then, the following procedures in the report module:>
Option Compare Database
Option Explicit
Dim DB As Database
Dim GrpPages As Recordset
Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [CategoryGroupPages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("CategoryGroupPages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when a new group starts.
Page = 1
End Sub
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![PageNumber]
End If
End Function
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![PageNumber] < Me.Page Then
GrpPages.Edit
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![DapID] = Me![DapID]
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
End Sub
<end code>
Three text boxes are created in the page footer:
Text box: -----------------------------
Name: GroupXY
ControlSource: =GetGrpPages()
Visible: No
Text box: ------------------------------
Name: ReferToPages
ControlSource: =Pages
Visible: No
Text box: ------------------------------
Name: Text154
ControlSource: ="Page " & [Page] & " of " & [GroupXY]
Visible: Yes
Text box: ------------------------------
Name: Text190
ControlSource: =Page
Visible: No
<To make certain controls in the page footer visible/invisible, I used the
following procedure (using just one control as an example):>
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
Select Case Me!GroupXY 'GroupXY is total pages for the Group
Case Is = 1 'Group requires single page
Me!Line32.Visible = True
Case Is > 1 'Group requires multiple pages
Select Case Me!Text190 'Designates which page we're on
Case Is <> Me!GroupXY 'Not the last page
Me!Line32.Visible = False
Case Is = Me!GroupXY 'Is the last page
Me!Line32.Visible = True
End Select
End Select
End Sub
<end code>
......... If within the page header's OnPrint (or OnFormat) event I try to
reference the text boxes in the page footer (GroupXY, ReferToPages, Text154,
and Text190), the result is not as expected -- the group page numbers
displayed in the page header section do not match the numbers displayed in
the page footer.
I thought maybe I could use the same process in the page header section for
re-numbering pages that I used in the page footer section, but this would
entail ForceNewPage *Before* the Group section (as well as after it) --
which would be undesirable.
Fut, you can not do that kind of thing in most/all Print
events. Formatting, including setting the visible property,
is supposed to be done in the Format event.
Your Text190 text box is redundant. The code can use
Me.Page instead.
I would use something like this at the end of the footer's
Format event:
If Me.Pages > 0 Then
Me!Line32.Visible = (Me.Page = Me!GroupXY)
End If
After a fair amount thought, I'm pretty sure that doing
something similar for controls in the page header will not
work on the group's first page, because Page is not reset to
1 until the group header. (Add a text box with =Page to the
page header to see what it is on the first page in each
group.)
FPS said:Thank you, Marsh ...... this works fine at the end of the Format event and is much more elegantIf Me.Pages > 0 Then
Me!Line32.Visible = (Me.Page = Me!GroupXY)
End If
than what I was using. (Don't know why mine would only work in the Print
event).
I took your suggestion and placed a control in the page header (=Page).
Here's the results:
Group1 (single page), control = "1"
Group2 (two pages), first page = "2", second page = "2"
Group3 (single page), control = "3"
Group4 (single page), control = "2"
Group4 (two pages), first page = "2", second page = "2"
Group5 (single page), control = "3"
... not much rhyme or reason that I can see.
Anyhow, if I understand it correctly, if the page control is to be placed in
the page header, then the first page of each group is set to zero (0)
instead of 1, and the ForcePageAdvance is before the group, rather than
after.
I'll try running the same process but with the page control in the header,
setting the first page of each group to zero and making controls in the
header visible/not visible using the same method you showed me for the
footer.
If it's true that the page header cannot "see" what's in the page footer,
maybe the opposite holds true -- the page footer *can* see what's in the
header since the header is formatted first--? If so, then I should be able
to make use of the page control in the header to also turn on or turn off
controls in the footer --??
Thanks very much for your help, Marsh.
Mark
Marshall Barton said:oops ... sorry, Marsh. I thought you had seen the code at
http://support.microsoft.com/kb/841779/en-us.
Yes I have studied it. I even referred the part in the
PageFooter Format event.
Following that link, I created a table in the FE:
Table Name: CategoryGroupPages
Field Name: DapID
Data Type: Long
Indexed: Yes (No Duplicates)
Field Name: PageNumber
Date Type: Number
Field Size: Long
PrimaryKey: DapID
< then, the following procedures in the report module:>
Option Compare Database
Option Explicit
Dim DB As Database
Dim GrpPages As Recordset
Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [CategoryGroupPages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("CategoryGroupPages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when a new group starts.
Page = 1
End Sub
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![PageNumber]
End If
End Function
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![DapID]
If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![PageNumber] < Me.Page Then
GrpPages.Edit
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![DapID] = Me![DapID]
GrpPages![PageNumber] = Me.Page
GrpPages.Update
End If
End Sub
<end code>
Three text boxes are created in the page footer:
Text box: -----------------------------
Name: GroupXY
ControlSource: =GetGrpPages()
Visible: No
Text box: ------------------------------
Name: ReferToPages
ControlSource: =Pages
Visible: No
Text box: ------------------------------
Name: Text154
ControlSource: ="Page " & [Page] & " of " & [GroupXY]
Visible: Yes
Text box: ------------------------------
Name: Text190
ControlSource: =Page
Visible: No
<To make certain controls in the page footer visible/invisible, I used the
following procedure (using just one control as an example):>
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
Select Case Me!GroupXY 'GroupXY is total pages for the Group
Case Is = 1 'Group requires single page
Me!Line32.Visible = True
Case Is > 1 'Group requires multiple pages
Select Case Me!Text190 'Designates which page we're on
Case Is <> Me!GroupXY 'Not the last page
Me!Line32.Visible = False
Case Is = Me!GroupXY 'Is the last page
Me!Line32.Visible = True
End Select
End Select
End Sub
<end code>
......... If within the page header's OnPrint (or OnFormat) event I try to
reference the text boxes in the page footer (GroupXY, ReferToPages, Text154,
and Text190), the result is not as expected -- the group page numbers
displayed in the page header section do not match the numbers displayed in
the page footer.
I thought maybe I could use the same process in the page header section for
re-numbering pages that I used in the page footer section, but this would
entail ForceNewPage *Before* the Group section (as well as after it) --
which would be undesirable.
Fut, you can not do that kind of thing in most/all Print
events. Formatting, including setting the visible property,
is supposed to be done in the Format event.
Your Text190 text box is redundant. The code can use
Me.Page instead.
I would use something like this at the end of the footer's
Format event:
If Me.Pages > 0 Then
Me!Line32.Visible = (Me.Page = Me!GroupXY)
End If
After a fair amount thought, I'm pretty sure that doing
something similar for controls in the page header will not
work on the group's first page, because Page is not reset to
1 until the group header. (Add a text box with =Page to the
page header to see what it is on the first page in each
group.)
... this works fine at the end of the Format event and is much more elegant
than what I was using. (Don't know why mine would only work in the Print
event).
I took your suggestion and placed a control in the page header (=Page).
Here's the results:
Group1 (single page), control = "1"
Group2 (two pages), first page = "2", second page = "2"
Group3 (single page), control = "3"
Group4 (single page), control = "2"
Group4 (two pages), first page = "2", second page = "2"
Group5 (single page), control = "3"
... not much rhyme or reason that I can see.
Anyhow, if I understand it correctly, if the page control is to be placed in
the page header, then the first page of each group is set to zero (0)
instead of 1, and the ForcePageAdvance is before the group, rather than
after.
I'll try running the same process but with the page control in the header,
setting the first page of each group to zero and making controls in the
header visible/not visible using the same method you showed me for the
footer.
If it's true that the page header cannot "see" what's in the page footer,
maybe the opposite holds true -- the page footer *can* see what's in the
header since the header is formatted first--? If so, then I should be able
to make use of the page control in the header to also turn on or turn off
controls in the footer --??