Page Numbers

[garyd1234]

I have a report where the page numbers have to change based on a department code changing. Every time the dept code changes I want the page number to reset to 1. Example Dept 1 might have 3 pages, then reset and dept 2 might have 6 pages. etc. I want it to say Page 1 of 3, then when the dept code changes say Page 1 of 6 etc. Do I insert and object for page numbers? What header do I put it in- Report Header or Page Header or Dept Code Header and how do I make it change? Thanks

Gary

 

[Stubby Holder]

Hi Gary,
I don't think tou can reset the Total number of pages in a report, so you need to loop print each dept as a separate report.Try something like this in the report button click event, & it doesn't matter which header for this method (change DEPTCODE to match the fieldname for your dept):

Private Sub cmdYourReportButton_Click()
On Error GoTo Err_cmdYourReportButton_Click
Dim db As Database
Dim rs As Recordset
Dim strFilterCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct DEPTCODE from tblYourTable")

rs.MoveFirst
Do Until rs.EOF
    strFilterCriteria = "[DEPTCODE] = '" & rs!DEPTCODE & "'"
    DoCmd.OpenReport "rptYourReport", acNormal, ,strFilterCriteria
    rs.MoveNext
Loop

Exit_cmdYourReportButton_Click:
    Exit Sub

Err_cmdYourReportButton_Click:
    MsgBox Err.Description
    Resume Exit_cmdYourReportButton_Click
End Sub

(You can reset the page number, but I don't think you need to do that in this case as each dept report will start at 1 anyway !)

Stubby

 

[Dr_Worm]

I used the above code to do the same thing with labels I needed to print, now I've been asked to make the same labels reset for two different changes, "Room" and "ItemCode".

At the moment it already resets the page number everytime the room number changes. In a room there may be four items with the code WR01 and 3 items with the code LR01. The labels need to show; 1 of 4, 2 of 4, 3 of 4, 4 of 4, 1 of 3, 2 of 3, 3 of 3. in each room.

I'm not sure what changes I need to make to the code.

There is always more to learn.

 

[bat17]

I think that you need a group query for this try the following untested code

Private Sub cmdYourReportButton_Click()
On Error GoTo Err_cmdYourReportButton_Click
Dim db As Database
Dim rs As Recordset
Dim strFilterCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select DEPTCODE, SubCode from tblYourTable GROUP BY DEPTCODE, SubCode")

rs.MoveFirst
Do Until rs.EOF
    strFilterCriteria = "[DEPTCODE] = '" & rs!DEPTCODE & "' AND [SubCode] = '" & rs!SubCode & "'"
    DoCmd.OpenReport "rptYourReport", acNormal, , strFilterCriteria
    rs.MoveNext
Loop

Exit_cmdYourReportButton_Click:
    Exit Sub

Err_cmdYourReportButton_Click:
    MsgBox Err.Description
    Resume Exit_cmdYourReportButton_Click
End Sub

HTH

Peter

 

[CT Witter]

You can also try some of the microsoft knowledge base articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc - How to Reset the Page Number and Total Page Count for Each Section of a Report
*By using a macro or code with a report's section properties, you can design a report that breaks the page for each new entry in a group and resets the report's page number. For example, if the first group of records consists of two pages, you can number them "1 of 2" and "2 of 2." If the second group of records consists of three pages, you can number them "1 of 3," "2 of 3," and "3 of 3."*

To get individual line numbers or room numbers try something like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;296246&Product=acc - How to Print Line Number for Each Record/Group on a Report

*You can add a text box control that prints a sequential number for each record to the detail section of a report. You can reset this number so that a separate set of numbers is printed for each group of records. *

HTH,
CT

 

[Dr_Worm]

Thankyou for your assistance ,

The code does exactly what I needed, and the microsoft knowledge base articles were good to read.

Dr Worm