changing grouping and sorting using code

[smig]

Is it possible to change the Grouping and sorting data of report using code.
I'd like an option to sort the report once by the Client Name and in other times by it's ID.
Also I'd like an option to group the report once by City and in other time by Year of Birth

Thanks,
Tal

 

[pbaldy]

See if this gives you any ideas:

Private Sub Report_Open(Cancel As Integer)
  If Forms!frmShiftData.chkSummary = True Then
    Me.Detail.Visible = False
  End If

  Select Case Forms!frmShiftData.fraGroupBy
    Case 1    'group on dates
      Me.GroupLevel(0).ControlSource = "Enter_Date"
      Me.txtGroupHeader.ControlSource = "Enter_Date"
      Me.txtDetail.ControlSource = "Shift"
      Me.lblGroupLabel.Caption = "DOR Date"
      Me.lblDetailLabel.Caption = "Shift"
    Case 2    'group on shifts
      Me.GroupLevel(0).ControlSource = "Shift"
      Me.txtGroupHeader.ControlSource = "Shift"
      Me.txtDetail.ControlSource = "Enter_Date"
      Me.lblGroupLabel.Caption = "Shift"
      Me.lblDetailLabel.Caption = "DOR Date"
  End Select
  
End Sub

 

[smig]

Thanks

exactly what I needed
I missed the .ControlSource


Tal

 

[pbaldy]

Happy to help.

 

[s_solt]

You can also turn a group off, rather than change it to group on another field, by making the groupheader.visible false

However, unless you set the grouplevel.controlsource to a constant, the hidden group will still control the order of the records

Private Sub Report_Open(Cancel As Integer)
    If FormIsOpen("frmDrawingIssues") Then
        Dim oSql As New SqlString 
        'this is my class to handle modifying sql expressions
        oSql.sql = RecordSource
        If Form_subIssueDrawings.tglRegisterOrder Then
            'user toggled to group and sort by register
            GroupHeader1.Visible = True
            GroupLevel(1).ControlSource = "fldRegister"                                                    
            oSql.pOrder = "fldCoName, fldRegister, cDwgNo, fldRev"
        Else
            GroupHeader1.Visible = False
            GroupLevel(1).ControlSource = "fldIssueID" 'this is a constant
            oSql.pOrder = "fldCoName, fldDwgSortOrder, cDwgNo, fldRev"
        End If
        If oSql.sql <> RecordSource Then
            RecordSource = oSql.sql
        End If
    End If
End Sub