How set sorting and grouping in code?

[mscertified]

My record source is passed to my report in openargs and I need to dynamically
set sorting and grouping in code.
Is this possible and if so, how?

Thanks.

 

[Duane Hookom]

Allen Browne has code for this at his web site
http://www.allenbrowne.com/ser-33.html. You must have the grouping and
sorting levels created prior to running the code. This can be done by setting
the field/expression to =1.

 

[Klatuu]

Here is an example that may help:

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Report_Open_Error

'Activity is always last
Me.GroupLevel(5).ControlSource = "Activity"
With Forms!frmbporeports
'Determine which Subtotals have been selected
'HomeRooom
If .chkHomeRoom Then
Me.GroupLevel(4).ControlSource = "PerformAcctUnit"
Else
Me.GroupLevel(4).ControlSource = Me.GroupLevel(5).ControlSource
End If
'Pool
If .chkPool Then
Me.GroupLevel(3).ControlSource = "Pool"
Else
Me.GroupLevel(3).ControlSource = Me.GroupLevel(4).ControlSource
End If
'BillNetwork
If .chkBillNetwork Then
Me.GroupLevel(2).ControlSource = "BillNetwork"
Else
Me.GroupLevel(2).ControlSource = Me.GroupLevel(3).ControlSource
End If
'MasterActivity
If .chkMActivity Then
Me.GroupLevel(1).ControlSource = "MActivity"
Else
Me.GroupLevel(1).ControlSource = Me.GroupLevel(2).ControlSource
End If
End With
'Top Level For Billable Product Offering
Me.GroupLevel(0).ControlSource = "ProjectId"

Report_Open_Exit:

On Error Resume Next
Exit Sub

Report_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure Report_Open of VBA Document
Report_rptBPOProducOffering"
GoTo Report_Open_Exit
End Sub

 

[Marshall Barton]

My record source is passed to my report in openargs and I need to dynamically
set sorting and grouping in code.
Is this possible and if so, how?

You need to create the grouping entries and group
header/footers manually in design view.

You can then change the field/expression by using code in
the report's Open event:
Me.GroupLevel(x).RecordSource = "fieldname"

To effectively disable a sort/group, set it to a constant
expression:
Me.GroupLevel(x).RecordSource = "=0"
and make any group header/footer sections invisible.

Check VBA Help for details about the GroupLevel object.