force new page for each header data 2

[TimTDP]

In Access 2000, I have a report that contains a:

Report Footer
Invoice Date Header
Delivery Driver Header
Detail
Page Footer
Report Footer

I currently individually select each delivery driver and print the report for that driver. I now want to create a command button that prints all the delivery reports. I want to keep the the entire print out as a single report, but with a page break after each driver. Each driver must get all the above detail on his report because I need to e-mail it.

What is the best method to achieve this?

 

[dhookom]

You can use the Force New Page property to create page breaks between drivers. Also, check out the Repeat Section property to get upper level group headers to display on every page.

Report Header and Footer sections display only once each. You can replace these sections with a new top level sorting and grouping level based on a constant expression like:
=1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Is there a method that I can include page numbers for each driver. e.g. page 1 of 2, page 2 of 2 for diver; 1 and page 1 of 1 for diver 2; page 1 of 3, page 2 of 3, page 3 of 3 for diver 3

 

[dhookom]

Try

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Thanks dhookom

The code works well, until I made a slight change to the report. I added a field to the detail section. Now the code does not work! I removed the code, saved the report, reinstalled the code and it works!

It appears that whenever the report changes, the code does not run correctly!

 

[dhookom]

Simply adding a "control" to the detail section should not break the code. What was the name of the control you added?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

The control was called "txtArea" and was placed in the detial section.

The code is like this:
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

The line of code that does not run after a report design change is:
Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)

because

If Me.Pages = 0 Then is always true!

 

[dhookom]

I can't understand why adding a text box to the detail section would mess up the code. There are a number of steps I would take to trouble-shoot including using debug.print, compiling the code, trying other text boxes,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Just to confirm, I have added the code to the Page Footer, On Format Event. Is this the correct place?

 

[dhookom]

I believe the page footer on format is correct. Did you also add the variable declaration to the General Declarations section?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Yes

 

[dhookom]

TimTDP,
Any response to my other suggestions: "There are a number of steps I would take to trouble-shoot including using debug.print, compiling the code, trying other text boxes,..."

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

I have tried everything that I can think of. The line of code:

If Me.Pages = 0 Then is always true!

Many thanks for your help.

 

[dhookom]

I just created a report to test this. I found the event in Access 2003 is actually PageFooterSection_Format. The code should be:

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Mine is the same!

 

[dhookom]

Look at your event properties dialog for the Page Footer section. Do you see anything in the On Format property? You should see [Event Procedure].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Yes I do!

 

[dhookom]

Can you copy and paste the total code from your module into a reply?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TimTDP]

Option Compare Database
Option Explicit

Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer

Private Sub PageFooterSection_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.DeliveryDriver
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 = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent


End Sub

 

[dhookom]

I don't think this statement in the MVPS link is true

Because the code listed uses the Pages Property (Total number of pages), you automatically force access to format the report twice.

Add a text box to your page footer section with a control source of:
="Page " & [Page] & " of " & [Pages]
You can make this text box invisible if you want.

If this fixes your issue, please let me know so I can have the MVPS.ORG/Access page updated.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]