Solved

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

Posted on 2009-04-11
15
2,581 Views
Last Modified: 2012-05-06
I've created a report in Access which is my picking list. This report has two imbedded sub reports. The first sub report is the stock items lines and the second is the free text item weight lines.

The sub reports are in the detail section. When I print the report print fine with a few lines of stock, but with more than six items of stock the report should then print on the next page. I've tried to add the goup and sort of the document no, but this does not help. The report prints over two pages but throws out the document layout.

How can I make this layput print properly and make the picking note print on two pages
SELECT Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentTypeID, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentStatusID, Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode1, Invent_Tbl_Copy_dbo_SLCustomerAccount.CustomerAccountNumber, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentDate, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentNo, Invent_Tbl_Copy_dbo_SOPOrderReturn.SOPOrderReturnID, Invent_Tbl_Copy_dbo_SOPDocDelAddress.PostalName, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine1, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine2, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine3, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine4, Invent_Tbl_Copy_dbo_SOPDocDelAddress.PostCode, Invent_Tbl_Copy_dbo_SOPDocDelAddress.Contact, Invent_Tbl_Copy_dbo_SOPDocDelAddress.TelephoneNo, Invent_Tbl_Copy_dbo_SOPDocDelAddress.EmailAddress, Invent_Tbl_Copy_dbo_SOPOrderReturn.CustomerDocumentNo, Sum([AllocatedQuantity]*[Weight]) AS TotalWeight, Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode3

FROM ((Invent_Tbl_Copy_dbo_SLCustomerAccount INNER JOIN (Invent_Tbl_Copy_dbo_SOPOrderReturn INNER JOIN Invent_Tbl_Copy_dbo_SOPDocDelAddress ON Invent_Tbl_Copy_dbo_SOPOrderReturn.SOPOrderReturnID = Invent_Tbl_Copy_dbo_SOPDocDelAddress.SOPOrderReturnID) ON Invent_Tbl_Copy_dbo_SLCustomerAccount.SLCustomerAccountID = Invent_Tbl_Copy_dbo_SOPOrderReturn.CustomerID) INNER JOIN Invent_Tbl_Copy_dbo_SOPOrderReturnLine ON Invent_Tbl_Copy_dbo_SOPOrderReturn.SOPOrderReturnID = Invent_Tbl_Copy_dbo_SOPOrderReturnLine.SOPOrderReturnID) INNER JOIN Invent_Tbl_Copy_dbo_StockItem ON Invent_Tbl_Copy_dbo_SOPOrderReturnLine.ItemCode = Invent_Tbl_Copy_dbo_StockItem.Code

GROUP BY Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentTypeID, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentStatusID, Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode1, Invent_Tbl_Copy_dbo_SLCustomerAccount.CustomerAccountNumber, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentDate, Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentNo, Invent_Tbl_Copy_dbo_SOPOrderReturn.SOPOrderReturnID, Invent_Tbl_Copy_dbo_SOPDocDelAddress.PostalName, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine1, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine2, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine3, Invent_Tbl_Copy_dbo_SOPDocDelAddress.AddressLine4, Invent_Tbl_Copy_dbo_SOPDocDelAddress.PostCode, Invent_Tbl_Copy_dbo_SOPDocDelAddress.Contact, Invent_Tbl_Copy_dbo_SOPDocDelAddress.TelephoneNo, Invent_Tbl_Copy_dbo_SOPDocDelAddress.EmailAddress, Invent_Tbl_Copy_dbo_SOPOrderReturn.CustomerDocumentNo, Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode3

HAVING (((Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentTypeID)="0") AND ((Invent_Tbl_Copy_dbo_SOPOrderReturn.DocumentStatusID)="0") AND ((Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode1)="INTERNET" Or (Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode1)="MAILORDER") AND ((Invent_Tbl_Copy_dbo_SOPOrderReturn.AnalysisCode3)=[Forms]![Invent_Frm_PickingListMenu]![Combo24]));

Open in new window

PickingList.JPG
0
Comment
Question by:Invent121
  • 8
  • 7
15 Comments
 
LVL 57
ID: 24122511
<<The report prints over two pages but throws out the document layout.>>
  Not quite sure what you mean and what exactly the problem is.  What should be happening that's not?
JimD.
0
 

Author Comment

by:Invent121
ID: 24128528
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.
0
 
LVL 57
ID: 24129569
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.
0
 

Author Comment

by:Invent121
ID: 24129583
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.
0
 

Author Comment

by:Invent121
ID: 24158257
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.

RW-Picking-List-V2.pdf
0
 
LVL 57
ID: 24158694
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.
0
 

Author Comment

by:Invent121
ID: 24175281
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
RW-PickingList-3.jpg
RW-Picking-List-3.pdf
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57
ID: 24183700
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.
0
 

Author Comment

by:Invent121
ID: 24185770
This works better but only prints the first document and not all three
0
 
LVL 57
ID: 24185869
<<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.
0
 

Author Comment

by:Invent121
ID: 24185904
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.
0
 
LVL 57
ID: 24186004
<<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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 24187114
BTW, the other thing you could do is move those fields back to the Document header and then set the repeat section property to try.  That would also give you those headings on each page.
JimD.
0
 

Author Comment

by:Invent121
ID: 24278048
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
0
 

Author Closing Comment

by:Invent121
ID: 31569183

Was this comment helpful? Yes No Invent121: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
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now