Page x of y on 2-page report

[JWCrosby]

I've designed a 2-page report and I'd like to have "Page x of y" printed at
the bottom of each page. so, if I was running the report on 2 records it
would print 4 pages, with the first one printing "page 1 of 2" at the bottom,
the second page printing "page 2 of 2" and the third page printing "page 1 of
2", etc.

How do I do that?

Thanks in advance!

jerry

 

[Evi]

http://www.mvps.org/access/reports/rpt0013.htm
has instructions how to do this. Note that it doesn't work if , in
Properties, you have set the group header or footer to Repeat Section.
Evi

 

[Evi]

One important ammendment. To make this work in Acc2000 (and possibly other
versions of Access) you need to replace the line saying

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

with:

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

Note the addition of the word Section

Evi

 

[JWCrosby]

Thanks for the link, Evi. I'm trying to get it to work, but to no avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing "Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I get the prompt
for "Me".

Any idea what I missed?

Jerry

 

[Evi]

It's a fairly complex bit of coding. If you are new to this then you may need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and giving it a Group Header? Put the control/field by which you want to group your report into the group header (so if you are grouping by Salesman, put the Salesman control there).

The Properties button is on your toolbar when you are in Report Design View. If you aren't sure where it is, then you almost certainly haven't set the Repeat Section . You can also get at Properties by clicking on any part of your report, right clicking, then choosing Properties from the list.
Click on that grey bar just above the Header section of your group with the Properties box open, (the text in the colored area at the top of the Properties box will say something like GroupHeader) click on the Format tab and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text box, click on Properties, click on the Other tab and next to where it says Name, type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's not the name of the control)

Paste all this code over everything on your report's code page. I've repeated it below (sorry about the HTML everyone) in the hope that it won't break up the lines. Just check on the web page. Note that I've changed the line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see your new text box at all, it doesn't have the right numbers in it, it doesn't have anything in it) If a grey box appears warning of an error, tell us what the error is. If it offers you the chance to Debug, press Debug and let us know which line is highlighted.

(to get the report working again after pressing Debug, click Reset (the small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi

 

[JWCrosby]

I appreciate your patience, Evi. I'm pretty familiar with Access and coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group begins, that
first page only has the group field at the top, with no detail. The detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y" in the
footer, plus that one other oddity.

Any ideas for me?

Jerry

It's a fairly complex bit of coding. If you are new to this then you may need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and giving it a Group Header? Put the control/field by which you want to group your report into the group header (so if you are grouping by Salesman, put the Salesman control there).

The Properties button is on your toolbar when you are in Report Design View. If you aren't sure where it is, then you almost certainly haven't set the Repeat Section . You can also get at Properties by clicking on any part of your report, right clicking, then choosing Properties from the list.
Click on that grey bar just above the Header section of your group with the Properties box open, (the text in the colored area at the top of the Properties box will say something like GroupHeader) click on the Format tab and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text box, click on Properties, click on the Other tab and next to where it says Name, type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's not the name of the control)

Paste all this code over everything on your report's code page. I've repeated it below (sorry about the HTML everyone) in the hope that it won't break up the lines. Just check on the web page. Note that I've changed the line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see your new text box at all, it doesn't have the right numbers in it, it doesn't have anything in it) If a grey box appears warning of an error, tell us what the error is. If it offers you the chance to Debug, press Debug and let us know which line is highlighted.

(to get the report working again after pressing Debug, click Reset (the small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi

Thanks for the link, Evi. I'm trying to get it to work, but to no avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing "Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I get the prompt
for "Me".

Any idea what I missed?

Jerry

 

[Evi]

Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page - After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click just
right of that and look at the first line of code which you see. If we have
it right, it will be the code which you pasted in. Otherwise, replace the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got this
code working. I had Repeat Section set to Yes in my report (it should be No)
which mucked it up and then I didn't spot that the first line of code was in
a different format than in my version of Access.
Evi

I appreciate your patience, Evi. I'm pretty familiar with Access and coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group begins, that
first page only has the group field at the top, with no detail. The detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y" in the
footer, plus that one other oddity.

Any ideas for me?

Jerry

it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put the
Salesman control there).View. If you aren't sure where it is, then you almost certainly haven't set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the list.the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format tab
and check that next to Repeat Section, it says No.click on Properties, click on the Other tab and next to where it says Name,
type:repeated it below (sorry about the HTML everyone) in the hope that it won't
break up the lines. Just check on the web page. Note that I've changed the
line that starts

Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see

your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an error, tell
us what the error is. If it offers you the chance to Debug, press Debug and
let us know which line is highlighted.

 

[JWCrosby]

I've taken all the steps you said. I did have some page breaks in the detail
section and I removed them. It is not a multi-column report. Everything else
checks out.

Still there is nothing in the CtlGrpPages control and the one blank page in
the middle has only the text in the group header showing; details don't show
up until the next page.

Jerry

Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page - After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click just
right of that and look at the first line of code which you see. If we have
it right, it will be the code which you pasted in. Otherwise, replace the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got this
code working. I had Repeat Section set to Yes in my report (it should be No)
which mucked it up and then I didn't spot that the first line of code was in
a different format than in my version of Access.
Evi

I appreciate your patience, Evi. I'm pretty familiar with Access and coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group begins, that
first page only has the group field at the top, with no detail. The detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y" in the
footer, plus that one other oddity.

Any ideas for me?

Jerry

it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put the
Salesman control there).View. If you aren't sure where it is, then you almost certainly haven't set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the list.the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format tab
and check that next to Repeat Section, it says No.click on Properties, click on the Other tab and next to where it says Name,
type:repeated it below (sorry about the HTML everyone) in the hope that it won't
break up the lines. Just check on the web page. Note that I've changed the
line that starts

Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see

your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an error, tell
us what the error is. If it offers you the chance to Debug, press Debug and
let us know which line is highlighted.

 

[Evi]

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and it does
work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any of your
groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as a multi
column report)

