Order by in a report dynamically

[swee]

How do I let users choose the way they want to sort their reports through a form dynamically?

 

[john471]

I achieve this by building the form with controls to provide the various (likely) options. Use the form to open the report, then use the report's OnOpen event to examine the state of the form's controls, and build and set the report's OrderBy property accordingly.... but don't forget to also set OrderByOn = True !

HTH

Regards

John.

 

[swee]

..and build and set the report's OrderBy property accordingly.... but don't forget to also set OrderByOn = True !

How do I set the Orderbyproperty in the OpenReport method?

 

[john471]

I don't fully know your circumstances, but for example say you had an Employee report which might make sense to sort by either "Surname then First Name" or "Employee Number"....

Put an option group on the form from which you will have the report open, with a radio buttons for the user to make their choice, and a command button to open the report.

Then in Properties of the report, under event, onOpen, set it to "[Event Procedure]" and click on the elipsis ("...") to go to the code module.

The OnOpen event will be built for you.

In there, put the code similar to the following....

Private Sub Report_Open(Cancel As Integer)
    Dim szOrderBy As String
    Dim f As Form_frmMyUserChoiceForm
    
    If bIsLoaded("frmMyUserChoiceForm") Then
        'Only do this if the frmMyUserChoiceForm is loaded
        'otherwise don't change the sort order set at report design time.
        Set f = Forms!frmMyUserChoiceForm
        
        Select Case f.FraOrderBy.Value
            Case 1 ' Surname, Name
                szOrderBy = "[NameLast], [NameFirst]"
            Case 2 ' Staff Number
                szOrderBy = "[StaffNbr]"
        End Select
        
        Me.OrderBy = szOrderBy
        Me.OrderByOn = True
        Set f = Nothing
    End If
End Sub

Function bIsLoaded(szFrmName As String) As Boolean
    
    '  Determines if a form is loaded.
    
    Const conFormDesign = 0
    Dim n As Integer
    
    bIsLoaded = False
    For n = 0 To Forms.Count - 1
        If Forms(n).FormName = szFrmName Then
            If Forms(n).CurrentView <> conFormDesign Then
                bIsLoaded = True
                Exit Function  ' Quit function once form has been found.
            End If
        End If
    Next
End Function

Good Luck.

HTH

John