Dynamically change subreport?

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

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

Philip Herlihy wrote:
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

If you're interested, this is solved in Marshall Barton's post "
Dynamic grouping in reports?" 13/02/2009 20:38 in
microsoft.public.access.reports. The trick is to set the ControlSource
of the relevant GroupLevel, and also modify the properties (Caption,
Control Source) of the header/footer's controls (which are used for
displaying totals).

Phil

After scouring the web to find a solution I only kept coming up with these kinds of answers i.e. open in Design View and then modify.

As usual Microsoft only go half way with their OO approach (love to have a dig).

My simple idea is to have one report defined that has a standard bar chart control and it gets instantiated multiple times. For each, a slightly different record source would be used. Of course, as soon as you instantiate (which can be done, as with forms, after setting the report's Has Module property) it goes into Print Preview mode (even though invisible) and very few properties can be changed.

It makes me yearn for the days of PowerBuilder with DataWindows! Maybe I should have a go in .net anyway... Or maybe someone has an answer...

Sample code:

Dim g2 As New Report_pr_test

g2.Graph0.RowSource = Replace(g2.Graph0.RowSource, "pr_1", "pr_1test")
g2.Visible = True




Philip Herlihy wrote:

Dynamically change subreport?
14-Feb-09

Philip Herlihy wrote:

If you're interested, this is solved in Marshall Barton's post "
Dynamic grouping in reports?" 13/02/2009 20:38 in
microsoft.public.access.reports. The trick is to set the ControlSource
of the relevant GroupLevel, and also modify the properties (Caption,
Control Source) of the header/footer's controls (which are used for
displaying totals).

Phil

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials...tom-pagin.aspx