I'm not sure if this is possible, but I need to be able to have the system generate a continuation sheet if items on a report won't fit into a pre-defined area. Here's my problem: I have a database which generates a Despatch Note. The Despatch Note is created as an Access report, and is populated with basic job details (e.g. customer, shipping address, job reference number etc). There is then a sub form placed on the report which draws in a list of items to be included on the Despatch Note. The problem is that when there are more than 6 items on the list they don't fit onto the Despatch Note. Sometimes there needs to be up to 10 items. The font size is already on the small size (8) so I can't really reduce this any further. The form itself has to conform to a set design, so I can't even re-organise the layout. What I really need is to have the system generate a continuation sheet with the extra items on it if the number of items exceeds 6, but I haven't a clue how to go about this, or, indeed, if it's even possible to do such a thing in Access. HELP!
You might take a look at how the Force New Page, New Row Or Col, and Keep Together options work for each of the sections of the of the reports and subreports. I glanced through the Microsoft help file on all three subjects and I think that might be how you would set that up, not 100% sure though, I dont have anything thing that I would use to test that with just as of yet.
If the continuation sheet will look just like the first sheet, that should be no problem. The way to get only the first 6 to print in the sub report is to have everything that should be at the bottom of the page, after the subreport area, should be in the page footer. That will keep the subreport from printing more than the page can handle.
If the continuation sheet will be different than the first page, let us know and we can help with that possibility too.
Thanks for the suggestions. I tried setting the Force New Page property for the sub report and putting the report content below the sub report into the footer. Unfortunately all that happened was that the report generated 10 separate pages, all identical and each one attempting to cram all 10 items onto each page. I read the help files on New Row or Col and Keep Together. To be honest, I couldn't really see the relevance of New Row or Col, but Keep Together seemed as if it might do the trick. However, when I tried setting the Keep Together property for the fields in question to "Whole Group" it still didn't seem to have an effect on the result. I'm clearly still going wrong somewhere. I've attached 3 screenshots to make my predicament clearer to see. ADDEDIT shows the screen for inputting the despatch note data initially (the user has to click the "Generate Despatch Note" button to launch the report), NOTEDESIGN shows a portion of the Despatch Note design view, and NOTEPRINT shows a portion of the despatch note as printed. Any help or suggestions would be gratefully appreciated!!
I would get rid of the report and page headers and footers because they are not being used. You do not need Force New Page, or anything else that has been suggested. This really is a VERY straight forward solution. By defining what you want at the top of the page, and at the bottom of the page, and leaving only enough room between those two portions of a page for no more than 6 lines of detail from your subreport, it should work just as you want. The seventh line should be on the second page with the page header at the top and the page footer at the bottom. Nothing fancy at all. Just the page header and page footer in the main report with the subreport within the detail section of the main report.
Please let us know how this works for you.
Thanks Vic - this works a treat. Funny how the simplest solutions are usually the best! One little niggle though - I don't know why this is, but if there are less than 6 items (i.e. they all fit on one page) it still generates 2 pages. The 2nd page is blank. I wondered if it was something to do with how the footer was set up. You can get round it by selecting to just print "page 1" in the print dialog, so it's not a big deal.
I would suggest looking at the combined page height from the page header, detail section, and page footer to be sure that it is less than the page height defined in the page size for the printer you have choosen. And, that the page is not too wide for the size of page you have defined for the printer.
I checked these dimensions and made some minor adjustments. It's made no difference, but I've noticed that I'm getting the same number of reports as there are items on the sub report. If I reduce the number of items from 10 to 7, for example, I get 7 reports. 3 items on the report gives 3 copies, and so on.
Great catch! Your main report is bound to the same record source as the subreport. So, if you have 7 records to print in the sub report, the main report will also print 7 times. I don't know what should be the record source for the main report, but you want it to be only one record is what it looks like from here.
There are two tables linked in a one-to-many relationship, tblDespatchNote and tblDespatchNoteItem. The main report is bound to tblDespatchNote and the subreport bound to tblDespatchNoteItem. Or at least I thought that's what I'd set up! If the tables are related in this way, why does it print one report for each record on the "many" side of the relationship?
Aha! Yes - the record source is indeed a query. I didn't know that the 1:N relationship set up at table level didn't carry forward when used as a source for a query. I need the report to be generated from a query because how else will it know which record to pull through into the report? As I said in an earlier post it's not a huge deal because the user can simply choose to print page 1, but...it would kinda be nice to know, if you see what I mean!
Is the query made up of both tables? If so, you should be able to change the query to be only the main table. I can not say that for sure, because I don't know any of the details of your situationl. But, in 99.44% of the cases, (and all I have ever seen) the main report should only have the main table as it's record source, or the query only be using it. You can have more tables involved in the query, just should not need the detail table that is being used with the subreport.
Yes, 2 separate queries. You only need master type records for the master portion of the report. The detail records will be on the subreport, so that is where you will need a query for the detail records. When we put records into a query that are not needed on a report is when the report does things we don't like or need.
So, two queries are in order here.