Solved

User Defined Custom Sort Order - Ms Access 2003

Posted on 2010-09-13
7
1,174 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 119

Expert Comment

by:Rey Obrero
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now