Resetting Page Numbers after each Group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ross - 30 Nov 2005 17:08 GMT

How can I re-set the number after each group on my many paged report?

Thank you

Ross

Reply to this Message

fredg - 30 Nov 2005 18:05 GMT

> How can I re-set the number after each group on my many paged report?
>
> Thank you
>
> Ross

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 a control to the page Footer that computes [Pages]
= [Pages]
You can make this control not visible if you wish.

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

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

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

Signature

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

Reply to this Message

Ross - 30 Nov 2005 19:07 GMT

Thank you Fred!

I will grind through this.

> > How can I re-set the number after each group on my many paged report?
> >
[quoted text clipped - 18 lines]
> 4) In the code, change Me!Salesman to
> Me![Name of the control used to group by]

Reply to this Message

Ross - 01 Dec 2005 18:19 GMT

Fred,

I ran into a problem with the code. The code is not able to recognize the
second page of the first group. For example, If my groups are G1 (2 Pages)
and G2 (3 Pages) (Total of 5 Pages) Here is what I get.

Get SB
G1 1 of 1 1 of 2
G1 1 of 4 2 of 2
G2 2 of 4 1 of 3
G2 3 of 4 2 of 3
G2 4 of 4 3 of 3

The code can't recognize the second page of the G1.

Do you have any ideas!

> > How can I re-set the number after each group on my many paged report?
> >
[quoted text clipped - 18 lines]
> 4) In the code, change Me!Salesman to
> Me![Name of the control used to group by]

Reply to this Message

fredg - 01 Dec 2005 20:01 GMT

> Fred,
>
[quoted text clipped - 35 lines]
>> 4) In the code, change Me!Salesman to
>> Me![Name of the control used to group by]

My guess is you have done (or not done) something elsewhere that is
causing your difficulty. That code works just fine.
If you wish, create a small sample database importing the report and a
sampling of enough data to run it. Send it to me and I'll take a look
at it and get back to you. If you do send it make sure the Subject
line of the email reads "Grouping database" otherwise I won't read it.
jandf at adelphia dot net (no spaces and insert the appropriate
symbols)

Signature

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

Reply to this Message

Ross - 01 Dec 2005 14:08 GMT

Hi Fred,

I am having a little trouble getting this code to work. The Problem is when
the GrpNamePrevious = GrpNameCurrent, this step works OK but the Next step is
"End Sub". The set empties the two variables plus other variables and forces
the loop based on those "reset" conditions.

Also, I don't know exactly what you mean by a control where [Pages] =
[Pages]. I made a text box called Pages and set it equal to =[Pages]. Is
that what you meant?

Here is the code that I am trying to make work.

'*******************************************************************************
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
'*******************************************************************************
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Dim i As Integer
StartOver:
If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)
GrpNameCurrent = Me.txtSeries

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 <-------This resets in the next step
'GoTo StartOver <-------- This is my ideal to prevent the reset (and it
does) be it

End Sub

> How can I re-set the number after each group on my many paged report?
>
> Thank you
>
> Ross

Reply to this Message

Ross - 01 Dec 2005 14:12 GMT

Hi Fred,

Please ignore the previous question. I got it to work. I simply moved the
ReDim's outside of the sub (under Option Explicit).

This is really Cool!

Thanks Again

> How can I re-set the number after each group on my many paged report?
>
> Thank you
>
> Ross

Reply to this Message