Subrpt forces new pg, main rpt pg doesn't prt new pg

[VicM]

I'm, generating a report containing OLE pics of state forms. I've overlayed text boxes on the OLE object to fill in the form.

The OLE state form I use in DETAIL section of main report contains an overlayed subreport object which has only text boxes in its DETAIL section. The subreport record source cycles thru the required records. Each OLE state form in main report DETAIL section has room for 2 records that the subreport generates.

When there are no more than 2 records in the subreport the entire report functions flawlessly.

But here's the rub: when there are more than 2 records in the subreport, the subreport forces another page (as it should be), but the main report DETAIL page is not repeated. Only the data, from the 3rd subreport record on, prints on pages which do not show the OLE object from the main report DETAIL section.

And one other quirk is page numbers. When there are less than 3 subreport records, all report page numbers print correctly. But when the aforementioned problem occurs, altho the page number sequencing is correct, the first main report DETAIL page containing the first 2 subreport records, does not print a page number at all.

That is to say the next page containing only the 3rd and 4th subreport records (with no background OLE object) prints the correct page number in its sequence.

I have tried all combinations of forcing new pages, moving the entire main DETAIL page into a GROUP HEADING where I can indicate Repeat Page; keeping groups together or not; causing sections and subreport object on the main report to grow or not; and just about anything else that might have a hint of working, but all to no avail.

This problem has be haunting me for several weeks and I'm ready to throw in the towel. So if any kind folks understand the problem and can suggest something I've yet to try, I'm all ears ... er, eyes.

Thanks,
Vic

P.S. Have a GREAT New Year!

 

[JerichoJ]

Vic,

Ok, I think I understand your problem and I might be able to help. First of all, are you very good with VBA coding? This report is going to require some serious code to get the results you desire. I will outline my suggested solution and then we can discuss actual code later after you tell me what you think.

Since the OLE overlay is just an image, Access is NEVER going to repeat that image in the way you want using the default reporting methods. Access has a .Picture property and an associated picture tiling property for forms and reports, but unfortunately they apply to the entire form or report. If only you could specify a separate picture for the background of each section of a form or report, you might have a chance. But, don't lose hope yet because I have a way to get around this problem.

Ok, you need to keep your report working the way you originally described it, meaning the detail section of the main form has the OLE control, and the subreport that you overlayed on it contains the text boxes with your data.

Now, what you are going to have to do is manipulate the data in your recordsource (query) to add additional fields and information to get Access to produce the report the way you desire. For example, let's say that your recordsource is producing the following data for the main report:

CustNum, CustName, Addr1, Addr2, City, State, Zip
2501, "Acme Sales", "123 Main", "", "MyTown", "AL", 66292

Then your subreport is producing this data:

CustNum, InvoiceDate, InvoiceNum, Amount
2501, 12/29/02, 991122, $59.72
2501, 12/30/02, 991123, $104.29
2501, 12/31/02, 991124, $153.48

This example is showing that CustNum is the link between Main and sub-Reports. What you need to do is add one more field to the output of both of your queries that matches a CustNum to ONLY 2 records in the sub report. Such as:

CustNum, GroupNum, CustName, Addr1, Addr2, City, State, Zip
2501, 1, "Acme Sales", "123 Main", "", "MyTown", "AL", 66292
2501, 2, "Acme Sales", "123 Main", "", "MyTown", "AL", 66292

Then your subreport is producing this data:

CustNum, GroupNum, InvoiceDate, InvoiceNum, Amount
2501, 1, 12/29/02, 991122, $59.72
2501, 1, 12/30/02, 991123, $104.29
2501, 2, 12/31/02, 991124, $153.48

Then, your link fields between the sub and the main report would be CustNum and GroupNum, and you would group the main report on both CustNum and GroupNum as well.

The difficult part is getting that GroupNum generated. This is where we need code, and a Temp table. What I typically do is create a temp table with all of the fields that the query is currently outputing, plus the new GroupNum field. Then, I write the code to run this report by doing the following:

1. Empty the temp table
2. set a recordset in code to the query results,
3. loop through each record checking to see if the
current CustNum is equal to the previous CustNum, if so, increment a counter.
4. When the counter reaches my maximum number of records per group, (in your case, 2) increment a group number counter.
5. As I process each record, the last step is to write the record, along with its Group Number to the temp table.
6. After all records have been processed, open the report.

BTW, the recordsource of the report is changed to point to the temp table instead of the original query.

Let me know if any of this makes sense to you and any questions you might have. This is the only way that you are going to get the report to print the way you desire. It is some work, but if you really need to make it work, it is worth it.

 

[VicM]

JerricoJ:

I didn't know about Access not reproducing the picture on a second page. Thanks for educating me.

I do program significant amounts of VBA code, so I do understand your suggestion. I'll have to work it a little more as there are 3 linked fields between parent and child. That's because I have a few more levels of grouping which trigger a new set of pages for the next group.

All tolled, there are a minimum of 3 pages per report. All of them have an OLE template overlayed with dynamic data. The only page which can repeat is the middle page, the one I described at start of this thread. Changes to any of the 3 grouping fields triggers the start of another full report, resetting the page numbering to 1.

The 4th level (a shipped to facility) is what is entered onto the detail overlay, it having room for 2 facilities. There is no grouping for this 4th level since multiple facilities can fall under the previous 3 groupings. When our shipments for the year exceed 2 'shipped to' facilites in each combination of the 3 groups, that is when the main detail section needs to be repeated.

I'm not sure I'm making this very clear, and I apologize. I'm afraid I'm going to make this long winded.

The 3 groupings are: 1) a waste code; 2) a waste description; and 3) a physical state, e.g., solid, liquid, gas. Each combination of those groups can then have multiple 'shipped to' facilities.

A waste code can have multiple descriptions and a change in that description means a new report, as do changes in the other 2 groups. Therefore my group headers are: WasteCode (empty), WasteDescr (empty), and PhysState. The PhysState header contains the first page of the report, listing our company info and the info for the 3 groupings, i.e., waste code, waste descr, and physical state.

The 2nd page of the report is located, as mentioned, in the main report's detail section and contains the page where the facilities are to be listed.

The 3rd page of the report resides in the PhysState footer, and is just a signature page. Each set of 3 or more pages are numbered starting at 1.

So if I understand correctly, you're suggesting I insert the GrpNum header after my PhysState header which should then cause the detail section to repeat on change of the GrpNum.

Sounds promising. I will begin working on the code.

Thanks
Vic