Using VBA to manipulate Reporting

  • Thread starter Thread starter MacNut2004
  • Start date Start date
M

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
 
MacNut2004 said:
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.
Click to expand...


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

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

Thanks again!
MN
 
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
 
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
 
Thank you once again!

Marshall Barton said:
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?
Click to expand...
Click to expand...
 
Back
Top