Link to home
Start Free TrialLog in
Avatar of k2jeff
k2jeffFlag for United States of America

asked on

Programmatically Setting Order By Property in a Three Level Report

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
    Next

    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
    Else
        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?
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial