On condition, add a blank page to report record

[BostonScott]

As a first time poster, I'd like to say hello to everyone!

I have a simple report that has a one to many relationship in the data being printed. The top part of the report contains the "one information and the bottom contains the "many records like most reports.

Sometimes there are enough records to cause the report to push the report length to two pages. Other times only one page is needed. If there are 6 records, then only one page is needed and more than six causes the next record to go to the next page.

Since I need to print this double sided, I would like to add a blank page after the pages that have only 6 records. That way, every record has two pages and I can just print the whole report double sided.

Is there some way to add code that is like IF less than 7 records THEN add second page ELSE print as is?

Let me know if you need more information. Thanks for your input

 

[theDBguy]

Hi Scott. Welcome to the forum. There are a couple of ways to approach this, I think. One way is to add a grouping in your report and then try using the Force New Page for the group section. Another is to maybe add a Page Break in the report and hopefully hiding it would not cause a new page, so when you make it visible, for 6 or less records, then you can force a new page. Or, you could add some dummy records, perhaps through a union query to make sure each group of records has a minimum of 7 records. Just a thought...

 

[Deleted member 147267]

add an unbound textbox txtCounter; set source to =1 (try running sum over group first)
add page break under detail section controls
in report detail format event enter
Me.NameOfYourPagebreakControl.Visible = Me.txtCounter Mod 6 = 0

