We help IT Professionals succeed at work.

User Defined Custom Sort Order - Ms Access 2003

Kev
Kev asked
on
1,256 Views
Last Modified: 2013-11-28
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

Open in new window

OriginalMenu.png
NewMenu.png
ExpertsQ.mdb
report-sorting-2000.mdb
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
test this

select surname in the first combo to see the sorting taking effect
then click the button set Sort Order

the report msut be open together with the form.
ExpertsQ.mdb
Kev

Author

Commented:
Hi Capricorn,
Thanks for that, unfortunately this does not really help me.
My original post (ExpertsQ.mdb) could already sort the report without the extra step of clicking the Sort button.
It is just when I use the same code in my real DB that it fails. I have uploaded the latest version of my Reporting Menu. Some elements have been changed so it works in isolation of the real DB.
You will see that it can open the report and set the sort order. My main Q is why would it work in isolation here, but when included as part of my real DB it would open the report then not do any sorting ?

Kev
ExpertsQ-v2.mdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
what is the name of the report in your real DB? is the report the same as the one in your sample db?
Kev

Author

Commented:
Hi Capricorn,

The real one is called 'rptNominalRoll-1'. The real reporting menu Select Report drop down displays the Report Description, but the bound column (1) has the report name ie rptNominalRoll-1
I just tried renaming it so see if it made a difference, no change.
Have tried a different approach where I leave the Report Menu open after the report opens so you can set custom sort order then. The issue is that I now get an error 'Error 2476 - You entered an expression that requires a report to be the active window' once you click ok it then sorts the report.

This is not my preferred approach as users have been used to having to select the sort order (prev radio buttons) before they click preview. They are also used to seeing the report menu disappear when they click preview.

Code attached top the sort order button is as follows:

Thanks

Kev


Private Sub cmdSort_Click()
    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
       
    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

Open in new window

Menu3.png
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
are you sure that the codes in the sample db that you uploaded does not work in your real db?

i have used this similar codes in my report before in one of my applications,  and it is working using the codes similar with your code used in your sample db.

can you upload the real db?
Kev

Author

Commented:
yep, very sure. I don't understand it myself.... I was tackling this for about 9 hours before I eventually asked for help.

It would open the report but would not sort the data.

I work for an Aust Gov Dept, so the DB has a lot of sensitive data in it, therefore can not upload it.

The new way above works better for me in fact as I can set a default sort order and use the default or user defined sort orders.

Kev
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.