Can I manipulate my report group header with VBA?

[Chrisso]

Hi All

I have a report that runs nicely.

I want to give my users the option of grouping the report.

I would like to use the same report object for both to ease
maintenance.

Therefore I would like to be able to tell my report via VBA to group
and display a preconfigured group header when this option is selected.
Otherwise I want no grouping and no group header to be displayed.

Is this possible?

Cheers for any ideas,
Chrisso

[Graham Mandeno]

Hi Chrisso

You can't add group levels or header/footer sections at run-time without
opening the report in design view.

However, you can change all the properties of an existing GroupLevel object
(including ControlSource - the field being grouped or sorted) in the
Report_Open event procedure, as well as making header and footer sections
visible or invisible as required.

So, the trick is to create as many group levels as you might possibly
require, and give them headers and/or footers if they might possibly be
needed. For each section with "variable requirement", set the ControlSource
(Field/Expression column) to =1, and make the header and/or footer
invisible.

Then, in Report_Open, you can then manipulate the existing objects as
required. For example:

If ...... Then
Me.GroupLevel(0).ControlSource = "MySortField"
Me.GroupLevel(0).SortOrder = True ' descending
Me.Section(5).Visible = True ' make the header visible
Me.Section(6).Visible = True ' make the footer visible
End If

Note that the header for GroupLevel(n) is Me.Section(n*2+5)
and the footer is Me.Section(n*2+6), where n starts from 0.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Chrisso" <chris7...@gmail.com> wrote in message
news:f6169840-e364-4e47...@s39g2000yqj.googlegroups.com...

[June7 via AccessMonster.com]

Not sure. Design with the headers/footers and controls in place. Use VBA code
to build the form's RecordSource query and pass it and other criteria in the
DoCmd.OpenForm.
In Open event of the form, extract the query string and set the RecordSource
property.
Extract other criteria from the string to use as conditions for displaying
header/footer in the Detail_Format event of the form. Ex:
DoCmd.OpenForm formname,,,,,strString & "," & strCriteria

In Open event:
Me.RecordSource = Left(Me.OpenArgs, InStr(Me.OpenArgs,",") - 1)

Now in the Detail_Format event
If Not Me.OpenArgs Like "*Groups" Then
code to not display header/footer, I have done this to set display of
controls but not headers/footers
End If

Or you can create multiple reports with copy/paste and call whichever is
needed.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200909/1

[June7 via AccessMonster.com]

Dang, wish I could edit posts, statement should read
"in the DoCmd.OpenForm OpenArgs argument."

and
"In the Open event of th REPORT, extract ..."

and
"...in the Detail_Format event of the REPORT.

June7 wrote:
>Not sure. Design with the headers/footers and controls in place. Use VBA code
>to build the form's RecordSource query and pass it and other criteria in the
>DoCmd.OpenForm.
>In Open event of the form, extract the query string and set the RecordSource
>property.
>Extract other criteria from the string to use as conditions for displaying
>header/footer in the Detail_Format event of the form. Ex:
>DoCmd.OpenForm formname,,,,,strString & "," & strCriteria
>
>In Open event:
>Me.RecordSource = Left(Me.OpenArgs, InStr(Me.OpenArgs,",") - 1)
>
>Now in the Detail_Format event
>If Not Me.OpenArgs Like "*Groups" Then
> code to not display header/footer, I have done this to set display of
>controls but not headers/footers
>End If
>
>Or you can create multiple reports with copy/paste and call whichever is
>needed.
>

>>Hi All
>>
>[quoted text clipped - 13 lines]

[Chrisso]

Thanks guys - I wll try these suggestions out and report back.

Chrisso

[June7 via AccessMonster.com]

Still didn't get my typos fixed, should be:
"in the DoCmd.OpenReport ..."

June7 wrote:
>Dang, wish I could edit posts, statement should read
>"in the DoCmd.OpenForm OpenArgs argument."
>
>and
>"In the Open event of th REPORT, extract ..."
>
>and
>"...in the Detail_Format event of the REPORT.
>

>>Not sure. Design with the headers/footers and controls in place. Use VBA code
>>to build the form's RecordSource query and pass it and other criteria in the

>[quoted text clipped - 22 lines]

[msh...@gmail.com]

Doesn't work for level 3: <header for GroupLevel(n) is Me.Section(n*2+5)>