Changing page numbers?

[MarcieFess]

I'm running a report of products by aisle in a retail store. I have the report sorting on the aisle number.

Is there a way to make it re-number beginning at page 1 again for each aisle? I'm trying to avoid having to indicate which aisle number and having this company have to run a new report for each aisle number.

Right now my report is 187 pages, and it says "Page x of 187".

I want it to say this instead: if aisle 17 has 8 pages of information, I want the pages labeled Aisle 17 to say "Page x of 8", and to begin again at 1 for Aisle 18, etc.

Is this possible?

 

[AccessMSSQL]

Yes, you can do it. You have to control the page number for each group.

http://support.microsoft.com/kb/209215

 

[MarcieFess]

I appreciate the answer but I need more basic instructions. I have a page header and a page footer...but I don't have any other headers. Since I have no idea even how to enter a sub-header (like "Aisle Header" or whatever) these instructions don't help me. I'm an intermediate user that's been put into a situation with a client who SHOULD be paying a $200+/hour professional to do the job they want done, but they are cheap and hired an office temp instead. Now it's on my shoulders to produce.

 

[AccessMSSQL]

You need to first create a group header based on your Aisle field. Start there. Also, please always post what version of Access you are using so we have better information to help you with.

Here is a link on how to create a group header in a report:
http://www.functionx.com/access/Lesson25.htm

You need to go to design view of the report - find that tab menu at the top that says grouping and sorting and click it. You will see a pane appear at the bottom of your report where you can add a group. When you click ADD you should be able to pick your Aisle field. Once you set it up you should see a new section appear on your report. Try that first and see how you do.

 

[AccessMSSQL]

Here are some screen shots to get you started.

 

[MarcieFess]

Thank you!

Now I have the Aisle Number Header.

The next part of the issue is how to get the page numbering to work correctly. I have
="Page " & [Page] & " of " & [Pages]
at the top of the page, in the header.

I want the numbering to start over at 1 for each new aisle number.
I want the "of ###" to show the number of pages for that aisle, not the total number of pages in the report.

Can I do this?

 

[MarcieFess]

Forgot to say it's Access 2010 but the database for some reason saved as a 2007-2010.

 

[AccessMSSQL]

Have you used VBA code before? The solution is a bit complex as you need to set the Page property of the form = 1 on the Format Event of the Group Header (which is when a new group begins) That is a bit simpler but to get the total pages for a particular Aisle will be more complex. I have an example but it involves creating arrays wihtin the group footers. I will try to provide you with a simple example.

 

[MarcieFess]

It's been a long time since I've used VBA code...but I'm not a stranger to writing code. I'm actually in the process (beginning stage) of learning Java, as I want to write an application for something I'm doing personally.

 

[AccessMSSQL]

Here's the simple part. Just put this one line of code in the format event of the group header that just got created.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Page = 1
End Sub

Also, make sure your Aisle group has a footer section in it. Do this by expanding the Group node you created earlier and choose Has Footer Section. Then in the properties of the group footer, set the Force New Page After section = true.

This should get your page numbers reset to 1 each time.

The other part is more tricky.

 

[MarcieFess]

Great! That almost worked.

I'm working with products in aisles in a grocery store.

The aisles change depending on which store location I'm working with; that's set up in one of the tables in the database.

The particular store I'm using to create the report has 23 pages for Aisle 6, then it jumps to Aisle 13 (this is correct for this store). However, the first page of the Aisle 13 section still says Aisle 6...the second page of Aisle 13 says Page 2 of... correctly. In other words, Page 1 of the new aisle still has the aisle number of the previous aisle.

 

[MarcieFess]

Could it have something to do with the fact that the page numbers are in the Page Header, not the group header?

 

[MarcieFess]

Not sure how to send a screen shot of what my report has to look like.

 

[AccessMSSQL]

Yes. This is assuming your page numbers are in the footer. So in that case you would need to switch your code to reset the page=1 in the group footer.

 

[MarcieFess]

I've found a piece of code that's supposed to produce the results I want. Here it is:

'************ 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 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!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 *************
*** Note: the line Me!Salesperson should be changed to the control name of the group you wish to track pages for. Me!ctlGrpPages is the name of a control you should place in the page footer. You can also change this line to whatever form you wish your page numbers to take.

My question: where exactly does this go? The page number is in the Page Header (not the group header).

Right now we have it in the group footer, but it's prompting for the control group grp pages variable. Any ideas?

 

[AccessMSSQL]

You need to declare grpPages at the top of your report module like this:

Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer
[COLOR=red]Dim GrpPages As Integer
[/COLOR]