Restart Page Numbers on a Report

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

Hi-
I'm on Access 2000. I have a report that breaks by
location, and I want the page numbers to reset to "Page 1
of x" at each change in location. Is there a way to
accomplish this?
Thanks
 
Lynn,
Me is a VBA word, not an Access word.
It refers to the active object -- the form or report.

So it works in the VBA code window, but not in an Access control source.

If you read the instructions accurately, you would set the control source
to:
= [ctlGrpPages]
No Me!

HOWEVER, you miss-read them.
From the instructions:
"Me!ctlGrpPages is the name of a control you should place in the page
footer."

Change the unbound Control's NAME to ctlGroupPages.
Leave the control source blank.
You don't need the Me!

Keep going. The finish line is in sight.
--
Fred

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


Lynn said:
I've never worked with modules before, and I must be doing
something wrong.
I opened the report in design view, then View - Code. I
copied the code, changed the Me!Salesperson to Me!Loc.
Loc is the name of my control - I assume I'm supposed to
keep the "Me!" I saved the code and went back to the form.

I added a text box to the page footer, and entered =Me!
ctlGrpPages. When I run the report, it comes up with
#Name? in the text box, where the pages should be. When I
go back to design mode, the field now says =[Me]!
[ctlGrpPages]

What am I doing wrong?
-----Original Message-----
see the reports section at:
http://www.mvps.org/access

--
Fred

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





.
Click to expand...
Click to expand...
 
Hi, Fred,

It's still not working!

I changed the unbound controls name to ctlGroupPages.
Now, I don't get any error messages - I get nothing at
all. Just an emplty space where the page number should be!

I notice that on the top of that Visual Basic screen
(which I had never seen before Friday), there are 2 drop
downs at the top. One has "General", "Detail", "Page
Footer", etc. When I open the screen, it seems that I
copied the code into the "General" section. Is that where
it should be?

Thanks so much for your help!
Lynn
-----Original Message-----
Lynn,
Me is a VBA word, not an Access word.
It refers to the active object -- the form or report.

So it works in the VBA code window, but not in an Access control source.

If you read the instructions accurately, you would set the control source
to:
= [ctlGrpPages]
No Me!

HOWEVER, you miss-read them.
From the instructions:
"Me!ctlGrpPages is the name of a control you should place in the page
footer."

Change the unbound Control's NAME to ctlGroupPages.
Leave the control source blank.
You don't need the Me!

Keep going. The finish line is in sight.
--
Fred

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


Lynn said:
I've never worked with modules before, and I must be doing
something wrong.
I opened the report in design view, then View - Code. I
copied the code, changed the Me!Salesperson to Me!Loc.
Loc is the name of my control - I assume I'm supposed to
keep the "Me!" I saved the code and went back to the form.

I added a text box to the page footer, and entered =Me!
ctlGrpPages. When I run the report, it comes up with
#Name? in the text box, where the pages should be. When I
go back to design mode, the field now says =[Me]!
[ctlGrpPages]

What am I doing wrong?
-----Original Message-----
see the reports section at:
http://www.mvps.org/access

--
Fred

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


Hi-
I'm on Access 2000. I have a report that breaks by
location, and I want the page numbers to reset
Click to expand...
to "Page
1
of x" at each change in location. Is there a way to
accomplish this?
Thanks


.
Click to expand...
Click to expand...


.
Click to expand...
 
Lynn,
Regarding:
When I open the screen, it seems that I
copied the code into the "General" section. Is that where
it should be?
Click to expand...

This is in the Report's code window, correct?
Not in one of the database Modules.

The answer is Yes ... and No.

The first 3 Dim statement lines go up in the General section.
The top of the code window (General Section) should look
something like this:

Option Compare Database
Option Explicit

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

======== Line here ======
Then you may (or may not) have some additional event or sub procedures under
the above line.

========== Line here =============
Then, in report design view, right-click on the page footer bar
and select Properties.
Click on the Page Footer Format event line.
Write
[Event Procedure]
Click on the button with 3 dots appears on that line.
The code window will appear with the cursor flashing
in the Page Footer Format event line.

Paste the code (without those top 3 lines that you have already
written at the top of the window).

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
** Paste the code here
** Do NOT paste the Private Sub PageFooter etc. line,
** nor the End Sub line as that has already been filled in by Access.
End Sub

======== Line here ============

