Page of Pages

[Bill]

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
..
..
..
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

 

[fredg]

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

 

[Bill]

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

From: "fredg" <[email protected]>
Subject: Re: Page of Pages
Date: Thursday, January 08, 2009 3:35 PM

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

Thanks Fred.

Looking at Jim's code, the statement
Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " &
GrpArrayPages(Me.Page)is never reached, as apparently If Me.Pages = 0 Then
is always true. I used Debug to trace thecode. The group name is changing
correctlyas each record is processed, but whateverit is that's supposed to
signify that the formatting is in the second pass isn'tworking. At least
that's my understandingof how Jim's code is going to recognizethe second
pass. Here's the whole
module:===============================================================Option
Compare DatabaseOption ExplicitDim GrpArrayPage(), GrpArrayPages()Dim
GrpNameCurrent As Variant, GrpNamePrevious As VariantDim GrpPage As Integer,
GrpPages As Integer '-------------------------------------Private Sub
Report_NoData(Cancel As Integer) MsgBox "There are no year-" & DonStmtsYr
& " offerings recorded for the " _ & "families or individuals
specified." Cancel = TrueEnd Sub
'-------------------------------------Private Sub Report_Open(Cancel As
Integer)Dim Jan1 As LongDim Dec31 As LongDim
LtrHdPathDoCmd.MaximizeMe.lblIPPhone.Caption = "Phone: " &
IPPhoneMe.lblIPAddress.Caption = IPAddressMe.lblIPCityState.Caption =
IPCityStateLtrHdPath = IPPath & "\LetterHd.jpg"If Len(Dir(LtrHdPath)) > 0
Then 'See if installation has a logo for their donation
statements Me.LetterHd.Picture = LtrHdPath 'There is an image file
Me.LetterHd.Visible = True 'Make it visibleEnd If'OpenArgs come
to us via public variables:'DonStmtsRSrc is the query to be used as the
reports RecordSource.'DonStmtsYr is the year used to build the filter
expression.'DonStmtsID is either empty, FamilyID or RegistryID; DonstmtsRSrc
differentiates latter two.Jan1 = CDate("1/1/" & DonStmtsYr)Dec31 =
CDate("12/31/" & DonStmtsYr)Me.RptHdr.Caption = "OFFERINGS FOR " &
DonStmtsYrMe.TotLbl.Caption = "Total for the year " &
DonStmtsYrMe.RecordSource = DonStmtsRSrcMe.Filter = "DOE >= " & Jan1 & " AND
DOE <= " & Dec31 'Range is one yearMe.FilterOn = TrueIf DonStmtsID = ""
ThenElse If Right(DonStmtsRSrc, 3) = "Fam" Then 'See if
query suffix is "Fam" Me.Filter = Me.Filter & "AND FamilyID = " &
DonStmtsID Else Me.Filter = Me.Filter & "AND RegistryID = " &
DonStmtsID End IfEnd If End Sub
'-------------------------------------Private Sub
PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)' 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'Dim i As Integer If Me.Pages = 0 Then ReDim
Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page +
1) GrpNameCurrent = Me!LastName 'Grouping on last name If
GrpNameCurrent = GrpNamePrevious Then GrpArrayPage(Me.Page) =
GrpArrayPage(Me.Page - 1) + 1 GrpPages = GrpArrayPage(Me.Page)
For i = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage
End If Else Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & "
of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrentEnd
Sub

 

[Bill]

YIKES! What happend to the formatting of my post???????

Let me try it again................................

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

From: "fredg" <[email protected]>
Subject: Re: Page of Pages
Date: Thursday, January 08, 2009 3:35 PM

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

Thanks Fred.

Looking at Jim's code, the statement
Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " &
GrpArrayPages(Me.Page)is never reached, as apparently If Me.Pages = 0
Then is always true. I used Debug to trace thecode. The group name is
changing correctlyas each record is processed, but whateverit is that's
supposed to signify that the formatting is in the second pass
isn'tworking. At least that's my understandingof how Jim's code is going
to recognizethe second pass. Here's the whole
module:===============================================================Option
Compare DatabaseOption ExplicitDim GrpArrayPage(), GrpArrayPages()Dim
GrpNameCurrent As Variant, GrpNamePrevious As VariantDim GrpPage As
Integer, GrpPages As Integer
'-------------------------------------Private Sub Report_NoData(Cancel As
Integer) MsgBox "There are no year-" & DonStmtsYr & " offerings recorded
for the " _ & "families or individuals specified." Cancel =
TrueEnd Sub '-------------------------------------Private Sub
Report_Open(Cancel As Integer)Dim Jan1 As LongDim Dec31 As LongDim
LtrHdPathDoCmd.MaximizeMe.lblIPPhone.Caption = "Phone: " &
IPPhoneMe.lblIPAddress.Caption = IPAddressMe.lblIPCityState.Caption =
IPCityStateLtrHdPath = IPPath & "\LetterHd.jpg"If Len(Dir(LtrHdPath)) > 0
Then 'See if installation has a logo for their donation
statements Me.LetterHd.Picture = LtrHdPath 'There is an image
file Me.LetterHd.Visible = True 'Make it visibleEnd If'OpenArgs
come to us via public variables:'DonStmtsRSrc is the query to be used as
the reports RecordSource.'DonStmtsYr is the year used to build the filter
expression.'DonStmtsID is either empty, FamilyID or RegistryID;
DonstmtsRSrc differentiates latter two.Jan1 = CDate("1/1/" &
DonStmtsYr)Dec31 = CDate("12/31/" & DonStmtsYr)Me.RptHdr.Caption =
"OFFERINGS FOR " & DonStmtsYrMe.TotLbl.Caption = "Total for the year " &
DonStmtsYrMe.RecordSource = DonStmtsRSrcMe.Filter = "DOE >= " & Jan1 & "
AND DOE <= " & Dec31 'Range is one yearMe.FilterOn = TrueIf
DonStmtsID = "" ThenElse If Right(DonStmtsRSrc, 3) = "Fam" Then
'See if query suffix is "Fam" Me.Filter = Me.Filter & "AND FamilyID
= " & DonStmtsID Else Me.Filter = Me.Filter & "AND RegistryID =
" & DonStmtsID End IfEnd If End Sub
'-------------------------------------Private Sub
PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)' 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'Dim i As Integer If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve
GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!LastName 'Grouping on
last name If GrpNameCurrent = GrpNamePrevious Then
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1 GrpPages =
GrpArrayPage(Me.Page) For i = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(i) = GrpPages Next i Else GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage GrpArrayPages(Me.Page) = GrpPage
End If Else Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & "
of " & GrpArrayPages(Me.Page) End If GrpNamePrevious = GrpNameCurrentEnd
Sub

 

