I once told a client how easy it is to make reports in Access. Since then, he's developed a savant-like ability to ask for reports that I find nearly impossible to do quickly and easily. Here is yet another example.
There's a report listing sales by inventory category. At the end of this report, there is a recap that groups and totals the categories by Group and sub-group. There are 4 sub-groups in 2 broad groups, so this recap shows 6 lines (subgroup, subgroup, group total, subgroup, subgroup and group total again).
This recap is a separate report that I have inserted as a sub-report on the main report. Now, he wants me to repeat the recap a number of times. Each recap will include on a given sales territory. So there will be the company-wide territory followed by Territory 1's recap, then Territory 2's recap and so on.
I thought, no problem, I'll just insert the same sub-report multiple times and use the .tag property to identify the territory limits for the report, which I'll set up in the subreport's Report.format event. (same query is used in all cases, the print event does queries to calculate values for each mtd/ytd column)
The problem is that Access 2003 does not allow the same report to be inserted as a sub-report more than 1 time. It sort of goes through the motions of letting you do that, but the report is not actually there. The sub-report object is there but the report within that object has no properties or objects.
I'm considering a wretched idea, copying and pasting the report I use as a subreport and just changing the name 5 times for the 5 territories I need, and then hard coding a territory limit in each before using them as subreports.
Any better ideas out there?
Thanks,
Jim