467,890 Members | 1,663 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,890 developers. It's quick & easy.

Conditional Report Group Footer/Header using VBA

I currently have a Form with Option buttons (to give you the option of
sorting by name, or by Field, etc), these are referenced in a module
which then formats the report contingent on the option chosen (i.e.
the title changes to say "Sorted by Name" or "Sorted by Field"). For
one of the options, "Sort by Field" I would not only like to have them
sorted by Field alphabetically, but I would like to Group them and
have a Group Header and Footer and have each Field on a separate page.
I can of course do this if I specifically have a report setup with
the Grouping and the Footer and Header with the page break, but I was
wondering if there was a way to do this on the fly using VBA.

Thanks in advance for any advice.
Nov 12 '05 #1
  • viewed: 11634
Share:
1 Reply
Shiz wrote:
I currently have a Form with Option buttons (to give you the option of
sorting by name, or by Field, etc), these are referenced in a module
which then formats the report contingent on the option chosen (i.e.
the title changes to say "Sorted by Name" or "Sorted by Field"). For
one of the options, "Sort by Field" I would not only like to have them
sorted by Field alphabetically, but I would like to Group them and
have a Group Header and Footer and have each Field on a separate page.
I can of course do this if I specifically have a report setup with
the Grouping and the Footer and Header with the page break, but I was
wondering if there was a way to do this on the fly using VBA.


You can use the report's Open event procedure to change the
properties of the header/footer sections and the GroupLevel
objects.

Start with the report that has the sorting and grouping set
up for one of the fields along with its associated header
and footer sections (with the header's ForceNewPage set to
Before Section). Then use code in the report's open event
to interrogate the form and set the properties as specified.
Here's some air code as an example:

Dim strFieldName As String
If Forms!theform.chkByName = True Then
Me.thelabel.Caption = "Sorted by Name"
Me.Section(5).Visible = False 'No header/footer
Me.Section(6).Visible = False
Me.GroupLevel(0).ControlSource = "=[namefield]"
Else
strFieldName = Forms!theform.fieldnamecombo
Me.thelabel.Caption = "Sort by Field " & strFieldName
Me.GroupLevel(0).ControlSource = strFieldName
End If

--
Marsh
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by David Horowitz | last post: by
1 post views Thread by PDH | last post: by
3 posts views Thread by carmela_wong | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.