Subreport print formatting help needed
I am creating a report for a dot-matrix printer that will be printed on preprinted multi-layer forms - each page is 8.5" x 11". The report has a sub-report consisting of a randomly long list of items (5 columns) that is about 2" high roughly centered in the right half of the page. The left side of the page has customer info and can be repeated on each page (or only on the first page.)
Currently, I have the sub-report printing items in the correct place, but if the list of items is longer than the space allowed (9 rows at Courier 10), the remaining rows are clipped off, not continued in the same spot on the next page. The sub-report is in the detail section of the main report, the rows of items are listed in the details section of the sub-report.
So the main question is: How do I get the sub report to create additional pages and print in the same spot on these if there are more items than will fit? If I have to do this in VBA, that is OK.
Currently, I have the sub-report printing items in the correct place, but if the list of items is longer than the space allowed (9 rows at Courier 10), the remaining rows are clipped off, not continued in the same spot on the next page. The sub-report is in the detail section of the main report, the rows of items are listed in the details section of the sub-report.
So the main question is: How do I get the sub report to create additional pages and print in the same spot on these if there are more items than will fit? If I have to do this in VBA, that is OK.
ASKER
Hi Jeff
Both sections can grow are set to yes. In the detail section, this simply makes the detail listing too long for the area - it doesn't jump to the next page.
I am on iPad this morning so do not have means to make picture, but the layout is on one or more 8.5 x11 sheets with the detail section only allowed to print in a 3.5 x 2" box about halfway down the right side of the page. If more room is needed to print additional info, this same size and positioned box must be used on subsequent pages only. On at least page 1, main report data will be to the left of this box.
Both sections can grow are set to yes. In the detail section, this simply makes the detail listing too long for the area - it doesn't jump to the next page.
I am on iPad this morning so do not have means to make picture, but the layout is on one or more 8.5 x11 sheets with the detail section only allowed to print in a 3.5 x 2" box about halfway down the right side of the page. If more room is needed to print additional info, this same size and positioned box must be used on subsequent pages only. On at least page 1, main report data will be to the left of this box.
Then perhaps it would be even better if you posted a stripped down version of this db and posted a graphic of the exact output you want...
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
ASKER
Here it is. If you open the rptBOL, it shows the data for the preprinted form that I am trying to get correct. I have added a box around the subreport that should show more lines on additional pages. At this point, I can only get it to truncate the list of Items, or have the box expand to fit them all. I need the report to repeat this page with the additional items. The data to the left should not repeat if possible.
forEE.accdb
forEE.accdb
Not sure, but I think this has to do with the fact that the sub report is on the side of the data.
The Can grow Prop seems to work best if the sub report is "In Line" with the data.
So if you simply make the Subreport as tall as all the fields in the detail section, it should display all the records.
(Finally note that your design seems like it may exceed the length of the page itself, this may also be a factor.)
forEE.accdb
The Can grow Prop seems to work best if the sub report is "In Line" with the data.
So if you simply make the Subreport as tall as all the fields in the detail section, it should display all the records.
(Finally note that your design seems like it may exceed the length of the page itself, this may also be a factor.)
forEE.accdb
ASKER
Hi,
Thanks for your input, but this is for a pre-printed form, and thus I am not able to adjust the formatting of the results. The box has to be to the right with header style info to the left. The subsequent pages can omit the header info on the left, but the detail box has to be in the same place each time.
Thanks for your input, but this is for a pre-printed form, and thus I am not able to adjust the formatting of the results. The box has to be to the right with header style info to the left. The subsequent pages can omit the header info on the left, but the detail box has to be in the same place each time.
Then AFAICT, (and if I am understanding your request), this is this is not possible directly.
That being said, ..there is alwaysa way to do anything...
How much resources you can afford to throw at the problem is another story.
I personally don't have the time for something like this currently...
But perhaps I am missing something simple.
I'll see if I can get another Expert in on this...
JeffCoachman
That being said, ..there is alwaysa way to do anything...
How much resources you can afford to throw at the problem is another story.
I personally don't have the time for something like this currently...
But perhaps I am missing something simple.
I'll see if I can get another Expert in on this...
JeffCoachman
ASKER
Thanks Jeff
I guess I will have to write a custom report completely with VBA. Do you know of any sample code on here to get me started?
I guess I will have to write a custom report completely with VBA. Do you know of any sample code on here to get me started?
I can't open the database (stuck with 2007 at the moment). From what I read, you need to switch from the report with subreport design scheme to the grouped report based on linked tables scheme — which is what I always recommend when its possible.
There are no good ways for a subreport to manipulate its parent report, and only limited ways to do the opposite. You have much more possibilities when working on a single report object.
If you want me to look into it, please save your database using an older version format.
(°v°)
There are no good ways for a subreport to manipulate its parent report, and only limited ways to do the opposite. You have much more possibilities when working on a single report object.
If you want me to look into it, please save your database using an older version format.
(°v°)
ASKER
Thanks for taking the time. I have created it in mdb format.
I am not sure I understand the difference concerning grouped query as related to the layout format, but I used the wizard to create the basic report and pointed it to the two related queries for the data source.
forEE.accdb
I am not sure I understand the difference concerning grouped query as related to the layout format, but I used the wizard to create the basic report and pointed it to the two related queries for the data source.
forEE.accdb
You didn't attach the *.mdb version... — (°v°)
ASKER
Oops, sorry :( Here is the correct file
Database1.mdb
Database1.mdb
Hello,
Thanks for the database, it's much clearer now.
I really see only one solution: use the page footer to force the lines from the subreport to go to the second page. I tried this:
1) I made the page footer higher by about four inches,
2) and moved the controls below the subreport to the page footer.
3) I reduced the detail section to end just below the subreport,
4) which I set to “can grow: yes”.
The subreport will now grow, until it meets the top of the page footer, at which point a new page is generated, continuing the detail section (which grows as well). All the fields in the page footer are repeated (we can turn that off if needed) but the fields to the left of the subreport are not (and there is no simply way I can think of to repeat them).
Try it to see if you can work with that. Hint: paint each section with a different background colour. It makes it much easier to understand what's going on.
(°v°)
Thanks for the database, it's much clearer now.
I really see only one solution: use the page footer to force the lines from the subreport to go to the second page. I tried this:
1) I made the page footer higher by about four inches,
2) and moved the controls below the subreport to the page footer.
3) I reduced the detail section to end just below the subreport,
4) which I set to “can grow: yes”.
The subreport will now grow, until it meets the top of the page footer, at which point a new page is generated, continuing the detail section (which grows as well). All the fields in the page footer are repeated (we can turn that off if needed) but the fields to the left of the subreport are not (and there is no simply way I can think of to repeat them).
Try it to see if you can work with that. Hint: paint each section with a different background colour. It makes it much easier to understand what's going on.
(°v°)
ASKER
Cool, any chance of posting the updated mdb file so I can see what you did?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A picture is worth a thousand words! Thanks for the help - I think that this will work just fine!
Rich
Rich
Do you have the Sub Report's Can Grow property set to: Yes
Without a graphic, it is not clear what you mean by:
"sub report to create additional pages and print in the same spot"
...normally a subreport will continue on the next page...
Another option I like to mention is to try using a "Grouped" Report.
This creates a report that is similar to a Main/subreport, but without all the overhead.
It is also sometimes easier to control the "grouped" (sub) records.
The Report wizard will walk you through the steps to create a Grouped report.
;-)
JeffCoachman