[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-04-11
15
Medium Priority
?
2,591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 58
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 58
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 58
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
 
LVL 58
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 58
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 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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