Modify group and sort levels in a report using VBA

[dogdays]

How does one modify the group and sort levels in a report at run time? I have to produce a report where the user will specify, on a form, the grouping and sorting criteria.

tia Jack

 

[tweedle]

These changes are only allowed when the report is opened in design view.

Products is my report
Product_Type is my Group/Sort field

onto the code:

Sub GroupTest()
'Call to add grouping
'retval = AddGroupToReport("Products", "Product_Type", True, True)
'call to add sort
'retval = SetReportOrder("Products", "Product_Type")
End Sub

Function AddGroupToReport(rpt$, fld$, GrpHdr As Boolean, GrpFooter As Boolean)
On Error GoTo ErrTrap
Dim intGroupLevel
    DoCmd.OpenReport rpt$, acViewDesign                             'Open in Design View
        intGroupLevel = CreateGroupLevel(rpt$, fld$, True, True)    'Mod the Group
    DoCmd.Close acReport, rpt$, acSaveYes                           'Close from Design View
Exit Function
ErrTrap:
Debug.Print Err.Number, Err.Description
Err = 0
End Function

As you build the string for fields to sort (var flds$), you can specify the asc/desc as such: [field] DESC

Function SetReportOrder(rpt$, flds$)
 
    DoCmd.OpenReport rpt$, acViewDesign     'Open in Design View
        Reports(rpt$).OrderBy = flds$       'Mod the Sort Property
    DoCmd.Close acReport, rpt$, acSaveYes   'Close from Design View
End Function

 

[dogdays]

Tweedle:
Thanks. What does the line of code <

intGroupLevel = CreateGroupLevel(rpt$, fld$, True, True) 'Mod the Group> do?

Jack

 

[tweedle]

That's the function that actually does the work.

intGroupLevel 'retval to test success if desired
=
CreateGroupLevel(Parm1, Parm2, Parm3, Parm4) ' Access function that creates the grouping
Parm1 - Report Name to add grouping
Parm2 - field to add grouping for
Parm3 - Add Header?
Parm4 - Add Footer?
True -

 

[tweedle]

Woops, I neglected to move the variables into the literal Trues of the function, I see now....