Using VBA to manipulate Reporting

[MacNut2004]

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

 

[Marshall Barton]

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"
. . .

 

[MacNut2004]

Marshall,

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

Thanks again!
MN

 

[MacNut2004]

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]

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

 

[MacNut2004]

Thank you once again!

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]

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?