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"
strSQL = strSQL & ", "
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
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?