mvps.org code for page x of y by group does not work

[kiln]

For some reason, the oft recommended code at

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

does not work for me. Adapted code follows:

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 = POID ' this is the grouped on field
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
txtPage = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages
(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

The format event is not running 2x as expected, thus the value of
txtPage is never set. Anyone see that is going on here? Any chance that
this code just doesn't work with Access 2002, latest sp? Or doesn't work
with print preview?

 

[Cheryl Fischer]

The key to making this work is that you need two controls in your page
footer:

The first should be named ctlGrpPages and should be Visible. It will hold
the Page x of x for the group. The second should be a textbox control,
name is unimportant and it can be invisible. Its Control Source should be:
=[Pages]

Setup in this way, the original code from the Access Web that you were using
will work as it should.

 

[kiln]

It does indeed. Am I blind, or is this control not mentioned in the code
at http://www.mvps.org/access/reports/rpt0013.htm? I can see now how it
works, and that there is oblique reference to the need to initialize
Pages. But there is no instruction akin to that you've kindly offered,
at least that I can decipher...

Thanks!

The key to making this work is that you need two controls in your page
footer:

The first should be named ctlGrpPages and should be Visible. It will hold
the Page x of x for the group. The second should be a textbox control,
name is unimportant and it can be invisible. Its Control Source should be:
=[Pages]

Setup in this way, the original code from the Access Web that you were using
will work as it should.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

For some reason, the oft recommended code at

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

does not work for me. Adapted code follows:

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 = POID ' this is the grouped on field
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
txtPage = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages
(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

 

[Cheryl Fischer]

It is, as you indicate, somewhat oblique. Many who ask about this issue
seem to "get it" right off. Until the "key" was explained to me, I was
among those who didn't.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

It does indeed. Am I blind, or is this control not mentioned in the code
at http://www.mvps.org/access/reports/rpt0013.htm? I can see now how it
works, and that there is oblique reference to the need to initialize
Pages. But there is no instruction akin to that you've kindly offered,
at least that I can decipher...

Thanks!

The key to making this work is that you need two controls in your page
footer:

The first should be named ctlGrpPages and should be Visible. It will hold
the Page x of x for the group. The second should be a textbox control,
name is unimportant and it can be invisible. Its Control Source should be:
=[Pages]

Setup in this way, the original code from the Access Web that you were using
will work as it should.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

For some reason, the oft recommended code at

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

does not work for me. Adapted code follows:

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 = POID ' this is the grouped on field
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
txtPage = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages
(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

 

[kiln]

Thanks again, a super help.

 

[Guest]

OK, I still don't get it. Cheryl, in your reply to
another post, you said "....ctlGrpPages and ..... The
second should be a text-box control,..." You emphasized
that the second control should be a text box. What is
the first control? I've tried this code and placed two
text boxes and it doesn't work. I did name the controls
as you suggested and changed the code accordingly.

The code also for some reason interferes with another
function I am performing. The report is based on a
query. The query contains an "empty" expression which
requires the entry of a number. Essentially, I'm summing
a control on the report then multiplying that sum by the
entered number.

-----Original Message-----
It is, as you indicate, somewhat oblique. Many who ask about this issue
seem to "get it" right off. Until the "key" was explained to me, I was
among those who didn't.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

It does indeed. Am I blind, or is this control not mentioned in the code
at http://www.mvps.org/access/reports/rpt0013.htm? I can see now how it
works, and that there is oblique reference to the need to initialize
Pages. But there is no instruction akin to that you've kindly offered,
at least that I can decipher...

Thanks!

Visible. It will
hold Control Source should
be:

=[Pages]

Setup in this way, the original code from the Access

Web that you were
using FormatCount As
Integer) & " of " &
GrpArrayPages

.

 

[Cheryl Fischer]

Here is how it works when I use the technique described at:
http://www.mvps.org/access/reports/rpt0013.htm

1. Two textbox controls in the PageFooter of the report
a. One textbox named: ctlGrpPages No other properties set
b. One textbox named: Text18 ControlSource property set to:
=[Pages] Visible set to 'No'

2. Code from the above link copied with no changes to the OnFormat event
of the Page Footer

I can't comment on whether or not the code from the link interferes with
your other code or not, as I haven't seen your other code. For testing
purposes, I'd comment out that code and then see if the code from the link
for Page Numbers works. Then put your code back and see what happens.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

OK, I still don't get it. Cheryl, in your reply to
another post, you said "....ctlGrpPages and ..... The
second should be a text-box control,..." You emphasized
that the second control should be a text box. What is
the first control? I've tried this code and placed two
text boxes and it doesn't work. I did name the controls
as you suggested and changed the code accordingly.

The code also for some reason interferes with another
function I am performing. The report is based on a
query. The query contains an "empty" expression which
requires the entry of a number. Essentially, I'm summing
a control on the report then multiplying that sum by the
entered number.

-----Original Message-----
It is, as you indicate, somewhat oblique. Many who ask about this issue
seem to "get it" right off. Until the "key" was explained to me, I was
among those who didn't.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

It does indeed. Am I blind, or is this control not mentioned in the code
at http://www.mvps.org/access/reports/rpt0013.htm? I can see now how it
works, and that there is oblique reference to the need to initialize
Pages. But there is no instruction akin to that you've kindly offered,
at least that I can decipher...

Thanks!

The key to making this work is that you need two controls in your page
footer:

The first should be named ctlGrpPages and should be

Visible. It will
hold

the Page x of x for the group. The second should be a textbox control,
name is unimportant and it can be invisible. Its

Control Source should
be:

=[Pages]

Setup in this way, the original code from the Access

Web that you were
using

will work as it should.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


For some reason, the oft recommended code at

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

does not work for me. Adapted code follows:

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 = POID ' this is the grouped on field
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
txtPage = "Page " & GrpArrayPage(Me.Page)

& " of " &
GrpArrayPages

(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub

.

 

[kiln]

This is the most unfathomable code and report behavior I've seen in a
bit. It still isn't working. The report is set up so that it may preview
more than one Purchase Order at a pass. Thus the Purchase Order ID is
the value that the report groups on. The Purchase Order ID group footer
has force new page after (altho I've also removed with no diff). As an
example, two POs are to be printed (previewed). The first is longer and
has two pages, the second is shorter and has only one page. I expect the
footer page message to be 1 of 2, then 2 of 2, then 1 of 1. But I get 1
of 1, 1 of 2, 2 of 2. Debugging shows that by the time of the second
pages footer format event, the Purchase Order ID value that is passed to
the function is already pointing to the second PO, not the first as it
should be. The footer message is therefore accurately reporting the data
in the arrays; but why is the Purchase Order ID from the 2nd PO being
pulled into the footer from the 1st PO. Very odd. I have set up a simple
textbox in the footer that has the value of the PO ID and it stays true
to the data as I'd expect; ie two pages have the same value followed by
the PO ID of the new PO for the third page. But still the array is
always passed the 2nd PO ID a page early as it's about to tranistion to
a new PO. I've tried pulling out all page forcing, keep together etc
properties as a test and it's 100% consistent regardless. Can anyone
sense what's going on here? Code is directly off the website, not mods.

It is, as you indicate, somewhat oblique. Many who ask about this issue
seem to "get it" right off. Until the "key" was explained to me, I was
among those who didn't.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

It does indeed. Am I blind, or is this control not mentioned in the code
at http://www.mvps.org/access/reports/rpt0013.htm? I can see now how it
works, and that there is oblique reference to the need to initialize
Pages. But there is no instruction akin to that you've kindly offered,
at least that I can decipher...

Thanks!

The key to making this work is that you need two controls in your page
footer:

The first should be named ctlGrpPages and should be Visible. It will hold
the Page x of x for the group. The second should be a textbox control,
name is unimportant and it can be invisible. Its Control Source should be:
=[Pages]

Setup in this way, the original code from the Access Web that you were using
will work as it should.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


For some reason, the oft recommended code at

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