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