When done, if you place your cursor on one of the
Dim statements at the top of the sheet, the drop-down
shold indicate 'Declarations'.
If you place your cursor within the Sub PageFooter, the
drop-down will show PageFooter.

You placed an unbound control named ctlGrpPages in the Page Footer.
And you indicated you already have changed the
Me!Salesperson to Me!Loc.

I just did all of this and it does work.

A thought has just occurred to me.
You must also have a control in the report that calculates [Pages].
Anything like =[Page] & " of " & [Pages]
or just =[Pages].
If you don't want to show it, just make it not visible.
This forces Access to Format the report twice.
Once to find the total page count (which also fills the above group arrays),
then again to actually preview or print the report.
I just notice the web site does not explicitly tell you to add the control,
though it does mention the use of [Pages] in passing.

Hope this takes care of it for you. Let me know.
--
Fred

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


Lynn said:
Hi, Fred,

It's still not working!

I changed the unbound controls name to ctlGroupPages.
Now, I don't get any error messages - I get nothing at
all. Just an emplty space where the page number should be!

I notice that on the top of that Visual Basic screen
(which I had never seen before Friday), there are 2 drop
downs at the top. One has "General", "Detail", "Page
Footer", etc. When I open the screen, it seems that I
copied the code into the "General" section. Is that where
it should be?

Thanks so much for your help!
Lynn
Click to expand...
** snipped **
 
Hi, Fred,

I just went through your instructions 3 times. I made
sure I'm in the report's code window.

I went to the Page Footer prperties, and clicked on Event
Procedure and the ..., to get to the code.

The appropriate lines were already in the General
section. So was everything else!


I have no other events or sub procedures, so immediately
after the line, I copied the rest of the code. I made
sure that I didn't copy the Private Sub Page Footer, etc..
line, or the End Sub line.

I did just what you said - when I put my cursor on the Dim
lins at the top, it showed "Declarations", and when I put
my cursor in the sub page footer section, the drop down
showed that I was in the Page Footer.

And I double checked that Me!Salesperson was changed to Me!
Loc. And that Loc is the name of the field that I am
grouping on.

I went back into design view, and added a test box for
="Page " & [Page] & " of " & [Pages].

Now when I try to run the report, I get an error:
"The expression On Format you entered as the event
property setting produced the following error: Expected"
line number or label or statement or end of statement.

The expression may not result in the name of a macro, the
name of a user-defined funtion, or [Event Procedure}.
There may have been an error evaluation the function,
event, or macro."

Huh???? If you can figure this out, I'll be forever in
your debt!!! I'm so confused!

Thanks again,
Lynn
-----Original Message-----
Lynn,
Regarding:
When I open the screen, it seems that I
copied the code into the "General" section. Is that where
it should be?
Click to expand...

This is in the Report's code window, correct?
Not in one of the database Modules.

The answer is Yes ... and No.

The first 3 Dim statement lines go up in the General section.
The top of the code window (General Section) should look
something like this:

Option Compare Database
Option Explicit

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

======== Line here ======
Then you may (or may not) have some additional event or sub procedures under
the above line.

========== Line here =============
Then, in report design view, right-click on the page footer bar
and select Properties.
Click on the Page Footer Format event line.
Write
[Event Procedure]
Click on the button with 3 dots appears on that line.
The code window will appear with the cursor flashing
in the Page Footer Format event line.

Paste the code (without those top 3 lines that you have already
written at the top of the window).

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
** Paste the code here
** Do NOT paste the Private Sub PageFooter etc. line,
** nor the End Sub line as that has already been filled in by Access.
End Sub

======== Line here ============

When done, if you place your cursor on one of the
Dim statements at the top of the sheet, the drop-down
shold indicate 'Declarations'.
If you place your cursor within the Sub PageFooter, the
drop-down will show PageFooter.

You placed an unbound control named ctlGrpPages in the Page Footer.
And you indicated you already have changed the
Me!Salesperson to Me!Loc.

I just did all of this and it does work.

A thought has just occurred to me.
You must also have a control in the report that calculates [Pages].
Anything like =[Page] & " of " & [Pages]
or just =[Pages].
If you don't want to show it, just make it not visible.
This forces Access to Format the report twice.
Once to find the total page count (which also fills the above group arrays),
then again to actually preview or print the report.
I just notice the web site does not explicitly tell you to add the control,
though it does mention the use of [Pages] in passing.

Hope this takes care of it for you. Let me know.
--
Fred

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


