I came across a request from a client who wanted the sorting from a Form to be transferred to a Report. Meaning that whatever was sorted for the Data Sheet view in a sub-form, to be transferred to the Report. This was a task that became almost impossible until I discovered a few things.

1. You cannot sort a Report Query and expect it to sort in a Report. It will not work.
2. You have to pass in the Forms Order By property, or update a global variable to store the sorting.
3. There is manual VB work involved.
4. There is also dummy Groups that are needed in the report that have to be updated when the Report is opened.

This is how I did it. I created a global variable to hold the Order By string from the Sub form. This variable was in a module, any module

Public g_strJobCostSort As String

I then wrote this code for opening the Report. Note that I had to remove certain sections of the Order String because of control names like Lookup_cboVendor, which is a dropdown in the sub-form.

    Dim strSQLSort As String
    
    If Nz(Me.subfrmJobCostItems.Form.OrderBy, "") <> "" Then
        strSQLSort = Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]")
        g_strJobCostSort = strSQLSort
        DoEvents
    End If

    DoCmd.OpenReport "rptJobCostingCombined", acPreview, , "JobCostID=" & Me.txtJobCostID

I also created 2 buttons on the Sub-Form so the user could see the existing Sort Order and also clear it. One thing worth noting too is that the last column you sort on is actually the first column to be sorted on. It makes sense but you might need to try this is order for it to make any sense to you.

The Sorting button runs this code and the following dialog is shown.

MsgBox Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]"), vbInformation, "Sorting"

The Clear Sorting code is this

    Me.subfrmJobCostItems.Form.OrderBy = ""
    Me.subfrmJobCostItems.Form.Requery

The Sub Report has the following Group On and Sort By properties/sections. I only added 4 Sort By sections but you can have as much as you like I guess. I noticed too that if I added a Group On but did not have a Group Header or Group Footer that it switched to a Sort by section. That's fine as it working the same way.

 This is the Code that I added to the Report_Open event in the subform. Note that I added " DESC" with a space at the beginning to make sure it was targeting the DESC sort order at the end, if there was one. If your columns have this combination, like Description, then you'll need to check that this code does not affect them. Basically this code gets all the sort columns, even if they're blank! This is why I chose to do On Error Resume Next. It's not the most glamorous way code, but it works and it will sort on the "up to" 4 sections that I added.

    Dim strSorting() As String
    strSorting = Split(g_strJobCostSort, ",")
    
    On Error Resume Next
    Me.GroupLevel(1).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(0)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(2).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(1)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(3).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(2)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(4).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(3)), " DESC", ""), "]", ""), " ", ""), "[", "")
    DoEvents
    
    If InStr(strSorting(0), "DESC") > 0 Then
        Me.GroupLevel(1).SortOrder = True
    End If
    
    If InStr(strSorting(1), "DESC") > 0 Then
        Me.GroupLevel(2).SortOrder = True
    End If
    
    If InStr(strSorting(2), "DESC") > 0 Then
        Me.GroupLevel(3).SortOrder = True
    End If
    
    If InStr(strSorting(3), "DESC") > 0 Then
        Me.GroupLevel(4).SortOrder = True
    End If

Just as a courtesy, I have add the following links that helped me get to this solution after about 8 hours of trying. 

https://stackoverflow.com/questions/18836199/access-2010-vba-to-sort-a-report-with-existing-grouping/51014170#51014170?newreg=168265e41fd044cea63b1817e09e86ea

https://stackoverflow.com/questions/40402/what-is-the-command-to-truncate-a-sql-server-log-file

https://accessexperts.com/blog/2011/07/15/dynamically-sorting-forms-and-reports/

http://allenbrowne.com/ser-33.html - Allen Browne from Perth, Australia, is a legend!

Happy Coding, Coders!