Hello,

I have a handful of reports that I am trying to manipulate using VBA.  
Depending on whatever they choose in a "currency" dropdown will determine 
what data the report will be showing.  This is my question.  Is there a way 
through VBA, depending on the value chosen in the pull down on the main form, 
to bring up a report but manipulate it so it looks at a certain query, 
changes its grouping levels, etc based on that choice in the dropdown?  This 
would save me a TON of work, so I don't have to create new reports looking at 
different queries and have to put grouping levels in each, etc, but can just 
use that single report with some VBA manipulation.

Anyone would could help me with this would be GREATLY appreciated!

Thank you very much in advance,
MN

MacNut2004 wrote:
>I have a handful of reports that I am trying to manipulate using VBA.  
>Depending on whatever they choose in a "currency" dropdown will determine 
>what data the report will be showing.  This is my question.  Is there a way 
>through VBA, depending on the value chosen in the pull down on the main form, 
>to bring up a report but manipulate it so it looks at a certain query, 
>changes its grouping levels, etc based on that choice in the dropdown?  This 
>would save me a TON of work, so I don't have to create new reports looking at 
>different queries and have to put grouping levels in each, etc, but can just 
>use that single report with some VBA manipulation.


You can do that kind of thing in the report's Open event
procedure.  The reports record source can be set and
**existing** group levels can be modified.  The code usually
looks something like:

Select Case Forms!yourform.thecombobox
Case "rptA"	'or whatever value
	Me.RecordSource = "QueryA"
	Me.GroupLevel(0).ControlSource = "field1"
	Me.GroupLevel(1).ControlSource = "field2"
		'effectively "disable" level 3
	Me.GroupLevel(2).ControlSource = "=1"
	Me.Section(9).Visible = False		'header
	Me.Section(10).Visible = False		'footer
	Me.textboxX.Visible = False
	 . . .
Case "rptB"
	 . . .

-- 
Marsh
MVP [MS Access]

Marshall,

Thank you very very much! I will try this and hopefully get the result I 
need!!

Thanks again!
MN

"Marshall Barton" wrote:

> MacNut2004 wrote:
> >I have a handful of reports that I am trying to manipulate using VBA.  
> >Depending on whatever they choose in a "currency" dropdown will determine 
> >what data the report will be showing.  This is my question.  Is there a way 
> >through VBA, depending on the value chosen in the pull down on the main form, 
> >to bring up a report but manipulate it so it looks at a certain query, 
> >changes its grouping levels, etc based on that choice in the dropdown?  This 
> >would save me a TON of work, so I don't have to create new reports looking at 
> >different queries and have to put grouping levels in each, etc, but can just 
> >use that single report with some VBA manipulation.
> 
> 
> You can do that kind of thing in the report's Open event
> procedure.  The reports record source can be set and
> **existing** group levels can be modified.  The code usually
> looks something like:
> 
> Select Case Forms!yourform.thecombobox
> Case "rptA"	'or whatever value
> 	Me.RecordSource = "QueryA"
> 	Me.GroupLevel(0).ControlSource = "field1"
> 	Me.GroupLevel(1).ControlSource = "field2"
> 		'effectively "disable" level 3
> 	Me.GroupLevel(2).ControlSource = "=1"
> 	Me.Section(9).Visible = False		'header
> 	Me.Section(10).Visible = False		'footer
> 	Me.textboxX.Visible = False
> 	 . . .
> Case "rptB"
> 	 . . .
> 
> -- 
> Marsh
> MVP [MS Access]
> 

Marshall,

Just thought of something. I also have subreports in my reports...can i 
change the recordsource for those as well?  If so, can it be on the onopen 
event of the MAIN report or does it have to be in each subreport as well?

Thank you!
MN

"Marshall Barton" wrote:

> MacNut2004 wrote:
> >I have a handful of reports that I am trying to manipulate using VBA.  
> >Depending on whatever they choose in a "currency" dropdown will determine 
> >what data the report will be showing.  This is my question.  Is there a way 
> >through VBA, depending on the value chosen in the pull down on the main form, 
> >to bring up a report but manipulate it so it looks at a certain query, 
> >changes its grouping levels, etc based on that choice in the dropdown?  This 
> >would save me a TON of work, so I don't have to create new reports looking at 
> >different queries and have to put grouping levels in each, etc, but can just 
> >use that single report with some VBA manipulation.
> 
> 
> You can do that kind of thing in the report's Open event
> procedure.  The reports record source can be set and
> **existing** group levels can be modified.  The code usually
> looks something like:
> 
> Select Case Forms!yourform.thecombobox
> Case "rptA"	'or whatever value
> 	Me.RecordSource = "QueryA"
> 	Me.GroupLevel(0).ControlSource = "field1"
> 	Me.GroupLevel(1).ControlSource = "field2"
> 		'effectively "disable" level 3
> 	Me.GroupLevel(2).ControlSource = "=1"
> 	Me.Section(9).Visible = False		'header
> 	Me.Section(10).Visible = False		'footer
> 	Me.textboxX.Visible = False
> 	 . . .
> Case "rptB"
> 	 . . .
> 
> -- 
> Marsh
> MVP [MS Access]
> 

