Restart Page Numbers In Groups

[Pookey]

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

 

[fredg]

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

 

[Pookey]

Are you supposed to paste the code in the text box "ctlGrpPages" or paste it
in the footer? Sorry, but I'm a little confused because it's not working.

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

 

[fredg]

Are you supposed to paste the code in the text box "ctlGrpPages" or paste it
in the footer? Sorry, but I'm a little confused because it's not working.

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

Paste the code into the Report's Report Footer Format event.
#2 in my previous reply.

Here is how you write (or Paste) code.

First, select all of the code from the web site (everything BETWEEN
the
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)
line and the
End Sub
line)
Copy the selected text to the clipboard.

Then open the report in Design View.
Right-click on the Page Footer section.
Select Properties.
Click on the Event tab.
On the Format line write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between these 2 lines write (or in this case, Paste) your code.

Follow the instructions I included in my previous post about changing
the code to reflect your actual field names.
Save the code.

 

[Pookey]

The page numbering is still not working. See below the code I have on the
footer. I know this is probably simple but there must be something I'm
missing.

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 'Department Name
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

Are you supposed to paste the code in the text box "ctlGrpPages" or paste it
in the footer? Sorry, but I'm a little confused because it's not working.

On Fri, 19 Sep 2008 13:28:00 -0700, Pookey wrote:

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

Paste the code into the Report's Report Footer Format event.
#2 in my previous reply.

Here is how you write (or Paste) code.

First, select all of the code from the web site (everything BETWEEN
the
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)
line and the
End Sub
line)
Copy the selected text to the clipboard.

Then open the report in Design View.
Right-click on the Page Footer section.
Select Properties.
Click on the Event tab.
On the Format line write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between these 2 lines write (or in this case, Paste) your code.

Follow the instructions I included in my previous post about changing
the code to reflect your actual field names.
Save the code.

 

[fredg]

The page numbering is still not working. See below the code I have on the
footer. I know this is probably simple but there must be something I'm
missing.

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 'Department Name
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

Are you supposed to paste the code in the text box "ctlGrpPages" or paste it
in the footer? Sorry, but I'm a little confused because it's not working.

:

On Fri, 19 Sep 2008 13:28:00 -0700, Pookey wrote:

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

Paste the code into the Report's Report Footer Format event.
#2 in my previous reply.

Here is how you write (or Paste) code.

First, select all of the code from the web site (everything BETWEEN
the
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)
line and the
End Sub
line)
Copy the selected text to the clipboard.

Then open the report in Design View.
Right-click on the Page Footer section.
Select Properties.
Click on the Event tab.
On the Format line write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between these 2 lines write (or in this case, Paste) your code.

Follow the instructions I included in my previous post about changing
the code to reflect your actual field names.
Save the code.

This line:
GrpNameCurrent = Me 'Department Name
is not correct.

Perhaps you meant:

GrpNameCurrent = Me![Department Name]

or perhaps not. In any event
Me 'Department Name
is not correct. The ' is a comment character. Anything after the
apostrophe on that line will be ignored when the code is run.
*** Look at #3 in my first reply. ***
Note also, that if "Department Name" is the correct name of the field
you are grouping on, then it MUST be enclosed within brackets, i.e.
[Department Name] because it contains a space within the name.

 

[Pookey]

I should have know about the the field being in brackets since you have to do
that in queries. It just didn't occur to me doing it in the code. I changed
"Department Name" to have brackets around it and it still says page 1 of 17
instead of 1 of 1. I'm missing something somewhere.

Help!

The page numbering is still not working. See below the code I have on the
footer. I know this is probably simple but there must be something I'm
missing.

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 'Department Name
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

On Mon, 22 Sep 2008 06:35:01 -0700, Pookey wrote:

Are you supposed to paste the code in the text box "ctlGrpPages" or paste it
in the footer? Sorry, but I'm a little confused because it's not working.

:

On Fri, 19 Sep 2008 13:28:00 -0700, Pookey wrote:

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Paste the code into the Report's Report Footer Format event.
#2 in my previous reply.

Here is how you write (or Paste) code.

First, select all of the code from the web site (everything BETWEEN
the
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)
line and the
End Sub
line)
Copy the selected text to the clipboard.

Then open the report in Design View.
Right-click on the Page Footer section.
Select Properties.
Click on the Event tab.
On the Format line write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between these 2 lines write (or in this case, Paste) your code.

Follow the instructions I included in my previous post about changing
the code to reflect your actual field names.
Save the code.

This line:
GrpNameCurrent = Me 'Department Name
is not correct.

Perhaps you meant:

GrpNameCurrent = Me![Department Name]

or perhaps not. In any event
Me 'Department Name
is not correct. The ' is a comment character. Anything after the
apostrophe on that line will be ignored when the code is run.
*** Look at #3 in my first reply. ***
Note also, that if "Department Name" is the correct name of the field
you are grouping on, then it MUST be enclosed within brackets, i.e.
[Department Name] because it contains a space within the name.

 

[fredg]

I should have know about the the field being in brackets since you have to do
that in queries. It just didn't occur to me doing it in the code. I changed
"Department Name" to have brackets around it and it still says page 1 of 17
instead of 1 of 1. I'm missing something somewhere.

Help!

*** snipped ***

Did you remove the ' from that line and replace it with the !?

I can't 'see' your report.
The code and the method I suggested to you does work. You'll have to
double-check your control names, and code, etc.

 

[Pookey]

This is the code in "on format" in the page footer:

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![Department Name]
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

There is also a text box in the fotter with the following in it:

="Page" & [Page]& "of" &[Pages]

Do you see anything that looks wrong?

Thanks so much for your input with this. I really appreciate your time.

 

[fredg]

This is the code in "on format" in the page footer:

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![Department Name]
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

There is also a text box in the fotter with the following in it:

="Page" & [Page]& "of" &[Pages]

Do you see anything that looks wrong?

Thanks so much for your input with this. I really appreciate your time.

*** snipped ***

Did you remove the ' from that line and replace it with the !?

I can't 'see' your report.
The code and the method I suggested to you does work. You'll have to
double-check your control names, and code, etc.

If you are using Access 2003 or older, if you wish, you can send me a
sample copy of your database; just whatever is needed to run the
report. If you strip out un-needed stuff, make sure you try the report
before sending it.
Please do not send any data that might be construed as confidential.
I'll look at it and get back to you.

Send it to

jandf
at
roadrunner
dot
com

Make sure you write "Report Database" in the email subject line.
I'll be gone for several hours, so it won't be until later that I get
back to you.

If you are using Access 2007, you will have to convert the sample
database to an earlier version. I cannot open a .accdb database.

 

[fredg]

This is the code in "on format" in the page footer:

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![Department Name]
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

There is also a text box in the fotter with the following in it:

="Page" & [Page]& "of" &[Pages]

Do you see anything that looks wrong?

Thanks so much for your input with this. I really appreciate your time.

*** snipped ***

Did you remove the ' from that line and replace it with the !?

I can't 'see' your report.
The code and the method I suggested to you does work. You'll have to
double-check your control names, and code, etc.

I found 2 problems.

1) You did not add an unbound control to the Page Footer section
(ctlGrpPages), that is used to actually show the group page numbers in
the report.
So.. Add an unbound control to the report Footer.
I placed it (just for testing) directly below your control that shows
the page count for the entire report. Name this control "ctlGrpPages".
Leave it's Control Source blank.

2) Your code is now correct, except ...

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)

is the name of YOUR event code in the Page Footer.

However, in my Access 2002 database, it is called:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

I do not know which is correct for Access 2007.

Try this.
Copy your code current starting one line beneath the above procedure
name, i.e. at
Dim i As Integer
Then Highlight down to, but not including, the End Sub line and CUT
the entire procedure
Save the change.

Go back to Report Design view.
Right-Click on the Page Footer section.
Properties + Event tab.
Enter
[Event Procedure]
on the Page Footer Format line.
Click on the button with the 3 dots on that line.
When the code window opens, Paste the code between the 2 already
written lines.
Make sure you have only one line that says
Private Sub PageFooter..... etc...
Make sure you have only one
End Sub
line.
Save the changes.
The report should now indicate the various page numbers by group, as
well as for the entire report.

