Invent121
asked on
How to make Access 2007 insert a page break in my report
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
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]));
ASKER
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.
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 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.
ASKER
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.
ASKER
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
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
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.
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.
ASKER
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
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
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.
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.
ASKER
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.
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.
ASKER
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 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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
Not quite sure what you mean and what exactly the problem is. What should be happening that's not?
JimD.