Have a fresh look yourself in the morning and if anyone else can spot what
we are missing, please chip in.
Evi

I've taken all the steps you said. I did have some page breaks in the detail
section and I removed them. It is not a multi-column report. Everything else
checks out.

Still there is nothing in the CtlGrpPages control and the one blank page in
the middle has only the text in the group header showing; details don't show
up until the next page.

Jerry

Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page - After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click just
right of that and look at the first line of code which you see. If we have
it right, it will be the code which you pasted in. Otherwise, replace the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got this
code working. I had Repeat Section set to Yes in my report (it should be No)
which mucked it up and then I didn't spot that the first line of code was in
a different format than in my version of Access.
Evi

I appreciate your patience, Evi. I'm pretty familiar with Access and coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group

begins,
that

first page only has the group field at the top, with no detail. The detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y"

in
the

footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this then you

may
need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and

giving
it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put the
Salesman control there).

The Properties button is on your toolbar when you are in Report

Design
View. If you aren't sure where it is, then you almost certainly haven't set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the list.

Click on that grey bar just above the Header section of your group

with
the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format tab
and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text

box,
click on Properties, click on the Other tab and next to where it says Name,
type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's

not
the name of the control)

Paste all this code over everything on your report's code page. I've

repeated it below (sorry about the HTML everyone) in the hope that it won't
break up the lines. Just check on the web page. Note that I've changed the
line that starts

Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000

until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber

then
change that to

Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces

or
symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't

see
your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an error, tell
us what the error is. If it offers you the chance to Debug, press Debug and
let us know which line is highlighted.

