How to reset page number per group

Hi,
I have report grouped by user and force user's data to next page. I would
like to number the page by user. That means it will start count from "1 of x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit nothing.

"Tran Hong Quang" wrote in message
...
Hi,
I have report grouped by user and force user's data to next page. I would
like to number the page by user. That means it will start count from "1 of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the line
after else never run.
If me.pages =0 then
....
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it is
under print or preview.
And one more thing, can I apply the code to make the page number on the page
header instead of page footer? In that case should I use PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit nothing.

"Tran Hong Quang" wrote in message
...
Hi,
I have report grouped by user and force user's data to next page. I would
like to number the page by user. That means it will start count from "1 of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

Not havng the original post or the link, I can only speculate.

Did you put a control on the report (preferably in the page footer) that has
Pages as its control source?
You can set the control's visible property to false if you don't want to see
it - which is probably the case in this situation.


"homer" wrote in message
...
Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work
for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the
line
after else never run.
If me.pages =0 then
...
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I
have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it
is
under print or preview.
And one more thing, can I apply the code to make the page number on the
page
header instead of page footer? In that case should I use
PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit
nothing.

"Tran Hong Quang" wrote in
message
...
Hi,
I have report grouped by user and force user's data to next page. I
would
like to number the page by user. That means it will start count from "1
of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

I have added a text box in the page footer, and in its property window,
control source, I tried Pages, me!Pages, =Pages, or =me!pages. None of them
seems work. What did I do wrong?

"John Spencer" wrote:

Not havng the original post or the link, I can only speculate.

Did you put a control on the report (preferably in the page footer) that has
Pages as its control source?
You can set the control's visible property to false if you don't want to see
it - which is probably the case in this situation.


"homer" wrote in message
...
Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work
for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the
line
after else never run.
If me.pages =0 then
...
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I
have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it
is
under print or preview.
And one more thing, can I apply the code to make the page number on the
page
header instead of page footer? In that case should I use
PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit
nothing.

"Tran Hong Quang" wrote in
message
...
Hi,
I have report grouped by user and force user's data to next page. I
would
like to number the page by user. That means it will start count from "1
of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

I went back to serach other posts and found FredG's. That solved my syntax
problem.
Thanks John! You are right, adding a control with pages as its control
source is a key part of this solution. Too bad the original James Brooks post
did not make it clear.

"homer" wrote:

I have added a text box in the page footer, and in its property window,
control source, I tried Pages, me!Pages, =Pages, or =me!pages. None of them
seems work. What did I do wrong?

"John Spencer" wrote:

Not havng the original post or the link, I can only speculate.

Did you put a control on the report (preferably in the page footer) that has
Pages as its control source?
You can set the control's visible property to false if you don't want to see
it - which is probably the case in this situation.


"homer" wrote in message
...
Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work
for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the
line
after else never run.
If me.pages =0 then
...
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I
have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it
is
under print or preview.
And one more thing, can I apply the code to make the page number on the
page
header instead of page footer? In that case should I use
PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit
nothing.

"Tran Hong Quang" wrote in
message
...
Hi,
I have report grouped by user and force user's data to next page. I
would
like to number the page by user. That means it will start count from "1
of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang

On Mon, 28 Nov 2005 09:19:09 -0800, homer wrote:

I have added a text box in the page footer, and in its property window,
control source, I tried Pages, me!Pages, =Pages, or =me!pages. None of them
seems work. What did I do wrong?

"John Spencer" wrote:

Not havng the original post or the link, I can only speculate.

Did you put a control on the report (preferably in the page footer) that has
Pages as its control source?
You can set the control's visible property to false if you don't want to see
it - which is probably the case in this situation.

"homer" wrote in message
...
Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work
for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the
line
after else never run.
If me.pages =0 then
...
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I
have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it
is
under print or preview.
And one more thing, can I apply the code to make the page number on the
page
header instead of page footer? In that case should I use
PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit
nothing.

"Tran Hong Quang" wrote in
message
...
Hi,
I have report grouped by user and force user's data to next page. I
would
like to number the page by user. That means it will start count from "1
of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang






Things to make sure of:



1) Did you DIM the variables up in the Declaration section of the
code:

Option Compare Database
Option Explicit

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
===============

2) Add a control to the page Footer that computes [Pages]
= [Pages]
You can make this control not visible if you wish.

3) Add an unbound control to the Page Footer.
Name this control "ctlGrpPages"

4) Try placing the Group field name within brackets:
Me!txtPageNum = [head_order_nbr] & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Well, it seems to work, but I have not achieved my goal. The code runs
flawlessly, but the outcome is not what I have expected.
It shows
Order 123 Page 1 of 3
Order 123 Page 2 of 3
Order 456 Page 3 of 3

And I am expecting to see:
Order 123 Page 1 of 2
Order 123 Page 2 of 2
Order 456 Page 1 of 1

When I stepped through the code, it seems the first pass is assigning the
right numbers to both arrays, i.e.
GrpArrayPage(1) =1, GrpArrayPages(1)=1
GrpArrayPage(2) =2, GrpArrayPages(2)=2
GrpArrayPage(3) =1, GrpArrayPages(3)=1

However, when the second pass code renders the arrays, it does not checking
the condition of GrpNameCurrent and GrpNamePrevious. So the outcome is no
different than directly put page/pages on the report.

I’m lost. Please help! Thanks!


"fredg" wrote:

On Mon, 28 Nov 2005 09:19:09 -0800, homer wrote:

I have added a text box in the page footer, and in its property window,
control source, I tried Pages, me!Pages, =Pages, or =me!pages. None of them
seems work. What did I do wrong?

"John Spencer" wrote:

Not havng the original post or the link, I can only speculate.

Did you put a control on the report (preferably in the page footer) that has
Pages as its control source?
You can set the control's visible property to false if you don't want to see
it - which is probably the case in this situation.

"homer" wrote in message
...
Bruce or any other Access MVPs,

That was a great link.
I want to use James Brooks' code on my report, but haven't made it work
for
me.
I am sure you can give me some help.

It did not print any page number. When I stepped through it, I saw the
line
after else never run.
If me.pages =0 then
...
else
me.ctlGrpPages =...
End If
Or to put it in another way, my report never run two passes, even though I
use the total pages property as the post suggested, and that supposed to
force access to format the report twice.
When I have one page report, it runs into If me.pages = 0 once, when I
have
two pages, me.pages=0 got run twice, but me.pages never 0, no matter it
is
under print or preview.
And one more thing, can I apply the code to make the page number on the
page
header instead of page footer? In that case should I use
PageHeader_Format()?

Here is my revised code for the page footer:
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!head_order_nbr
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!txtPageNum = head_order_nbr & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

Thanks!

"BruceM" wrote:

http://www.mvps.org/access/reports/rpt0013.htm

It's simpler than it looks at first. Just read carefully, and omit
nothing.

"Tran Hong Quang" wrote in
message
...
Hi,
I have report grouped by user and force user's data to next page. I
would
like to number the page by user. That means it will start count from "1
of
x"
for next user.

How to do that?

Thanks for any help
Tran Hong Quang






Things to make sure of:



1) Did you DIM the variables up in the Declaration section of the
code:

Option Compare Database
Option Explicit

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
===============

2) Add a control to the page Footer that computes [Pages]
= [Pages]
You can make this control not visible if you wish.

3) Add an unbound control to the Page Footer.
Name this control "ctlGrpPages"

4) Try placing the Group field name within brackets:
Me!txtPageNum = [head_order_nbr] & "-Page " & GrpArrayPage(Me.Page) &
" of " & GrpArrayPages(Me.Page)

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail