?
Solved

User Defined Custom Sort Order - Ms Access 2003

Posted on 2010-09-13
7
Medium Priority
?
1,189 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
0
Comment
Question by:Kev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33665556
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
0
 
LVL 5

Author Comment

by:Kev
ID: 33667612
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33667767
what is the name of the report in your real DB? is the report the same as the one in your sample db?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 5

Author Comment

by:Kev
ID: 33667849
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
0
 
LVL 5

Accepted Solution

by:
Kev earned 0 total points
ID: 33667987
Hi,

I think I have a viable solution using the open arguments option.

First, set report with default sort order, in this case 'RankOECode DESC, Surname, Initials' to allow for users not defining custom sort order.

Adjust code on reporting menu preview button to parse the user defined sort oder to the report

Set the  Report_Open event to 1. determine default sort order and 2. set new user defined sort order if not null..... see code below:

Your thoughts, comments ? Is there anything I may have missed or not considered ?

Kev





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, strCustomSort As String, intCounter As Integer
    
    strReportName = cmboReports.Value
    MsgBox strReportName
    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))
    End If
    
    MsgBox strCustomSort
    Call SetFilterCriteria(strFilterCriteria)
    'DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria              'Open selected Report
    DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria, , strCustomSort
    Me.Visible = False
    
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

'***************************************************
Private Sub Report_Open(Cancel As Integer)
    Dim strOrderBy As String
    strOrderBy = Me.OrderBy
    MsgBox strOrderBy
    
    Me.OrderBy = Nz(Me.OpenArgs, strOrderBy)
    MsgBox Me.OrderBy
    Me.OrderByOn = True
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33668083
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?
0
 
LVL 5

Author Comment

by:Kev
ID: 33668151
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question