(to get the report working again after pressing Debug, click Reset

(the
small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi





Thanks for the link, Evi. I'm trying to get it to work, but to no avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing "Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I get

the
prompt

for "Me".

Any idea what I missed?

Jerry


:

http://www.mvps.org/access/reports/rpt0013.htm
has instructions how to do this. Note that it doesn't work if , in
Properties, you have set the group header or footer to Repeat Section.
Evi

I've designed a 2-page report and I'd like to have "Page x of

y"
printed

at
the bottom of each page. so, if I was running the report on 2 records it
would print 4 pages, with the first one printing "page 1 of 2"

at
the

bottom,
the second page printing "page 2 of 2" and the third page

printing
"page 1

of
2", etc.

How do I do that?

Thanks in advance!

jerry

 

[JWCrosby]

Well, I've had a good night's sleep and it's sunny and blue sky here in
Oregon today, but there's still a cloud of bewilderment hovering over my
database file!

I only have the one field set in grouping and sorting and the keep together
is set to "No."

I've verified the columns to be set to one (I had never gone to that panel
when creating this report).

I've verified that the page width (7") does not exceed the width of the
paper with margins, which are set to .5" on left and right (8.5" paper).

I'm still getting the orphan first page on the second group and the page
footer is blank. Would you prefer I send you a copy of my file? Go ahead
and send me a copy of what you have working. Do you need my email address?

Thanks again for all your help.

Jerry

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and it does
work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any of your
groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as a multi
column report)

Have a fresh look yourself in the morning and if anyone else can spot what
we are missing, please chip in.
Evi

I've taken all the steps you said. I did have some page breaks in the detail
section and I removed them. It is not a multi-column report. Everything else
checks out.

Still there is nothing in the CtlGrpPages control and the one blank page in
the middle has only the text in the group header showing; details don't show
up until the next page.

Jerry

Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page - After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click just
right of that and look at the first line of code which you see. If we have
it right, it will be the code which you pasted in. Otherwise, replace the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got this
code working. I had Repeat Section set to Yes in my report (it should be No)
which mucked it up and then I didn't spot that the first line of code was in
a different format than in my version of Access.
Evi



I appreciate your patience, Evi. I'm pretty familiar with Access and
coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had
there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group begins,
that
first page only has the group field at the top, with no detail. The
detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y" in
the
footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this then you may
need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and giving
it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put the
Salesman control there).

The Properties button is on your toolbar when you are in Report Design
View. If you aren't sure where it is, then you almost certainly haven't set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the list.
Click on that grey bar just above the Header section of your group with
the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format tab
and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text box,
click on Properties, click on the Other tab and next to where it says Name,
type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's not
the name of the control)

Paste all this code over everything on your report's code page. I've
repeated it below (sorry about the HTML everyone) in the hope that it won't
break up the lines. Just check on the web page. Note that I've changed the
line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then
change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or
symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see
your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an error, tell
us what the error is. If it offers you the chance to Debug, press Debug and
let us know which line is highlighted.

(to get the report working again after pressing Debug, click Reset (the
small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi





Thanks for the link, Evi. I'm trying to get it to work, but to no
avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat
Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing
"Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I get the
prompt
for "Me".

Any idea what I missed?

Jerry


:

http://www.mvps.org/access/reports/rpt0013.htm
has instructions how to do this. Note that it doesn't work if , in
Properties, you have set the group header or footer to Repeat
Section.
Evi

I've designed a 2-page report and I'd like to have "Page x of y"
printed
at
the bottom of each page. so, if I was running the report on 2
records it
would print 4 pages, with the first one printing "page 1 of 2" at
the
bottom,
the second page printing "page 2 of 2" and the third page printing
"page 1
of
2", etc.

How do I do that?

 

[JWCrosby]

It's another day and it's sunny w/blue sky here in Oregon, but there's still
a cloudiness of bewilderment over my Access report!

After further troubleshooting...

It appears that the orphan group header page is related to the height of the
detail section. Because the detail section has to contain a lot of data it
has to be larger than a normal page (thus forcing the second page). Right
now it's set to be 18" high, but if I change it to be 10 or so inches, that
orphan page goes away. However, I don't know of any other way to get all the
information into the detail section without making it large.

I have the "Keep Together" set to "No".

There are no columns formated; never have been on this report.

The page width is 7" with 0.5" margins on left and right; well within the
total of 8.5" width on the paper.

I also did some troubleshooting on the code. I rem'd out every line except
this one (which I shortened):
Me!CtlGrpPages = "Group Page "
In doing that, I now see "Group Page" in the control in the page footer. So
apparently, it's never dropping down to that line when the code is processed.

That help any?

Jerry

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and it does
work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any of your
groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as a multi
column report)

Have a fresh look yourself in the morning and if anyone else can spot what
we are missing, please chip in.
Evi

I've taken all the steps you said. I did have some page breaks in the detail
section and I removed them. It is not a multi-column report. Everything else
checks out.

Still there is nothing in the CtlGrpPages control and the one blank page in
the middle has only the text in the group header showing; details don't show
up until the next page.

Jerry

Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page - After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click just
right of that and look at the first line of code which you see. If we have
it right, it will be the code which you pasted in. Otherwise, replace the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got this
code working. I had Repeat Section set to Yes in my report (it should be No)
which mucked it up and then I didn't spot that the first line of code was in
a different format than in my version of Access.
Evi



I appreciate your patience, Evi. I'm pretty familiar with Access and
coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back in
from your reply (even though it was identical to what I already had
there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the first
page in the printout prints properly, but when the second group begins,
that
first page only has the group field at the top, with no detail. The
detail
part doesn't start until page 2. That's one "oddity" that's popped up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y" in
the
footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this then you may
need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and giving
it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put the
Salesman control there).

The Properties button is on your toolbar when you are in Report Design
View. If you aren't sure where it is, then you almost certainly haven't set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the list.
Click on that grey bar just above the Header section of your group with
the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format tab
and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text box,
click on Properties, click on the Other tab and next to where it says Name,
type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's not
the name of the control)

Paste all this code over everything on your report's code page. I've
repeated it below (sorry about the HTML everyone) in the hope that it won't
break up the lines. Just check on the web page. Note that I've changed the
line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber then
change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces or
symbols in your field name)

If it doesn't work, let us know what is happening ie you just can't see
your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an error, tell
us what the error is. If it offers you the chance to Debug, press Debug and
let us know which line is highlighted.

(to get the report working again after pressing Debug, click Reset (the
small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi





Thanks for the link, Evi. I'm trying to get it to work, but to no
avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat
Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing
"Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I get the
prompt
for "Me".

Any idea what I missed?

Jerry


:

http://www.mvps.org/access/reports/rpt0013.htm
has instructions how to do this. Note that it doesn't work if , in
Properties, you have set the group header or footer to Repeat
Section.
Evi

I've designed a 2-page report and I'd like to have "Page x of y"
printed
at
the bottom of each page. so, if I was running the report on 2
records it
would print 4 pages, with the first one printing "page 1 of 2" at
the
bottom,
the second page printing "page 2 of 2" and the third page printing
"page 1
of
2", etc.

How do I do that?

 

[Evi]

Another dull day here in Grimsby UK
The tall detail section shouldn't matter. I made my detail section extra
large on purpose and all it should do is to make more pages for our Group
Page Counter to count.
Make that report width just a tad narrower than it needs to be - see if that
surpresses your blank page. Otherwise, check that you don't have another
page break lying around.

Have you set the Group Footer's 'Force New Page' Property (on the Format Tab
of Properties) to 'After Section'? (It probably needs to be)

I'll happily send you my 'laboratory rat' db (I experiment on it) so you can
see the Group Footer Control working, I think you'll be able to figure out
my email addy so email me yours (don't post it to the newsgroup cos the
spambots will get you!). If you send me a copy of your db however, you'll
need to convert it to Access 2000 and the code doesn't always work then.
Make sure you only try to convert a COPY of your db - it sometimes snarfs it
up.

Evi

It's another day and it's sunny w/blue sky here in Oregon, but there's still
a cloudiness of bewilderment over my Access report!

After further troubleshooting...

It appears that the orphan group header page is related to the height of the
detail section. Because the detail section has to contain a lot of data it
has to be larger than a normal page (thus forcing the second page). Right
now it's set to be 18" high, but if I change it to be 10 or so inches, that
orphan page goes away. However, I don't know of any other way to get all the
information into the detail section without making it large.

I have the "Keep Together" set to "No".

There are no columns formated; never have been on this report.

The page width is 7" with 0.5" margins on left and right; well within the
total of 8.5" width on the paper.

I also did some troubleshooting on the code. I rem'd out every line except
this one (which I shortened):
Me!CtlGrpPages = "Group Page "
In doing that, I now see "Group Page" in the control in the page footer. So
apparently, it's never dropping down to that line when the code is processed.

That help any?

Jerry

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and it does
work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any of your
groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as a multi
column report)

