Re: Dynamically change subreport?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Philip Herlihy wrote:
Instinct is starting to tell me that I'm trying to do something over-complicated, so, if you'll bear with me, I'll explain what I want and what I've come up with so far.

I have a home-made billing system in Access 2003. I create invoices for my customers from a form, in which I select the customer using a Combo box, and view un-invoiced items in two subforms. Then I click a command button on this main form, and that fires off a report, which is based on a query which refers back to the customer combo box on the form.

That report has two (unlinked) subreports, whose record sources are queries which also refer back to controls on the original form, including the customer.

After studying Northwind.mdb (after seeing a prompt to do that in this group) I now know I could have organised these using the Link Child Fields in the properties of the subform/report controls, but at present it's the main Invoices form which ties everything together, and it all works!

Now I have a customer who wants his invoices grouped by Site - most customers don't. Instinct tells me I don't want to maintain two completely different families of reports, but that I should re-use anything I can, and include a Check-box "By Site" on the main Invoices form.

After rejecting the idea of changing grouping dynamically (?) I've come up with the possibility of creating new subreports with the added grouping and controls, so I'd want to be able to assign them based on the check-box setting. I find you can't set the subreport control's SourceObject in any of the parent report's event handlers, so I'd have to do it in the OnClick event handler for the button that fires off the report.

Here's some code I found (thanks to Steve Arbaugh) which seems to do this:

<quote>
The only way to do that is open the report in design mode and then change the Source Object property of the subreport control. As in:

On Error resume next
Docmd.Echo False
Docmd.OpenReport "MyReport", acViewDesign
Reports![My Report]!MySubreportControl.SourceObject = "some new subreport"
Docmd.Close acReport, "MyReport", acSaveTrue
Docmd.Echo True
>
> Steve Arbaugh
> </quote>
>
> However, this is truly "self-modifying" code, which rings alarm bells!
>
> Am I missing something simpler? Is it possible to include the extra grouping and make it invisible in the majority of cases, or is this ok?
>
> Grateful for any advice!
>
> Phil, London

(I see I shouldn't have left in the two hyphens in Steve's signature!)


No comments yet! I needed to get on with this, so I've duplicated the parent form and made modified versions of the two relevant subforms, which of course will increase the work I have to do (if I remember!) when the time comes to make overall changes.

I now have a check-box on the master Invoice form, and the "go" button's event handler uses this to determine which report to run: in one the subreports are grouped on location, and in the other they are grouped by date (monthly), both with totals summed in the group footer. It all works.

Comments still very welcome.

Phil
.

Quantcast