Lynn said:
Hi, Fred,

It's still not working!

I changed the unbound controls name to ctlGroupPages.
Now, I don't get any error messages - I get nothing at
all. Just an emplty space where the page number should be!

I notice that on the top of that Visual Basic screen
(which I had never seen before Friday), there are 2 drop
downs at the top. One has "General", "Detail", "Page
Footer", etc. When I open the screen, it seems that I
copied the code into the "General" section. Is that where
it should be?

Thanks so much for your help!
Lynn
Click to expand...
** snipped **



.
Click to expand...
 
Lynn,
If you have no other code in the Report's Code window, this
is what your ENTIRE window should look like:

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!Loc
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

=============

Open up the report to the Report's Code window.
Click on Debug + Compile
(or click the Compile toolbutton .. It looks like a
stack of papers with a down arrow).

What happens?
If there is a coding syntax error, the error will be highlighted
and an error message will display. You'll have to fix each
error, one at a time, compiling until you no longer get an error.
Check your spelling and punctuation.
Count your If .. Then's and then count your End If's.
The numbers must match (2 each).

--
Fred

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


Lynn said:
Hi, Fred,

I just went through your instructions 3 times. I made
sure I'm in the report's code window.

I went to the Page Footer prperties, and clicked on Event
Procedure and the ..., to get to the code.

The appropriate lines were already in the General
section. So was everything else!


I have no other events or sub procedures, so immediately
after the line, I copied the rest of the code. I made
sure that I didn't copy the Private Sub Page Footer, etc..
line, or the End Sub line.

I did just what you said - when I put my cursor on the Dim
lins at the top, it showed "Declarations", and when I put
my cursor in the sub page footer section, the drop down
showed that I was in the Page Footer.

And I double checked that Me!Salesperson was changed to Me!
Loc. And that Loc is the name of the field that I am
grouping on.

I went back into design view, and added a test box for
="Page " & [Page] & " of " & [Pages].

Now when I try to run the report, I get an error:
"The expression On Format you entered as the event
property setting produced the following error: Expected"
line number or label or statement or end of statement.

The expression may not result in the name of a macro, the
name of a user-defined funtion, or [Event Procedure}.
There may have been an error evaluation the function,
event, or macro."

Huh???? If you can figure this out, I'll be forever in
your debt!!! I'm so confused!
Click to expand...
 
Fred,

I honestly don't know how to thank you for your support
and patience! It worked!

You are a remarkable person, and I truly appreciate that
you take time to help those of us who want to learn more
about working with Access. You are a real hero!!!!

Lynn
-----Original Message-----
Lynn,
If you have no other code in the Report's Code window, this
is what your ENTIRE window should look like:

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!Loc
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

=============

Open up the report to the Report's Code window.
Click on Debug + Compile
(or click the Compile toolbutton .. It looks like a
stack of papers with a down arrow).

What happens?
If there is a coding syntax error, the error will be highlighted
and an error message will display. You'll have to fix each
error, one at a time, compiling until you no longer get an error.
Check your spelling and punctuation.
Count your If .. Then's and then count your End If's.
The numbers must match (2 each).

--
Fred

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


Lynn said:
Hi, Fred,

I just went through your instructions 3 times. I made
sure I'm in the report's code window.

I went to the Page Footer prperties, and clicked on Event
Procedure and the ..., to get to the code.

The appropriate lines were already in the General
section. So was everything else!


I have no other events or sub procedures, so immediately
after the line, I copied the rest of the code. I made
sure that I didn't copy the Private Sub Page Footer, etc..
line, or the End Sub line.

I did just what you said - when I put my cursor on the Dim
lins at the top, it showed "Declarations", and when I put
my cursor in the sub page footer section, the drop down
showed that I was in the Page Footer.

And I double checked that Me!Salesperson was changed to Me!
Loc. And that Loc is the name of the field that I am
grouping on.

I went back into design view, and added a test box for
="Page " & [Page] & " of " & [Pages].

Now when I try to run the report, I get an error:
"The expression On Format you entered as the event
property setting produced the following error: Expected"
line number or label or statement or end of statement.

The expression may not result in the name of a macro, the
name of a user-defined funtion, or [Event Procedure}.
There may have been an error evaluation the function,
event, or macro."

Huh???? If you can figure this out, I'll be forever in
your debt!!! I'm so confused!
Click to expand...



.
Click to expand...
 
Back
Top