User Defined Custom Sort Order - Ms Access 2003

Hi,

So I have what is proving to be a rather difficult problem to resolve or maybe I am just being too ambitious.

Situation: I have a DB that has a few hundred reports in it. My custom report menu (see below) works very well. You will see that the reporting menu has a sort order option area. The issue with the current sort order option is that you need several lines of code behind each report to set the sort order based on the selection on the reporting menu. It creates a lot of code that could be done without (maybe) and is a lot to manage.

I am hoping by changing my sort order approach I can
1. reduce the need for code to sit behind every report
2. reduce the over DB size by reducing the code requirement
3. introduce a more powerful sort order option to users.
4. make it easier to manage report sort order options, ie I set a default and that's it. All users can do what they want thereafter.

I found an example online and tried to implement it. I was having problems so I decided to put together parts of my db and a separate app to upload for help. The issue is that it started working perfectly in the standalone version, but when I put the code into my main DB, it does not work. The report opens as expected but the report does not sort based on the custom sort order as it does in the stand alone version.

Tried turning the OrderBy property on and off on the report with no success.
Have removed any sorting from the underlying query.
Have put a value in the OrderBy property in the hope that it will be overwritten (which it does in the test DB) with no luck.

Attached is the following:
My original sort order approach - menu and example code
New sort order menu approach - screen shot and example code
Test DB with excerpts from my DB.  Note that I have disabled a lot of the functionality of the report menu so that it wont error, it relies on lots of info from the real DB.

Where am I going wrong ?

Thanks in advance.

Kev

'***************** Original Sort Order Code ********************
Private Sub Report_Open(Cancel As Integer)
'************************************************************************************************
' FUNCTION NAME:        Report_Open
' PURPOSE:              Set OrderBy property (sort order) based on selection from ReportsMenu
' INPUT PARAMETERS:     intSortOption
' RETURN:               Report is sorted based on option selected
'************************************************************************************************
On Error GoTo Err_Handler
Dim intSortOption As Integer, strOrderBy As String

    intSortOption = Form_frmReportsMenu1.FrameSort.Value

    Select Case intSortOption
        Case "1"
           'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "2"
            'Rank Alphabetical
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "3"
           'Alphabetical
            strOrderBy = "Surname, Initials"
        Case "4"
            'SubUnit Rank Alphabetical
            strOrderBy = "SubUnitSortPri, RankOECode DESC,Surname,Initials"
        Case "6"
            'Employee ID
            strOrderBy = "EID"
        Case Else
            
            'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
    End Select

    Me.OrderBy = strOrderBy

Exit_Handler:
        Exit Sub
Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "Report_Open()"
        Resume Exit_Handler
End Sub

'***************************** Code form test DB Sort Order **************************

Private Sub cmdPreview_Click()
'************************************************************************************************
' FUNCTION NAME:        Preview_Click
' PURPOSE:              Previews selected report and closes ReportsMenu
' INPUT PARAMETERS:     [cmboReports].Value
' RETURN:               Preview of selected report
'************************************************************************************************
On Error GoTo Err_Handler
    Dim strFilterCriteria As String, strReportName As String
    Dim strSQL As String, intCounter As Integer, strCustomSort As String
    
    'Build strSQL String
    For intCounter = 1 To 6
        If Me("cboSort" & intCounter) <> "" Then
        strCustomSort = strCustomSort & "[" & Me("cboSort" & intCounter) & "]"
        If Me("Chk" & intCounter) = True Then
            strCustomSort = strCustomSort & " DESC"
        End If
        strCustomSort = strCustomSort & ", "
    End If
    Next
    
            strReportName = cmboReports.Value
            Call SetFilterCriteria(strFilterCriteria)
            DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria              'Open selected Report
            Me.Visible = False
   
    If strCustomSort <> "" Then
        'Strip Last Comma & Space
        strCustomSort = Left(strCustomSort, (Len(strCustomSort) - 2))
        MsgBox strCustomSort
        'Set the OrderBy property
        Reports(strReportName).OrderBy = strCustomSort
        Reports(strReportName).OrderByOn = True
    Else
        Reports(strReportName).OrderByOn = False
    End If

Exit_Handler:
        Exit Sub
Err_Handler:
    If Err = 2501 Then
        Resume Err_Handler
    Else
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "cmdPreview_Click()"
        Resume Exit_Handler
    End If
End Sub

'**************************** Code on New Reporting Menu *****************
Private Sub cmdPreview_Click()
'************************************************************************************************
' FUNCTION NAME:        Preview_Click
' PURPOSE:              Previews selected report and closes ReportsMenu
' INPUT PARAMETERS:     [cmboReports].Value
' RETURN:               Preview of selected report
'************************************************************************************************
On Error GoTo Err_Handler
    Dim strFilterCriteria As String, strCustomSort As String, intCounter As Integer, strReportName As String
    
    strReportName = cmboReports.Value
    'MsgBox strReportName
    'Build custom sort order String
    For intCounter = 1 To 6
        If Me("cboSort" & intCounter) <> "" Then
        strCustomSort = strCustomSort & "[" & Me("cboSort" & intCounter) & "]"
        If Me("Chk" & intCounter) = True Then
            strCustomSort = strCustomSort & " DESC"
        End If
        strCustomSort = strCustomSort & ", "
    End If
    Next
    
    Call SetFilterCriteria(strFilterCriteria)
    DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria              'Open selected Report
    Me.Visible = False
    
    If strCustomSort <> "" Then
        'Strip Last Comma & Space
        strCustomSort = Left(strCustomSort, (Len(strCustomSort) - 2))
        'Set the OrderBy property
        MsgBox strCustomSort
        Reports(strReportName).OrderBy = strCustomSort
        Reports(strReportName).OrderByOn = True
    Else
        Reports(strReportName).OrderByOn = False
    End If
    
Exit_Handler:
        Exit Sub
Err_Handler:
    If Err = 2501 Then
        Resume Err_Handler
    Else
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "cmdPreview_Click()"
        Resume Exit_Handler
    End If
End Sub

Select allOpen in new window

OriginalMenu.png
NewMenu.png
ExpertsQ.mdb
report-sorting-2000.mdb