I would be interested in knowing which of the 2 above Sub PageFooter
lines is correct for Access 2007.

Have fun.

 

[fredg]

This is the code in "on format" in the page footer:

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![Department Name]
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

There is also a text box in the fotter with the following in it:

="Page" & [Page]& "of" &[Pages]

Do you see anything that looks wrong?

Thanks so much for your input with this. I really appreciate your time.

On Tue, 23 Sep 2008 07:50:02 -0700, Pookey wrote:

I should have know about the the field being in brackets since you have to do
that in queries. It just didn't occur to me doing it in the code. I changed
"Department Name" to have brackets around it and it still says page 1 of 17
instead of 1 of 1. I'm missing something somewhere.

Help!


*** snipped ***

Did you remove the ' from that line and replace it with the !?

I can't 'see' your report.
The code and the method I suggested to you does work. You'll have to
double-check your control names, and code, etc.

If you are using Access 2003 or older, if you wish, you can send me a
sample copy of your database; just whatever is needed to run the
report. If you strip out un-needed stuff, make sure you try the report
before sending it.
Please do not send any data that might be construed as confidential.
I'll look at it and get back to you.

Send it to

jandf
at
roadrunner
dot
com

Make sure you write "Report Database" in the email subject line.
I'll be gone for several hours, so it won't be until later that I get
back to you.

If you are using Access 2007, you will have to convert the sample
database to an earlier version. I cannot open a .accdb database.

I don't know if my just sent reply went.
Sorry if I've duplicated it.

I found 2 problems.

1) You did not add an unbound control to the Page Footer section
(ctlGrpPages), that is used to actually show the group page numbers in
the report.
So.. Add an unbound control to the report Footer.
I placed it (just for testing) directly below your control that shows
the page count for the entire report. Name this control "ctlGrpPages".
Leave it's Control Source blank.

2) Your code is now correct, except ...

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)

is the name of YOUR event code in the Page Footer.

However, in my Access 2002 database, it is called:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

I do not know which is correct for Access 2007.

Try this.
Copy your code current starting one line beneath the above procedure
name, i.e. at
Dim i As Integer
Then Highlight down to, but not including, the End Sub line and CUT
the entire procedure
Save the change.

Go back to Report Design view.
Right-Click on the Page Footer section.
Properties + Event tab.
Enter
[Event Procedure]
on the Page Footer Format line.
Click on the button with the 3 dots on that line.
When the code window opens, Paste the code between the 2 already
written lines.
Make sure you have only one line that says
Private Sub PageFooter..... etc...
Make sure you have only one
End Sub
line.
Save the changes.
The report should now indicate the various page numbers by group, as
well as for the entire report.

I would be interested in knowing which of the 2 above Sub PageFooter
lines is correct for Access 2007.

Have fun.

 

[Pookey]

It finally started working for about 1 minute. Then I moved the text box to
a different place in the footer and now is doesn't show the page numbers in
more. Do you know what happened? I didn't change anything, just moved the
box.

Thanks so much for all of your help on this.

This is the code in "on format" in the page footer:

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![Department Name]
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

There is also a text box in the fotter with the following in it:

="Page" & [Page]& "of" &[Pages]

Do you see anything that looks wrong?

Thanks so much for your input with this. I really appreciate your time.

On Tue, 23 Sep 2008 07:50:02 -0700, Pookey wrote:

I should have know about the the field being in brackets since you have to do
that in queries. It just didn't occur to me doing it in the code. I changed
"Department Name" to have brackets around it and it still says page 1 of 17
instead of 1 of 1. I'm missing something somewhere.

Help!


*** snipped ***

Did you remove the ' from that line and replace it with the !?

I can't 'see' your report.
The code and the method I suggested to you does work. You'll have to
double-check your control names, and code, etc.

I found 2 problems.

1) You did not add an unbound control to the Page Footer section
(ctlGrpPages), that is used to actually show the group page numbers in
the report.
So.. Add an unbound control to the report Footer.
I placed it (just for testing) directly below your control that shows
the page count for the entire report. Name this control "ctlGrpPages".
Leave it's Control Source blank.