You can set a subreport's record source in the subreport's
Open event, BUT only the first time the event procedure is
called.  The code could be like:

Static Initialized As Boolean
If Not Initialized Then
	Me.RecordSource = whatever
	Initialized = True
End If
-- 
Marsh
MVP [MS Access]


MacNut2004 wrote:
>Just thought of something. I also have subreports in my reports...can i 
>change the recordsource for those as well?  If so, can it be on the onopen 
>event of the MAIN report or does it have to be in each subreport as well?
>
>
>> MacNut2004 wrote:
>> >I have a handful of reports that I am trying to manipulate using VBA.  
>> >Depending on whatever they choose in a "currency" dropdown will determine 
>> >what data the report will be showing.  This is my question.  Is there a way 
>> >through VBA, depending on the value chosen in the pull down on the main form, 
>> >to bring up a report but manipulate it so it looks at a certain query, 
>> >changes its grouping levels, etc based on that choice in the dropdown?  This 
>> >would save me a TON of work, so I don't have to create new reports looking at 
>> >different queries and have to put grouping levels in each, etc, but can just 
>> >use that single report with some VBA manipulation.
>> 
>
>"Marshall Barton" wrote:
>> You can do that kind of thing in the report's Open event
>> procedure.  The reports record source can be set and
>> **existing** group levels can be modified.  The code usually
>> looks something like:
>> 
>> Select Case Forms!yourform.thecombobox
>> Case "rptA"	'or whatever value
>> 	Me.RecordSource = "QueryA"
>> 	Me.GroupLevel(0).ControlSource = "field1"
>> 	Me.GroupLevel(1).ControlSource = "field2"
>> 		'effectively "disable" level 3
>> 	Me.GroupLevel(2).ControlSource = "=1"
>> 	Me.Section(9).Visible = False		'header
>> 	Me.Section(10).Visible = False		'footer
>> 	Me.textboxX.Visible = False
>> 	 . . .
>> Case "rptB"
>> 	 . . .

Thank you once again!

"Marshall Barton" wrote:

> You can set a subreport's record source in the subreport's
> Open event, BUT only the first time the event procedure is
> called.  The code could be like:
> 
> Static Initialized As Boolean
> If Not Initialized Then
> 	Me.RecordSource = whatever
> 	Initialized = True
> End If
> -- 
> Marsh
> MVP [MS Access]
> 
> 
> MacNut2004 wrote:
> >Just thought of something. I also have subreports in my reports...can i 
> >change the recordsource for those as well?  If so, can it be on the onopen 
> >event of the MAIN report or does it have to be in each subreport as well?
> >
> >
> >> MacNut2004 wrote:
> >> >I have a handful of reports that I am trying to manipulate using VBA.  
> >> >Depending on whatever they choose in a "currency" dropdown will determine 
> >> >what data the report will be showing.  This is my question.  Is there a way 
> >> >through VBA, depending on the value chosen in the pull down on the main form, 
> >> >to bring up a report but manipulate it so it looks at a certain query, 
> >> >changes its grouping levels, etc based on that choice in the dropdown?  This 
> >> >would save me a TON of work, so I don't have to create new reports looking at 
> >> >different queries and have to put grouping levels in each, etc, but can just 
> >> >use that single report with some VBA manipulation.
> >> 
> >
> >"Marshall Barton" wrote:
> >> You can do that kind of thing in the report's Open event
> >> procedure.  The reports record source can be set and
> >> **existing** group levels can be modified.  The code usually
> >> looks something like:
> >> 
> >> Select Case Forms!yourform.thecombobox
> >> Case "rptA"	'or whatever value
> >> 	Me.RecordSource = "QueryA"
> >> 	Me.GroupLevel(0).ControlSource = "field1"
> >> 	Me.GroupLevel(1).ControlSource = "field2"
> >> 		'effectively "disable" level 3
> >> 	Me.GroupLevel(2).ControlSource = "=1"
> >> 	Me.Section(9).Visible = False		'header
> >> 	Me.Section(10).Visible = False		'footer
> >> 	Me.textboxX.Visible = False
> >> 	 . . .
> >> Case "rptB"
> >> 	 . . .
>