How to make Access 2007 insert a page break in my report

If the fist order has say 7 item lines, and the layout supports only 5 on the first page then the remainding 3 on the next, it does not see the footer properly so the last item on page 1 goes over the template, then the rest prints on page 2 put further down the page and then throws out page 3 for order 2 and page 4 for order 3.

Its as though it can't see where the footer should be and then create a new page with the same header details with the remainding stock items.

I was not sure if it was because I have two imbeded subreports, but without these I can't get the right criteria on the page. I'm trying to covert the document to pdf, but the document automation application is not liking it.

I think it would be best if you would preview the report and take a screen shot or upload a small DB with just the report if possible.

I tink your problem is witht he sub-reports because getting headings to repeat is one of the fundamental problems with them.  Since they are actually separate reports, they don't see any of the main report's events or settings, so they have no idea on how to put things like headings up.

 There are various ways around that, but I want to make sure I thoroughly understand the problem first before I suggest something.

JimD.

I've exported the database but not sure Im allowed to updated this as it contains loads of data. I'll try the screen shot method.

Attached picking list.

From the attached file you'll see how the first 7 items should be on the first page and then send the 8th item onto the next page. The following pages are then thrown out on the print out.

OK I now understand and can see what the problem is.

With a pre-printed form, your going to find this much easier to control if you pull everything up to the main report, which is what I would do.  

 In general, sub reports are really only useful if there is related data to something, but it is fundamentally a different report, with different headings, groups, footers, etc.  For example, an inventory report and for each item, you wanted to show the sales data by region.

 Unlike forms where you deal with a one to many relationship (ie. Order Header to Order line items) with a main/subform combination, with reports your generally better off to flatten out the data and handle everything in the main report.

So first step is to start a new query.  What you want is your order header, line items, and the inventory table (if the weights come from there).  One you've constructed that qeury, execute it.  Order header info will be repeated for every record, but that doesn't matter.  You should end up with one record for each line item.

 OK, now in the report, dump the sub reports, change the record source to the new query, and set your sorting and grouping fields.   Your header will remain the same.  

For the detail section, open your sub reports and copy/paste the controls into the detail section.  Size the section appropriately.  Now set the bottom margin to an 1 1/2".

 Detail will now print down to the bottom margin and format onto another page when you get more then 5 lines worth.  You'll find it easy to work with the pick slip footer as well.  Again, it will honor the bottom margin on your part with no extra work.  Just make sure you don't have extra vertical white space in the sections.

JimD.

Hi Jim,

Thanks for the info, I think where nearly there.

I've updated the report with the new querey so I don't need sub reports. I've set the margin as you said. The first page prints fine, but the second page doesn't print the header again and then pushes the 2second page out. I tried moving the header fields into the report header, but this made no difference.

Jon

Jon,

Move it to the page header. Since you will never have more then one document on any given page there is no problem in putting it there and it will give you the headings on every page.

The Report Header BTW only prints once, which is at the start of the report

JimD.

This works better but only prints the first document and not all three

<<This works better but only prints the first document and not all three>>

Note sure I follow, you get headings on all three, but it's always for the first document or you don't get any headings on anything but the first page?

JimD.

The report is made up of three picking lists/documents.
I get headings on the first and second page for document 1, document 2 and 3 does not print.

<<The report is made up of three picking lists/documents.
I get headings on the first and second page for document 1, document 2 and 3 does not print.>>

 I can't think of anything that would cause that just by moving the controls to the page heading section.  Something else is going on.

 Open the the query that the report is based on at the point that the report would run (even just while it's on the screen) and make sure all the data is still there.  Also, you should not have removed the grouping on the document as you still need the footer and it defined to trigger the page break.  From the looks of things, you still have it, but I thought I'd mention it anyway.

JimD.

Hi Jim,

I added the group on document no and the necessary sorting.. The header info which needed repeating was added into the page header. The weight totals were added into the document footer.

I then had to add Force new Page, after section in the Document No footer Property Sheet.

Thanks for your help

Jon