Have a fresh look yourself in the morning and if anyone else can spot what
we are missing, please chip in.
Evi

I've taken all the steps you said. I did have some page breaks in the detail
section and I removed them. It is not a multi-column report.

Everything
else

checks out.

Still there is nothing in the CtlGrpPages control and the one blank

page
in

the middle has only the text in the group header showing; details

don't
show

up until the next page.

Jerry

:


Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page.

The
grid

needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New

Page -
After

Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out

for
now.

I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure,

click
just

right of that and look at the first line of code which you see. If

we
have

it right, it will be the code which you pasted in. Otherwise,

replace
the

first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by

the
code

(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I

got
this

code working. I had Repeat Section set to Yes in my report (it

should be
No)

which mucked it up and then I didn't spot that the first line of

code
was in

a different format than in my version of Access.
Evi



I appreciate your patience, Evi. I'm pretty familiar with Access and
coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code

back
in

from your reply (even though it was identical to what I already had
there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it

the
first

page in the printout prints properly, but when the second group begins,
that
first page only has the group field at the top, with no detail. The
detail
part doesn't start until page 2. That's one "oddity" that's

popped
up.

Regarding the CtlGrpPages control in the page footer, it is blank.

(I
set

the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of

y"
in

the
footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this then

you
may

need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and giving
it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman,

put
the

Salesman control there).

The Properties button is on your toolbar when you are in Report Design
View. If you aren't sure where it is, then you almost certainly

haven't
set

the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from

the
list.

Click on that grey bar just above the Header section of your

group
with

the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the

Format
tab

and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text box,
click on Properties, click on the Other tab and next to where it

says
Name,

type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page -

it's
not

the name of the control)

Paste all this code over everything on your report's code page. I've
repeated it below (sorry about the HTML everyone) in the hope that

it
won't

break up the lines. Just check on the web page. Note that I've

changed
the

line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000 until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say

InvoiceNumber
then

change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have

spaces
or

symbols in your field name)

If it doesn't work, let us know what is happening ie you just

can't
see

your new text box at all, it doesn't have the right numbers in it, it
doesn't have anything in it) If a grey box appears warning of an

error,
tell

us what the error is. If it offers you the chance to Debug, press

Debug
and

let us know which line is highlighted.

(to get the report working again after pressing Debug, click

Reset
(the

small blue square) on the toolbar)

'************ 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 PageFooterSection_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!Salesperson
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
'************ Code End *************


Evi





Thanks for the link, Evi. I'm trying to get it to work, but to no
avail at
the moment. Where do I find the properties setting you referenced
("Properties, you have set the group header or footer to Repeat
Section.")?

I thought I did as I should by placing the code under "View-->Code",
changing the reference to my group field name and placing
"Me!ctrGrpPages" in
a control in the page footer. However, when I go to print I

get
the

prompt
for "Me".

Any idea what I missed?

Jerry


:

http://www.mvps.org/access/reports/rpt0013.htm
has instructions how to do this. Note that it doesn't work

if ,
in

Properties, you have set the group header or footer to Repeat
Section.
Evi

I've designed a 2-page report and I'd like to have "Page x

of
y"

printed
at
the bottom of each page. so, if I was running the report on 2
records it
would print 4 pages, with the first one printing "page 1

of 2"
at

the
bottom,
the second page printing "page 2 of 2" and the third page printing
"page 1
of
2", etc.

How do I do that?

 

[JWCrosby]

Well, I fixed part of it tonight!! I got rid of the orphan group header
page. In the Detail section properties, I set the "Keep Together" to No.

Now to work on that page footer!

Jerry

Another dull day here in Grimsby UK
The tall detail section shouldn't matter. I made my detail section extra
large on purpose and all it should do is to make more pages for our Group
Page Counter to count.
Make that report width just a tad narrower than it needs to be - see if that
surpresses your blank page. Otherwise, check that you don't have another
page break lying around.

Have you set the Group Footer's 'Force New Page' Property (on the Format Tab
of Properties) to 'After Section'? (It probably needs to be)

