Modify group and sort levels in a report using VBA

dogdays

Active Member
Joined
Apr 28, 2008
Messages
431
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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:
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

Code:
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

Code:
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

Active Member
Joined
Apr 28, 2008
Messages
431
Tweedle:
Thanks. What does the line of code <

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

Jack
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Woops, I neglected to move the variables into the literal Trues of the function, I see now....
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,453
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top