Blank page after category on Report

[currie]

I am creating a report that is based on various different categories of information (example: Airports, Fire Departments, Police Stations are all "categories").

For each category, there are a varying number of records. For example, Airports may have 5 records, Fire Departments may have 13 records, etc. What I need to do is insert a blank page after each category if that category ends its output on an odd-numbered page. IE: If there are 5 records for Airports, one per page, then pages 1-5 (double-sided) would be printed out with the info on them.

The problem is, on the back side of page 5, the first result from the next category would be printed. If updates are made to the "Airports" section, you would have to replace the entire paper-version of the report to update it, rather than simply being able to replace the "Airports" category.

Any help greatly appreciated,


- Ryan

 

[Chris RR]

Well, there's probably a better way, but why not make each section a stand-alone report, then always run them together (either in code or with a macro)? That way, you'll always start each "section" on a right-hand-side page.

If page numbering is a problem, maybe your users can adapt to a secion-prefix-plus-a-number scheme (Airports are pages A1 through A???, Fire Departments are B1 through B???, and so on).

 

[currie]

Chris,

I did have each section as a stand-alone report, however, that requires that I have about 180 individual reports (I'm sure you see the inconvenience here

).

I may have a solution using grouping, however. Thanks for your response!


- Ryan

 

[David R]

Looking at the long term effects of your report structure...what if the number of airports grows to 18? You can add blank pages to preserve shrinking sections, but there's no way of making the report fit 10 gallons of ... water into a 5 gallon bucket, if that's all you have allotted for it.

I think Chris's suggestion of Section titles is a good one. Can you perhaps include the Group in the page footer, and restart the page count for each grouping? Then you'd have something a little less arcane than B-12, it would be Airports, Page 12 of 13.

Just my morning maunderings,
David R

 

[currie]

Ok, this is what I've come up with.

In the page header, I'm going to add code that does this:

round((detail_count / 4)+0.25)

Thus the result from this will be the page number. Now, I need to know if here is a VB function to determine if the number is even or odd? On odd-numbered pages, I want to insert the blank page, and on even-numbered pages I don't need to.

Any suggestions?? Or a better/easier method perhaps?


Thanks!


- Ryan

 

[currie]

David,

The report doesnt use page numbers. The categories of the report each have a 'tab' along the vertical edge with the category title on it, so that you can quickly thumb through the report to find the category you need.

There is no purpose to numbering the pages in this report - it's actually for an emergency plan which is updated every 6 months. When a section of the plan is updated, the recipients are only provided with the updated portion of the report, not the entire report (because its about 400 pages long, double-sided)... this is why I need to solve this problem!

- Ryan

 

[David R]

You're golden then. I think the function you're looking for is IsOdd(). Check the help for it and you should be on your way.

(there may be a way to directly query what page you're in from the group footer, I'm not sure).

One thing though, currie: You said "in the page header, I'm going to add code..." I believe you meant in the group footer? Otherwise you'd be adding an extra page after EVERY odd numbered page, which could get recursive in a hurry.

HTH,
David R

 

[currie]

David,

IsOdd is EXACTLY what I need... except that it seems to be a no-go! Access 2000 won't accept it as a valid function. Any other suggestions??


- Ryan

 

[Val]

If you look in help in access 2000 there is a specifc .dll that is required to support the IsOdd function. I think it is msowcf.dll, not sure though.

Hope this helps!
Val

 

[currie]

Val,

I just came across this info... my question is - will each computer that uses this program require that that DLL be installed?

Thanks!!


- Ryan

 

[Val]

Ryan-

Yes it will, for the most part the initial installation of Access 2000 took care of it. We only had to manually add to a couple of PCs.

Val

 

[currie]

This is where I'm at with the code. It seems to work (ie: the message box pops up when it should), however, the code to print the blank page doesn't work - the code I'm using is from the Access 2000 Help, however, I'm not sure what the "Section(acDetail)" refers to. Any ideas? I'm trying to avoid using the "IsOdd()" because of the variety of computers the program will be used on (and most users will not be computer-savvy).


Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If (PageCounter Mod 2 <> 1) Then
'Insert Blank Page
Dim intGetVal As Integer
intGetVal = Reports![Appendix_4a].Section(acDetail).ForceNewPage
MsgBox PageCounter, , "This Should Only Appear On Reports With An Odd Number of Pages"
End If
PageCounter = 1
End Sub


Thanks again all,


- Ryan

 

[currie]

Slightly more progress...

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If (PageCounter Mod 2 <> 1) Then
'Insert Blank Page
Reports![Appendix_4a].Section(acFooter).ForceNewPage = 1
MsgBox PageCounter, , "This Should Only Appear On Reports With An Odd Number of Pages"
PageCounter = -1
Else
Reports![Appendix_4a].Section(acFooter).ForceNewPage = 0
PageCounter = -1
End If
End Sub

The downer is that its not forcing the new page properly, but the page counter seems to be working (incrementing properly), as the message box appears only when it should.


- Ryan

 

[currie]

Well, I got it to work, but not by conventional means. I ended up adding a couple text boxes to the Group Footer, and then using the code below (along with a PageCounter increment in the page header):

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
'If the last page of the category is odd:
If (PageCounter Mod 2 > 0) Then
'Insert blank page:
Blank_Page_Box1.Visible = True
Blank_Page_Box2.Visible = True
Blank_Page_Box1 = "This blank page is inserted intentionally to accommodate future Emergency Plan Updates."
Blank_Page_Box2 = "This page is intentionally blank to facilitate future plan revisions."
Header_Detail.Visible = True
'Reset the page counter:
PageCounter = 0
Else
'Reset blank page indicator:
Blank_Page_Box1.Visible = False
Blank_Page_Box2.Visible = False
'Reset the page counter:
PageCounter = 0
Header_Detail.Visible = True
End If
End Sub

I set the Blank_Page_Box1 and Box2 to invisible, and Blank_Page_Box1 was a "vertical" textbox, so that when the page number was odd, the text box would be filled with enough letters to stretch the group footer to take up an additional page. Not so fancy, but works perfect.

Thanks again to all for your help.


- Ryan

 

[mrpdbaker]

Another solution

I had this problem and managed to come up with a solution by adding a group footer with a single label with a single dash in it with the font color set to white.

I set the force new page option on the group footer to be "before section".

I then inserted two bits of vba

One into the page header format event to increase a counter

pageCount = pageCount + 1

and some into the group footer format event:

If pageCount Mod 2 = 0 then

' skip this footer this time

cancel = true

End If

That basically seemed to work. I say basically because it worked fine in preview mode but when I tried to print it to a pdf document the end of the first group header had a blank page that was not needed and I had to delete manually. Haven't got my head round why that was just yet.