I'll happily send you my 'laboratory rat' db (I experiment on it) so you can
see the Group Footer Control working, I think you'll be able to figure out
my email addy so email me yours (don't post it to the newsgroup cos the
spambots will get you!). If you send me a copy of your db however, you'll
need to convert it to Access 2000 and the code doesn't always work then.
Make sure you only try to convert a COPY of your db - it sometimes snarfs it
up.

Evi

It's another day and it's sunny w/blue sky here in Oregon, but there's still
a cloudiness of bewilderment over my Access report!

After further troubleshooting...

It appears that the orphan group header page is related to the height of the
detail section. Because the detail section has to contain a lot of data it
has to be larger than a normal page (thus forcing the second page). Right
now it's set to be 18" high, but if I change it to be 10 or so inches, that
orphan page goes away. However, I don't know of any other way to get all the
information into the detail section without making it large.

I have the "Keep Together" set to "No".

There are no columns formated; never have been on this report.

The page width is 7" with 0.5" margins on left and right; well within the
total of 8.5" width on the paper.

I also did some troubleshooting on the code. I rem'd out every line except
this one (which I shortened):
Me!CtlGrpPages = "Group Page "
In doing that, I now see "Group Page" in the control in the page footer. So
apparently, it's never dropping down to that line when the code is processed.

That help any?

Jerry

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and it does
work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any of your
groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as a multi
column report)

Have a fresh look yourself in the morning and if anyone else can spot what
we are missing, please chip in.
Evi


I've taken all the steps you said. I did have some page breaks in the
detail
section and I removed them. It is not a multi-column report. Everything
else
checks out.

Still there is nothing in the CtlGrpPages control and the one blank page
in
the middle has only the text in the group header showing; details don't
show
up until the next page.

Jerry