2) Your code is now correct, except ...

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As
Integer)

is the name of YOUR event code in the Page Footer.

However, in my Access 2002 database, it is called:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)

I do not know which is correct for Access 2007.

Try this.
Copy your code current starting one line beneath the above procedure
name, i.e. at
Dim i As Integer
Then Highlight down to, but not including, the End Sub line and CUT
the entire procedure
Save the change.

Go back to Report Design view.
Right-Click on the Page Footer section.
Properties + Event tab.
Enter
[Event Procedure]
on the Page Footer Format line.
Click on the button with the 3 dots on that line.
When the code window opens, Paste the code between the 2 already
written lines.
Make sure you have only one line that says
Private Sub PageFooter..... etc...
Make sure you have only one
End Sub
line.
Save the changes.
The report should now indicate the various page numbers by group, as
well as for the entire report.

I would be interested in knowing which of the 2 above Sub PageFooter
lines is correct for Access 2007.

Have fun.

 

[fredg]

It finally started working for about 1 minute. Then I moved the text box to
a different place in the footer and now is doesn't show the page numbers in
more. Do you know what happened? I didn't change anything, just moved the
box.

Thanks so much for all of your help on this.

"fredg" wrote:

*** snipped ***

So it did work after you made the changes.

It's (ctlGrpPages) still in the Report's Page Footer section isn't it?
By any chance is there another control or image placed over it?
Or did it's Visible property inadvertently get changed to No?
Or did it's forecolor get changed to the same color as it's backcolor
(i.e. white on white)?
Or .... ?
If you move it back to it's pevious position does it now work again?

I still have your Db on my desktop.
Let me try and duplicate it.
Exactly where did you move the ctlGrpPages control to?
Give me the control's Left and Top property values.

 

[Pookey]

I just found out why it disappeared! I deleted the other text box that had
the "Page # of #". I didn't know that I still needed it if the other was
working. Am I right to assume that the "Page #" text box and the
"ctlGrpPages" both have to be in the footer and you just have to make the
"Page #" box invisible?

 

[fredg]

I just found out why it disappeared! I deleted the other text box that had
the "Page # of #". I didn't know that I still needed it if the other was
working. Am I right to assume that the "Page #" text box and the
"ctlGrpPages" both have to be in the footer and you just have to make the
"Page #" box invisible?

Yes!

 

[Pookey]

Halelujah! Thanks so much for your time and patience with me on this!

 

[Bruce Kovacs]

I have used this code in the past and it works very well. However, is there
a way to get the "Group Page x of y" to print in the page header instead of
the page footer?

--
Bruce Kovacs

I'm doing a report and grouping on a field called "Department Name." Please
tell or show me line by line what code I can write in the "On Format" line in
the footer to make the pages numbers restart for each group.

Thanks in advance for any help.

See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

2) Paste the code into the Page Footer Format event.

3) In the code, change Me!Salesman to
Me![Name of the control used to group by]

 

[fredg]

I have used this code in the past and it works very well. However, is there
a way to get the "Group Page x of y" to print in the page header instead of
the page footer?

Sure.
In the Page Footer, set the [ctlGrpPages] control's Visible property
to No.
Leave the Page Footer Format event as is.

Add a new unbound control to the Page Header.
Name this control "txtShowPages"
Leave it's control source blank.


Code the Page Header PRINT event:

Me.[txtShowPages] = "Group Page " & GrpArrayPage(Me.Page) & " of " &
GrpArrayPages(Me.Page)

The above should be all on one line.

 

[Bruce Kovacs]

Thanks. That was just too simple!

--
Bruce Kovacs

I have used this code in the past and it works very well. However, is there
a way to get the "Group Page x of y" to print in the page header instead of
the page footer?

Sure.
In the Page Footer, set the [ctlGrpPages] control's Visible property
to No.
Leave the Page Footer Format event as is.

Add a new unbound control to the Page Header.
Name this control "txtShowPages"
Leave it's control source blank.


Code the Page Header PRINT event:

Me.[txtShowPages] = "Group Page " & GrpArrayPage(Me.Page) & " of " &
GrpArrayPages(Me.Page)

The above should be all on one line.