[Bill]

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

Looking at Jim's code, the statement

Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " &
GrpArrayPages(Me.Page)

is never reached, as apparently

If Me.Pages = 0 Then

is always true. I used Debug to trace thecode. The group name is changing
correctly as each record is processed, but whatever it is that's supposed to
signify that the formatting is in the second pass isn't working. At least
that's my understanding of how Jim's code is going to recognize the second
pass.

Here's the whole module:

==================================================
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no year-" & DonStmtsYr & " offerings recorded for the "
_
& "families or individuals specified."
Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim Jan1 As Long
Dim Dec31 As Long
Dim LtrHdPath
DoCmd.Maximize

Me.lblIPPhone.Caption = "Phone: " & IPPhone
Me.lblIPAddress.Caption = IPAddress
Me.lblIPCityState.Caption = IPCityState
LtrHdPath = IPPath & "\LetterHd.jpg"
If Len(Dir(LtrHdPath)) > 0 Then 'See if installation has a logo
for their donation statements
Me.LetterHd.Picture = LtrHdPath 'There is an image file
Me.LetterHd.Visible = True 'Make it visible
End If

'OpenArgs come to us via public variables:
'DonStmtsRSrc is the query to be used as the reports RecordSource.
'DonStmtsYr is the year used to build the filter expression.
'DonStmtsID is either empty, FamilyID or RegistryID; DonstmtsRSrc
differentiates latter two.

Jan1 = CDate("1/1/" & DonStmtsYr)
Dec31 = CDate("12/31/" & DonStmtsYr)

Me.RptHdr.Caption = "OFFERINGS FOR " & DonStmtsYr
Me.TotLbl.Caption = "Total for the year " & DonStmtsYr

Me.RecordSource = DonStmtsRSrc
Me.Filter = "DOE >= " & Jan1 & " AND DOE <= " & Dec31 'Range is one
year
Me.FilterOn = True

If DonStmtsID = "" Then
Else
If Right(DonStmtsRSrc, 3) = "Fam" Then 'See if query
suffix is "Fam"
Me.Filter = Me.Filter & "AND FamilyID = " & DonStmtsID
Else
Me.Filter = Me.Filter & "AND RegistryID = " & DonStmtsID
End If
End If



End Sub

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

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

Dim i As Integer
If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)
GrpNameCurrent = Me!LastName
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

 

[Bill]

In James Brook's code to determine "Page x of n"
for a group, the expression "If Me.Pages = 0" in
and of itself does not force Access to make a
second pass to determine the total number of pages.
Rather, one needs to at least have an unbound text
box with data source "=pages" somewhere within
their report's design. In my case, I put it in the page
footer section in order for Jim's code to take the
desired code path on the second formatting pass.

Bill

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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

 

[Bill]

In James Brook's code to determine "Page x of n"
for a group, the expression "If Me.Pages = 0" in
and of itself does not force Access to make a
second pass to determine the total number of pages.
Rather, one needs to at least have an unbound text
box with data source "=pages" somewhere within
their report's design. In my case, I put it in the page
footer section in order for Jim's code to take the
desired code path on the second formatting pass.

Bill

I currently have a page footer:

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

Which isn't exactly what I need. I'd like
it to reset as I advance to the next record
in the recordset.

E.g.,
For record 1 Page 1 of 1
For record 2 Page 1 of 2
Page 2 of 2
For record 3 Page 1 of 1
.
.
.
etc.

How do I express that? With code or is
there something in grouping I'm not familiar
with?

Thanks,
Bill

First you need to group the records in a meaningful manner.
See:
"Printing First and Last Page Numbers for Report Groups "
http://www.mvps.org/access/reports/rpt0013.htm

Things to make sure of:

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

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

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