:


Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your page. The
grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page -
After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them out for
now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in
Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click
just
right of that and look at the first line of code which you see. If we
have
it right, it will be the code which you pasted in. Otherwise, replace
the
first line of our code (Private Sub PageFooterSection_Format(Cancel As
Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except by the
code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not
experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling before I got
this
code working. I had Repeat Section set to Yes in my report (it should be
No)
which mucked it up and then I didn't spot that the first line of code
was in
a different format than in my version of Access.
Evi



I appreciate your patience, Evi. I'm pretty familiar with Access and
coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the code back
in
from your reply (even though it was identical to what I already had
there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added the group
header, and when I did, and put the reference_last control in it the
first
page in the printout prints properly, but when the second group
begins,
that
first page only has the group field at the top, with no detail. The
detail
part doesn't start until page 2. That's one "oddity" that's popped
up.

Regarding the CtlGrpPages control in the page footer, it is blank. (I
set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page x of y"
in
the
footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this then you
may
need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and
giving
it a Group Header? Put the control/field by which you want to group your
report into the group header (so if you are grouping by Salesman, put
the
Salesman control there).

The Properties button is on your toolbar when you are in Report
Design
View. If you aren't sure where it is, then you almost certainly haven't
set
the Repeat Section . You can also get at Properties by clicking on any
part of your report, right clicking, then choosing Properties from the
list.
Click on that grey bar just above the Header section of your group
with
the Properties box open, (the text in the colored area at the top of the
Properties box will say something like GroupHeader) click on the Format
tab
and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text
box,
click on Properties, click on the Other tab and next to where it says
Name,
type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code page - it's
not
the name of the control)

Paste all this code over everything on your report's code page. I've
repeated it below (sorry about the HTML everyone) in the hope that it
won't
break up the lines. Just check on the web page. Note that I've changed
the
line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000
until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber
then
change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces
or
symbols in your field name)

If it doesn't work, let us know what is happening ie you just

can't

 

[Evi]

Keep Together in the detail section! - you rogue you!
Thanks for letting me know.

Send me an email with an informative subject line so that I won't MailWash
it away (I think you will be able to edit my email addy so that it works) if
you want to see this Group Page working. You may be able to compare Code
pages in case there is something different.

BTW, do you by any chance have the dreaded Access 2007? If yes, then it
could be back to the drawing board because it has changed all sorts of code
stuff.

If you don't have Access 2007, I wonder if you have any missing References
that are needed to make the code work. I didn't really think of that because
normally Access objects loudly if something is missing rather than just
sullenly not working at all.

Evi

Well, I fixed part of it tonight!! I got rid of the orphan group header
page. In the Detail section properties, I set the "Keep Together" to No.

Now to work on that page footer!

Jerry

Another dull day here in Grimsby UK
The tall detail section shouldn't matter. I made my detail section extra
large on purpose and all it should do is to make more pages for our Group
Page Counter to count.
Make that report width just a tad narrower than it needs to be - see if that
surpresses your blank page. Otherwise, check that you don't have another
page break lying around.

Have you set the Group Footer's 'Force New Page' Property (on the Format Tab
of Properties) to 'After Section'? (It probably needs to be)

I'll happily send you my 'laboratory rat' db (I experiment on it) so you can
see the Group Footer Control working, I think you'll be able to figure out
my email addy so email me yours (don't post it to the newsgroup cos the
spambots will get you!). If you send me a copy of your db however, you'll
need to convert it to Access 2000 and the code doesn't always work then.
Make sure you only try to convert a COPY of your db - it sometimes snarfs it
up.

Evi

It's another day and it's sunny w/blue sky here in Oregon, but there's still
a cloudiness of bewilderment over my Access report!

After further troubleshooting...

It appears that the orphan group header page is related to the height

of
the

detail section. Because the detail section has to contain a lot of

data
it

has to be larger than a normal page (thus forcing the second page). Right
now it's set to be 18" high, but if I change it to be 10 or so inches, that
orphan page goes away. However, I don't know of any other way to get

all
the

information into the detail section without making it large.

I have the "Keep Together" set to "No".

There are no columns formated; never have been on this report.

The page width is 7" with 0.5" margins on left and right; well within the
total of 8.5" width on the paper.

I also did some troubleshooting on the code. I rem'd out every line except
this one (which I shortened):
Me!CtlGrpPages = "Group Page "
In doing that, I now see "Group Page" in the control in the page

footer.
So

apparently, it's never dropping down to that line when the code is processed.

That help any?

Jerry

:

Groan! I'll get to bed now and have another look in the morning Its 00:51
here and boy do I need my beauty sleep! I've tested the code out and

it
does

work. If it helps, I can send you a simple database so you can see it
working.

Do you have Keep Together set in the Sorting/Grouping box for any

of
your

groups? Check the width of your report in Properties.
Check the Columns tab in Page Set Up (if you've ever set this up as

a
multi

column report)

Have a fresh look yourself in the morning and if anyone else can

spot
what

we are missing, please chip in.
Evi


I've taken all the steps you said. I did have some page breaks in the
detail
section and I removed them. It is not a multi-column report. Everything
else
checks out.

Still there is nothing in the CtlGrpPages control and the one

blank
page

in
the middle has only the text in the group header showing; details don't
show
up until the next page.

Jerry

:


Lets start with the orphaned group header.

1. Make sure that your report is narrow enough to fit on your

page.
The

grid
needs to be the width of paper minus the width of both margins.

2. Add a Group Footer to reference_last.
Click on the footer and in Properties, Format, choose Force New Page -
After
Section (so that each group starts on a new page.

3.If you have any Page Break controls in your report, take them

out
for

now.
I haven't experimented with them yet so I don't know if they make a
difference.

4. Click on the Page Footer bar (not the Group Footer) and in
Properties,
Events, check if it says
Event Procedure next to On Format
If it doesn't, click next to On Format, Choose Event Procedure, click
just
right of that and look at the first line of code which you see.

If
we

have
it right, it will be the code which you pasted in. Otherwise, replace
the
first line of our code (Private Sub

PageFooterSection_Format(Cancel
As

Integer, FormatCount As Integer)
with whatever you see there.
It could be different in different versions of Access.
Remember, CtrlGrpPages should have nothing written in it except

by
the

code
(when we get it working!)
It should say Unbound when you are in Design View.

5. Is you report by any chance a Multi-column report? I've not
experimented
with that so I don't know if it makes a difference.

I have to tell you that it took me quite a bit of fiddling

before I
got

this
code working. I had Repeat Section set to Yes in my report (it should be
No)
which mucked it up and then I didn't spot that the first line of code
was in
a different format than in my version of Access.
Evi



I appreciate your patience, Evi. I'm pretty familiar with

Access
and

coding,
but this is taking me into new territory! fun!

I did everything you said to the "T", including pasting the

code
back

in
from your reply (even though it was identical to what I

already
had

there).
I made the changes as needed.

My sorting group field is "reference_last". I hadn't added

the
group

header, and when I did, and put the reference_last control in

it
the

first
page in the printout prints properly, but when the second group
begins,
that
first page only has the group field at the top, with no

detail.
The

detail
part doesn't start until page 2. That's one "oddity" that's popped
up.

Regarding the CtlGrpPages control in the page footer, it is

blank.
(I

set
the control border to solid so I can see it, but it's empty.)

I get no errors when I shift to print preview. Just no "page

x of
y"

in
the
footer, plus that one other oddity.

Any ideas for me?

Jerry

:

It's a fairly complex bit of coding. If you are new to this

then
you

may
need to ask quite a few questions to get it right but don't give up.

Have you Grouped your report using the Sorting Grouping box and
giving
it a Group Header? Put the control/field by which you want to

group
your

report into the group header (so if you are grouping by

Salesman,
put

the
Salesman control there).

The Properties button is on your toolbar when you are in Report
Design
View. If you aren't sure where it is, then you almost certainly haven't
set
the Repeat Section . You can also get at Properties by

clicking on
any

part of your report, right clicking, then choosing Properties

from
the

list.
Click on that grey bar just above the Header section of your group
with
the Properties box open, (the text in the colored area at the

top of
the

Properties box will say something like GroupHeader) click on the Format
tab
and check that next to Repeat Section, it says No.

Going to View Code is the correct place to put your code.

Put a new text box in your report page footer. Click on the text
box,
click on Properties, click on the Other tab and next to where it says
Name,
type:

CtlGrpPages

(note the spelling is not ctrGrpPages)

(the Me bit is how a report refers to itself in the Code

page -
it's

not
the name of the control)

Paste all this code over everything on your report's code

page.
I've

repeated it below (sorry about the HTML everyone) in the hope

that
it

won't
break up the lines. Just check on the web page. Note that I've changed
the
line that starts
Private Sub PageFooter
by adding the word Section. (the code didn't work in Access 2000
until I
did )

Where it says Me!Salesman you will need to edit the code.

If the field by which you have grouped is called say InvoiceNumber
then
change that to
Me![InvoiceNumber]

(I've put square brackets around the field in case you have spaces
or
symbols in your field name)

If it doesn't work, let us know what is happening ie you

just
can't

 

[JWCrosby]

I just sent the email and no, I'm not using Access 2007. I have thinking
about upgrading, thinking I need to keep on the cutting edge, but when I saw
you used the adjective "dreaded" I'm beginning to rethink it! My wife is
going to be getting a new laptop soon and we'll be jumping into the world of
Vista then.

Jerry