Specify sort order in sub-reports

[J_Goddard via AccessMonster.com]

Good day -

Using Access 2000.

I have a main report with two subreports. What I want to be able to do is
allow my users to select the sort order to be used on one of the subreports
(there are 5 options).

The main report and the two subreports are each based on separate queries.

In the form the used to set up the report specifcations, users select the
sort option to be used, and then use code to change to change the SQL of the
query which is the recordsource of the subreport:

Select Case Me!SortOrder ' User selects the sort order on the form
Case 1
CurrentDb.QueryDefs![corcas case report query].SQL = querySQL & _
" ORDER BY [module number], [KPath Number] desc;"
Case 2
CurrentDb.QueryDefs![corcas case report query].SQL = querySQL & _
" ORDER BY [module number], [countofpts]/[cluster size]*100 desc;"
'
' etc for 5 choices
'
End Select

I know this works, because I can go to the query [corcas case report query]
and see the changes have been made correctly.

However, when I run the main report, the subreport is not sorted correctly -
to the user it is not sorted at all - it is sorted on a field which is in the
underlying query, but is not part of the report.

- I have deleted grouping/sort order in the sub-report
- the sub-report is linked to the subreport through one field ([module number]
)
- changing the recordsource in the on-open of the subreport generates an
error

It appears the the sub-report does not recognize the sorting specified in its
source query.

Is there any way to accomplish what I am trying to do?

Thanks all

John

 

[KARL DEWEY]

It appears the the sub-report does not recognize the sorting specified in its
source query.
You have to use the Sorting and Grouping in the subreport to sort. Answer
is to create a calculated field named Sort_Order to use for sorting in the
report.

I do not use code to change to change the SQL of the query but use nested
IIF statements -- Sort_Order: IIF([Forms]![MyForm]![Frame4] = 1,
[Field1], IIF([Forms]![MyForm]![Frame4] = 2, [Field2, ....

 

[J_Goddard via AccessMonster.com]

Hi -

Thanks for pointing me in the right direction. It turns out that I could not
reference the sort order of the subreport directly - I had to use the
properties of the relevant GroupLevel.

It also turns out that while the setting is in the On Open event of the
subreport, it can only be done once, the first time the sub-report is opened.
Trying to set the sort order again in subsequent occurances of the subreport
produced an error that "you cannot set the sort order after printing has
started" (more or less).

So, for the benefit of all, here is what I came up with, in the On Open event
of the subreport:

Private Sub Report_Open(Cancel As Integer)
Reports![corcas main].SubCount = Reports![corcas main].SubCount + 1
If Reports![corcas main].SubCount = 1 Then
'
' Change order on first iteration only
'
Select Case Forms![corcas - report]!SortOrder
Case 1
Me.GroupLevel(1).ControlSource = "Kpath Number"
Me.GroupLevel(1).SortOrder = True
Me![SortKPath].Visible = True ' Label to mark the sorted column
Case 2
Me.GroupLevel(1).ControlSource = "percenttasks"
Me.GroupLevel(1).SortOrder = True
Me![SortPTP].Visible = True
Case 3
Me.GroupLevel(1).ControlSource = "sumofpts"
Me.GroupLevel(1).SortOrder = True
Me![SortPTS].Visible = True
Case 4
Me.GroupLevel(1).ControlSource = "Task Importance"
Me.GroupLevel(1).SortOrder = True
Me![SortIMP].Visible = True
Case 5
Me.GroupLevel(1).ControlSource = "performance emphasis"
Me.GroupLevel(1).SortOrder = True
Me![SortPE].Visible = True
End Select
End If
End Sub

Using a counter, it sets the sort order and sort field only on the first time
through - works like a charm.

Thanks for your ideas.

John

source query.
You have to use the Sorting and Grouping in the subreport to sort. Answer
is to create a calculated field named Sort_Order to use for sorting in the
report.

I do not use code to change to change the SQL of the query but use nested
IIF statements -- Sort_Order: IIF([Forms]![MyForm]![Frame4] = 1,
[Field1], IIF([Forms]![MyForm]![Frame4] = 2, [Field2, ....

Good day -

[quoted text clipped - 43 lines]

John

 

[Marshall Barton]

Thanks for pointing me in the right direction. It turns out that I could not
reference the sort order of the subreport directly - I had to use the
properties of the relevant GroupLevel.

It also turns out that while the setting is in the On Open event of the
subreport, it can only be done once, the first time the sub-report is opened.
Trying to set the sort order again in subsequent occurances of the subreport
produced an error that "you cannot set the sort order after printing has
started" (more or less).

So, for the benefit of all, here is what I came up with, in the On Open event
of the subreport:

Private Sub Report_Open(Cancel As Integer)
Reports![corcas main].SubCount = Reports![corcas main].SubCount + 1
If Reports![corcas main].SubCount = 1 Then
'
' Change order on first iteration only
'
Select Case Forms![corcas - report]!SortOrder

[]

In order to localize the logic to the subreport's Open event
procedure, I prefer to use a static variable instead of a
control on the main report:

Private Sub Report_Open(Cancel As Integer)
Dim Initialized As Boolean

If Not Initialized Then
Initialized = True
Select Case Forms![corcas - report]!SortOrder
. . .

 

[J_Goddard via AccessMonster.com]

Yes, so would I. I used my method because I was not sure the value of the
variable - in your case Initialized - would be retained from one execution of
the On Open event to the next. Will it, the way you have shown it -
especially since it is being re-declared each time through?
Should it read "Static Initialized as Boolean" ?

Thanks

John

Thanks for pointing me in the right direction. It turns out that I could not
reference the sort order of the subreport directly - I had to use the

[quoted text clipped - 16 lines]

'
Select Case Forms![corcas - report]!SortOrder

[]

In order to localize the logic to the subreport's Open event
procedure, I prefer to use a static variable instead of a
control on the main report:

Private Sub Report_Open(Cancel As Integer)
Dim Initialized As Boolean

If Not Initialized Then
Initialized = True
Select Case Forms![corcas - report]!SortOrder
. . .

 

[Marshall Barton]

Of course it should be Static. Dim was a brain fault :-\
--
Marsh
MVP [MS Access]

Yes, so would I. I used my method because I was not sure the value of the
variable - in your case Initialized - would be retained from one execution of
the On Open event to the next. Will it, the way you have shown it -
especially since it is being re-declared each time through?
Should it read "Static Initialized as Boolean" ?

Thanks for pointing me in the right direction. It turns out that I could not
reference the sort order of the subreport directly - I had to use the

[quoted text clipped - 16 lines]

'
Select Case Forms![corcas - report]!SortOrder

[]

In order to localize the logic to the subreport's Open event
procedure, I prefer to use a static variable instead of a
control on the main report:

Private Sub Report_Open(Cancel As Integer)
Dim Initialized As Boolean

If Not Initialized Then
Initialized = True
Select Case Forms![corcas - report]!SortOrder
. . .