Programmatically Setting Order By Property in a Three Level Report

Posted on 2004-11-04
Last Modified: 2008-02-01
I  am filtering and sorting a report by form.   The filter is working fine, but the sort has me stumped.  

The report is 3-levels.  1-ENTITY, 2-DOCUMENT, 3-SUBDOC.   The report's recordsource is a single wide query, and I am using Groupings to nest the SUBDOC data under the related DOCUMENT data, and this under the related ENTITY data.

When I set the Order By property of the report at run time using my form, Access ignores it.  It appears that Access doesn't process the Order By property when the report has Sortings and Groupings.  I get an error:  "You tried to execute a query that does not include the specified expression '[DocumentName]' as part of an aggregate expression."   To get this error to go away I added [DocumentName] (and the other sort fields) to the list of Sortings and Groupings.  The error goes away, but Access does not sort on anything other than the preset sortings and groupings.  That won't help because the client needs to support and filter this report every-which-way.  Here is the code behind the form's [Set Sort Order] button click event:

    Dim strSQL As String, intCounter As Integer
    'Build strSQL String
    For intCounter = 1 To 6
        If Me("cboSort" & intCounter) <> "" Then
        strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
        If Me("Chk" & intCounter) = True Then
            strSQL = strSQL & " DESC"
        End If
        strSQL = strSQL & ", "
    End If

    If strSQL <> "" Then
        'Strip Last Comma & Space
        strSQL = left(strSQL, (Len(strSQL) - 2))
        'Set the OrderBy property
        Reports![rpt_Entity Doc Mgmt Detail].OrderBy = strSQL
        Reports![rpt_Entity Doc Mgmt Detail].OrderByOn = True
        Reports![rpt_Entity Doc Mgmt Detail].OrderByOn = False
    End If

As an alternative I have created a main report with two subreports.  That allows me to get rid of any preset sortings and groupings.  Now I can use the Order By property for the main report, but not the sub reports.  I have not had any luck referencing the Order By property of the sub reports through the subreport controls on the main report.  The client needs to sort the data at the subreport level also.

I am getting a bit frustrated at this point.  All the options I can think of will be big time.  That would be a real bummer since the work would be on my dime.

Am I missing a simple solution?  Anybody have some tricks to share?
Question by:k2jeff
    1 Comment
    LVL 58

    Accepted Solution

    When you have a report without sorting/grouping, the actual recordset of the report *is* the record source, to which the report will happily apply any .Filter and .OrderBy.
    When you use sorting/grouping, the report will in fact create a number of queries, collecting information from the .ControlSource properties of all controls. This is how the report manages to display Sum(curPament) in a header or footer. When that mechanism is in place, the .OrderBy becomes meaningless.

    If you feel adventurous, you can manipulate the sorting/grouping by program, though. I beleive it can be done only at design time, but this might be a solution.

    But I must admit that the entire logic puzzles me. How can you have six level ordering and three level grouping? Surely, the six-level ordering must be applied either before the first grouping or after the last.
    * Sort Entities any which way, then show related documents and sub-documents
    * Withing entities and documents, sort sub-documents any which way (less likely)

    One solution that comes to mind is to create a top-level grouping based on a calculated field. This calcualted field can then be included in the record's query by changing its .RecordSource property. Another is to create *six* top level sorting fields (Sort1, Sort2, etc.) which will be replaced in the query by "Country As Sort1, Department as Sort2, Null As Sort3, Null As Sort4, etc."

    Hope this helps

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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 specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now