Solved

User Defined Custom Sort Order - Ms Access 2003

Posted on 2010-09-13
7
1,179 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:budorat
  • 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:budorat
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 5

Author Comment

by:budorat
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:
budorat 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:budorat
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

829 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