Modify group and sort levels in a report using VBA

dogdays

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

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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
430
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....
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,663
Messages
5,512,717
Members
408,910
Latest member
fchri31

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top