That should do it but you would have to modify code to use an odd number like 7 (I'm thinking IF block).You can hide counter when all is working.

 

[BostonScott]

Micron -- a couple of questions about your reply. I understand the concepts of what you are asking me to do, but I'm not quite sure how to implement it. I'm a complete novice.

I understand the first three lines of your solution, and understand the math behind the 4th line
"Me.NameOfYourPagebreakControl.Visible = Me.txtCounter Mod 6 = 0"
but I'm not sure how to implement that.

Also, I'm not sure about your last line either. Specifically
"modify code to use an odd number like 7 (I'm thinking IF block)"
I'm not sure about what to do here.

As for the IF, it was suggested elsewhere that it might be something like this:
If Me.RecCounter Mod 6 = 0 Then
Me.PageBreak.Visible = True
Else
Me.PageBreak.Visible = False
End

This may be quite stupid, but how to I set the value of Me.RecCounter or is this some internal value that is automatically set?

Can you give me a bit or a push in the right direction?

Thanks

 

[Deleted member 147267]

Sounds like you are saying you can add a textbox, name it txtCounter and set its running sum property to 'over group' and add a page break in the detail section below the controls.

Then you select the detail section in report design view and on the property sheet Event tab you click on the ellipses (...) in the row that has On Format to open the vb editor (at least that's how it works for me because of my option settings). You should now have the beginnings of a sub that looks like

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

End Sub

You put in the line I provided (making sure you swap in your names for the page break and counting textbox).
Now for that line; the left side means
"the visibility of the page break =". The right side evaluates to True or False, thus if the line count is evenly divisible by 6 visibility is True, otherwise it is False. The IF block you cite is just a longer way of saying in 5 lines what I wrote in 1. The IF I was referring to you can forget about. It was a brain fart about odd numbers being divided by even numbers, which doesn't apply.

AFAIC, there is really no such thing as a stupid question here. My habit is to assume the OP knows something (I've actually been chastised for replying as if someone knew nothing) and don't mind expounding. What I stay away from is posters who answer nothing when I've asked the same questions 2x and cross posters who don't acknowledge their transgressions, but maybe that's just me. I figure the pay here isn't high enough that I'm going to allow anyone to frustrate me. OK, off the soap box!!

 

[BostonScott]

Stay on your soapbox! I was a teacher for 20 years and what you say is on target. We all have to learn somehow. Btw, I learned programming (Pascal and OOP wan't even around) on an IBM 370 mainframe. Things have changed.

I will will give my best at implement this in the morning and get back to you. Thanks again!

 

[Deleted member 147267]

Btw, I learned programming (Pascal and OOP wan't even around) on an IBM 370 mainframe.

Surely it wasn't a new machine at the time? That would make you a bit older than me, which ought to defy the laws of General Relativity or something...
P.S. - forgot to mention in my diatribe about 'no stupid questions' I forgot that when I first started learning VBA about 15 years ago I had to ask someone how to present a user message with the message box function. We all have to start somewhere, and there's still TONS of it that I'll never know; at least not by rote.

 

[BostonScott]

I finally got to give this a try. Below is a summary

• Created an unbound textbox called txtCounter and added it to the header section
• set source to =1 ---- Not sure I did this correctly. I entered =1 into the Control Source box of txtControl
• Set Running Sum to “Over Group”
• added page break under detail section and named it PageBreakDetails
• With detail section selected, on event tab, in Format row added: Me.PageBreakDetails.Visible = Me.txtCounter Mod 6 = 0

When I view the report, nothing changes.The database contains 3 records. The subtables contain 3, 0, and 8 records respectively.

Page 1 → txtControl displays 1 and 3 subrecords (correct) are shown but no blank page follows
Page 2 → txtControl displays 2 and 0 records (correct) are shown but no blank page follows
Page 3&4 → txtControl displays 3 and 6 records are shown and the remaining two are forced onto page 4 as only 6 records can fit on the bottom of the page

To try to see what was going on, I changed the control source in the txtCounter box to =Count(*). I get a total of 11 pages.
Page 1 → txtControl displays 3 and 3 records (correct) are shown but no blank page follows
Page 2 → txtControl displays 4 and 0 records (correct) are shown but no blank page follows
Page 3 → txtControl displays 12 and 1 record is shown. There are 8 records total and the rest are shown on each of the next 7 pages. I know this is because the control is set for testing.

Any other suggestions?

And yes, it was 1983 and we still used the IBM 370. Only the upperclassmen could use a CRT (80x24 lines) I was stuck with a DEC Writer II paper terminal… Who says the past was better?

Thanks

 

[Deleted member 147267]

Created an unbound textbox called txtCounter and added it to the header section

No, detail section. Sorry I left out that point explicitly but second time (in post 5) kinda infers it.

added page break under detail section and named it PageBreakDetails

No. In detail section; see post 5. I think it's best to just ignore the rest of your post until you correct those 2 items. No way it would work the way you have it. What I posted works so there is hope for success. BTW, some events don't work in certain report views. This is all predicated on print preview. That had better be OK or it's game over, I think.


EDIT: your comment about "sub tables" is a bit concerning. Whatever you're getting in the detail section, it has to be enough records to work with the size of groups you want to create and cannot be a sub report.

 

[BostonScott]

I've added the database for clarity

I've moved the field to the Details section

Maybe I am being confusing about the setup of the form and the database. Let me clarify. There are two linked tables in a one to many relationship. The first table is members and the second table is deceased relatives (names, dates, etc). (for testing, I have only 3 records in the table. The linked tables have 3, 0, and 8 records. This allows a test of all conditions: under 6, zero, and over 6) The report looks something like this:

First Member Info (table 1)
1 Mother, Fname, Lname (table 2)
2 Father, Fname, Lname (table 2)
3 Son, Fname, Lname (table 2)
<pagebreak>
Second Member Info (table 1)
*No records*
<pagebreak>
Third Member Info (table 1)
1 Mother, Fname, Lname (table 2)
2 Father, Fname, Lname (table 2)
3 Son, Fname, Lname (table 2)
4 Grandmother, Fname, Lname (table 2)
5 Grandfather, Fname, Lname (table 2)
6 Aunt, Fname, Lname (table 2)
*Because the details section can only hold 6 records, the remaining 2 are kicked to the next page
7 Uncle, Fname, Lname (table 2)
8 Sister, Fname, Lname (table 2)

This results in 4 pages. in the first two sections, where there are 3 and 0 records in the sub report (specifically 6 or less) I need to have a manual page break so that there is a front and backside to the page when I print double sided.
The third section automatically generates 2 pages because it has 8 records and records 7 and 8 move to the next page.

This will result in 6 pages
1. Record 1
2. Blank
3. Record 2
4. Blank
5. Record 3 (records 1-6 of table 2)
6. Record 4 (records 7-8 of table two)

When this is printed double sided, it will print correctly.

Now when I run the report, the txtCounter shows 1,2,3 right along with each record. On the part with zero records, it shows a 1. On the thrird record, it numbers 1 -8 inline with each record

The details section has a pagebreak called PageBreakDetails.

The Event On Format for the Details sections has the following:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.PageBreakDetails.Visible = Me.txtCounter Mod 6 = 0
End Sub

I hope this helps

One question I have is about MOD.

I assume this means "make the pagebreak active when txtcounter mod 6 = 0?" This would happen when there are no records and 0 MOD 6 =0 and when there are 6 records as 6 MOD 6 = 0. The double "=" in the line messed me up for a second. Both conditions work becasue in both cases there should be a page break inserted. (There will be a problem with 12 or 18 but we can deal with that later.

So, where should I go from here?

I've attached the database this time. I hope it helps

 

[Deleted member 147267]

you can't receive or don't allow private messages so will have to post that here.

won't be able to review your latest until later tonight - that's a lot to digest and I've got errands to run.
I see somebody downloaded your db before me so they might beat me to a reply. Sounds like the build of your report isn't conducive to what you're after, but not sure as you're using terms/phrases that don't help with clarification. Later...

 

[Deleted member 147267]

BTW, some events don't work in certain report views. This is all predicated on print preview.

You are opening the report in report view by default. Either change the default to print preview (property sheet, format tab) or open it by a more controlled method, such as a form control or right-click on report in nav pane. That should get you closer to what you want. If so, the next step is to deal with the Mod problem.
EDIT - Actually, would suppressing the group section altogether be the right thing to do if there are no child records, or do you still need the part with the lines?

 

[BostonScott]

I'm sorry for the delayed replay and equally sorry if I am being confusing.

You are opening the report in report view by default. Either change the default to print preview (property sheet, format tab) or open it by a more controlled method, such as a form control or right-click on report in nav pane.

I'm the design view to work in and only the print view to view the results of changes. I switch between the two by using the nav bar.

I'm not sure that a MOD solution would work as it would only affect when thee are exactly 0 or 6 child records. I also need a new page if there are 1-5 records (i.e. any case of <= child records) Seven or more will just flow onto the next page which is perfect. Six or less need a blank page added.

If I could determine the total number of child records without counting them as each record is added in the creation of the report (that's what seems to happen if I use Count() ), then it might be much easier. I could simply say if there are <= six child records, insert a page break after the last record, otherwise don't do anything. BUT, I have no idea how to do that.

Can you provide some insight? I'm really up against the wall here.

 

[Deleted member 147267]

OK, I was going by the view property on the property sheet Format tab. When I opened report, it was the wrong view. At this point, I figured out how to suppress printing a group header that has no records, but am thinking that's wrong.

Requirements seem to be changing as we go - or at least my understanding of them is. Let's see if we can summarize with respect to the group "unique_id header" and the number of records in each group:

- if no records, print the header and a page break
- - (or is it "don't print and no break"?)
- - (or is it "don't print but do break"?)
- - (or does any of that depend on what happened before?)
- if records >= 1 and =< 6 print header and up to 6 records then a page break
- if records >= 7 and =< 12 same as previous
- if records >= 13 and =< 18 then what?
- anything not covered...

Maybe you could identify using the above or create your own summary, trying to get all the situations covered. I like to help, but it's more fun if the target is known and static.

 

[BostonScott]

Again, sorry for the confusion. THe view that it opened to must be the last state I saved it in. As far as determining if the whole thing is working, I use the Print view.


Let me see if I can summarize even further:

The rule is, I always need two pages so that two information sheets don't end up back to back on a double sided printing. This way, there are ALWAYS 2 pages per full record (parent and child? not sure of the term).
- If there are <= 6 records, i need a page break to make a second page.
--If there are >6 child records, they flow to the next page automatically, so there is no need for a manual page break to cause a second page.
-- Anything above 6 records doesn't matter at all. There are never more than 18 child records so I never have to worry about the second page filling up (i.e. there will never be a 3rd page or more)

Using what you wrote:
if no records, print the header and a page break YES print headers and pagebreak
- - (or is it "don't print and no break"?) Nothing to print anyhow.
- - (or is it "don't print but do break"?) NO
- - (or does any of that depend on what happened before?) NO
- if records >= 1 and =< 6 print header and up to 6 records then a page break YES
- if records >= 7 and =< 12 same as previous Of no concern, as the 7th will create the second page anyhow.
- if records >= 13 and =< 18 then what? Doesn't matter as there are never more than 18 child records (I checked) only >=7 is important
- anything not covered... NO. Just note that the header doesn't matter, it will always print even if zero records.[/QUOTE]

The important thing is that I always end up with two pages per parent and child records so that I can print double sided and end up with one complete form per whole record and not have any records print back to back. Either I end up with the 1st page having the parent record and 0-6 children and a blank 2nd page OR I end up with 6 children on the first page and 7+ records on the second page.

Check the attachment. I faked some results.

Does that clear it up?

 

[Deleted member 147267]

OK, watching ball game so that's it for just a bit. I think i have it.

 

[Deleted member 147267]

Best I think I can do is what's attached. In some? cases there will be an extra blank page at the end. Possibly not if there isn't a couple of stray records on a page, otherwise, with the data provided I get one. If there's a way to detect that the previous page is blank I don't know it. Can't be as simple as counting records in a section, because the blank page is achieved by putting your secondary page break in the section footer (which you didn't create but I added). Thus a prior section does in fact have records but the prior page does not always. Hope this is good enough for now.


EDIT - Forgot to mention that you really should turn on "require variable declaration" in the vb editor. You have Option Compare setting but not Option Explicit. A mis-spelled name can have you spinning your wheels for quite some time while trying to figure out the issue. As someone once wiser than me said "if you don't require variable declaration, you deserve what you get".