Sorting in Subreport

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

I have a report with three subreports. The user runs the
report from a form and based on a choice within an option
group, the user requests sort by either drug name or by
date.

I use the OpenArgs during the OpenReport process to pass
the option group choice, and I have an if/then statement
on the OnOpen event of the subreports to determine what
OpenArgs choice is made and then how to sort it, by drug
or date. So far, so good.

I tried sorting using the Me.OrderBy = "drug name, date"
or "date, drug name", and I get the error "2101 - the
setting you entered isn't valid for this property". OK,
the syntax appears correct, so I looked around and didn't
find much on what I needed to do to change it.

Someone suggested changing the recordsource, so I tried
that, adding an "ORDER BY drug, date" onto the string for
the record source, and the code runs, it just doesn't
complete the sort in the way I ask it (for example, when
I ask it to sort by drung name it doesn't sort by drug
name, asc or desc, it sorts by date every time).

Anyone have any experience with dynamically sorting
subreports?
 
Phil said:
I have a report with three subreports. The user runs the
report from a form and based on a choice within an option
group, the user requests sort by either drug name or by
date.

I use the OpenArgs during the OpenReport process to pass
the option group choice, and I have an if/then statement
on the OnOpen event of the subreports to determine what
OpenArgs choice is made and then how to sort it, by drug
or date. So far, so good.

I tried sorting using the Me.OrderBy = "drug name, date"
or "date, drug name", and I get the error "2101 - the
setting you entered isn't valid for this property". OK,
the syntax appears correct, so I looked around and didn't
find much on what I needed to do to change it.

Someone suggested changing the recordsource, so I tried
that, adding an "ORDER BY drug, date" onto the string for
the record source, and the code runs, it just doesn't
complete the sort in the way I ask it (for example, when
I ask it to sort by drung name it doesn't sort by drug
name, asc or desc, it sorts by date every time).
Click to expand...


Several issues going on here. First, anytime you use a name
that contains a space or other funky character, you must
enclose the name in square brackets [ ] E.g.

Me.OrderBy = "[drug name], date"

The next issue is that Date is the name of an Access
function. Your use of it as a field name is bound to
confuse either you and/or Access. Although you can
usually(?) get around this issue by enclosing it in square
brackets, you should change the field's name in the table
(better now than later when you're under the gun).

Another issue is that the record source query's Order By
clause and the report's OrderBy property are overridden by
the Sorting and Grouping settings.

A fourth issue is that a subreport's Open event fires for
each instance in the main report, BUT you can only change
several of the subreport's properties (Filter, OrderBy,
RecordSource, etc) the first time it's opened.

So, the way to do what you want is to first use Sorting and
Grouping to specify a sort level on a field, it doesn't
matter which field since it'll be changed when the
subreports are opened. With that in place, you can use the
subreports' Open event to set the sorting level's
ControlSource property

Dim Initialized As Boolean

Sub Report_Open()
Dim strSortField As String

If Not Initialized Then
' your code to determine the sorting field from
' Me.Parent.OpenArgs
strSortField = ???
Me.GroupLevel(N).ControlSource = strSortField
Initialized = True
End If
End Sub

The N above needs to be replaced by the level (zero based)
you're using for the sorting field.
 
Sorry about the naming - I sometimes rename items when I
post questions in hopes of clarifying the issue and
sometimes I muddy the waters.

....drug name is actually DrugCodeDesc - no need to []
....date is actually StartDate - no conflicts within Access

So, with that clarified I tested your code and it seems
to work in some aspects. I guess I not sure what the
initialize does, but I can look that up later.

What I didn't post here (posted on another thread), and
now comes into play, was that I already have a dummy
header as a group level in order to have a header on the
subreport when it spills out onto another page of the
main report. With the code you describe here, I then get
the header printed out numerous times within the
subreport when I then sort by either DrugCodeDesc or
StartDate

Isn't there a way to manipulate the OrderBy on a
supreport, or perhaps change the recordsource? (both of
which I tried and have not been too successful)

Thanks for your help and patience!

P.S. I also responded to another thread on this topic,
please disregard that thread and let's continue here.

-----Original Message-----
Phil said:
I have a report with three subreports. The user runs the
report from a form and based on a choice within an option
group, the user requests sort by either drug name or by
date.

I use the OpenArgs during the OpenReport process to pass
the option group choice, and I have an if/then statement
on the OnOpen event of the subreports to determine what
OpenArgs choice is made and then how to sort it, by drug
or date. So far, so good.

I tried sorting using the Me.OrderBy = "drug name, date"
or "date, drug name", and I get the error "2101 - the
setting you entered isn't valid for this property". OK,
the syntax appears correct, so I looked around and didn't
find much on what I needed to do to change it.

Someone suggested changing the recordsource, so I tried
that, adding an "ORDER BY drug, date" onto the string for
the record source, and the code runs, it just doesn't
complete the sort in the way I ask it (for example, when
I ask it to sort by drung name it doesn't sort by drug
name, asc or desc, it sorts by date every time).
Click to expand...


Several issues going on here. First, anytime you use a name
that contains a space or other funky character, you must
enclose the name in square brackets [ ] E.g.

Me.OrderBy = "[drug name], date"

The next issue is that Date is the name of an Access
function. Your use of it as a field name is bound to
confuse either you and/or Access. Although you can
usually(?) get around this issue by enclosing it in square
brackets, you should change the field's name in the table
(better now than later when you're under the gun).

Another issue is that the record source query's Order By
clause and the report's OrderBy property are overridden by
the Sorting and Grouping settings.

A fourth issue is that a subreport's Open event fires for
each instance in the main report, BUT you can only change
several of the subreport's properties (Filter, OrderBy,
RecordSource, etc) the first time it's opened.

So, the way to do what you want is to first use Sorting and
Grouping to specify a sort level on a field, it doesn't
matter which field since it'll be changed when the
subreports are opened. With that in place, you can use the
subreports' Open event to set the sorting level's
ControlSource property

Dim Initialized As Boolean

Sub Report_Open()
Dim strSortField As String

If Not Initialized Then
' your code to determine the sorting field from
' Me.Parent.OpenArgs
strSortField = ???
Me.GroupLevel(N).ControlSource = strSortField
Initialized = True
End If
End Sub

The N above needs to be replaced by the level (zero based)
you're using for the sorting field.
Click to expand...
 
phil said:
Sorry about the naming - I sometimes rename items when I
post questions in hopes of clarifying the issue and
sometimes I muddy the waters.

...drug name is actually DrugCodeDesc - no need to []
...date is actually StartDate - no conflicts within Access
Click to expand...

Well that's good to hear, but we could have avoided the
confusion if you had just used the real names in the
question.

So, with that clarified I tested your code and it seems
to work in some aspects. I guess I not sure what the
initialize does, but I can look that up later.
Click to expand...

That's a module level variable and it's used to prevent the
code from running more than once. Actually, now that I
think about it a little, it would be cleaner to make it a
Static variable within the procedure,

What I didn't post here (posted on another thread), and
now comes into play, was that I already have a dummy
header as a group level in order to have a header on the
subreport when it spills out onto another page of the
main report. With the code you describe here, I then get
the header printed out numerous times within the
subreport when I then sort by either DrugCodeDesc or
StartDate
Click to expand...

You were supposed to create a new sorting level **below**
any existing groups. If you already have one group
(GroupLevel zero), then the new sorting level would be 1
(the N in my earlier post).

Isn't there a way to manipulate the OrderBy on a
supreport, or perhaps change the recordsource? (both of
which I tried and have not been too successful)
Click to expand...

Like I said before, Sorting and Grouping takes precedence
over those other sorting techniques.

-----Original Message-----
Phil said:
I have a report with three subreports. The user runs the
report from a form and based on a choice within an option
group, the user requests sort by either drug name or by
date.

I use the OpenArgs during the OpenReport process to pass
the option group choice, and I have an if/then statement
on the OnOpen event of the subreports to determine what
OpenArgs choice is made and then how to sort it, by drug
or date. So far, so good.

I tried sorting using the Me.OrderBy = "drug name, date"
or "date, drug name", and I get the error "2101 - the
setting you entered isn't valid for this property". OK,
the syntax appears correct, so I looked around and didn't
find much on what I needed to do to change it.

Someone suggested changing the recordsource, so I tried
that, adding an "ORDER BY drug, date" onto the string for
the record source, and the code runs, it just doesn't
complete the sort in the way I ask it (for example, when
I ask it to sort by drung name it doesn't sort by drug
name, asc or desc, it sorts by date every time).
Click to expand...


Several issues going on here. First, anytime you use a name
that contains a space or other funky character, you must
enclose the name in square brackets [ ] E.g.

Me.OrderBy = "[drug name], date"

The next issue is that Date is the name of an Access
function. Your use of it as a field name is bound to
confuse either you and/or Access. Although you can
usually(?) get around this issue by enclosing it in square
brackets, you should change the field's name in the table
(better now than later when you're under the gun).

Another issue is that the record source query's Order By
clause and the report's OrderBy property are overridden by
the Sorting and Grouping settings.

A fourth issue is that a subreport's Open event fires for
each instance in the main report, BUT you can only change
several of the subreport's properties (Filter, OrderBy,
RecordSource, etc) the first time it's opened.

So, the way to do what you want is to first use Sorting and
Grouping to specify a sort level on a field, it doesn't
matter which field since it'll be changed when the
subreports are opened. With that in place, you can use the
subreports' Open event to set the sorting level's
ControlSource property

Dim Initialized As Boolean

Sub Report_Open()
Dim strSortField As String

If Not Initialized Then
' your code to determine the sorting field from
' Me.Parent.OpenArgs
strSortField = ???
Me.GroupLevel(N).ControlSource = strSortField
Initialized = True
End If
End Sub

The N above needs to be replaced by the level (zero based)
you're using for the sorting field.
Click to expand...
Click to expand...
 
A pox upon me for being such a clumsy lout - I'll put
real names from no on...

Thanks for your help - there is just one last thing (of
course).

This works fine for sort by either StartDate or
DrugCodeDesc, but my particular need is to sort by both
(DrugCodeDesc, StartDate) and (StartDate, DrugCodeDesc).
It doesn't seem to function well with this.

***I have, in my subreport, the following sections:

Dummy Header - has all the column headings, list as a
group, asc order

DrugCodeDesc - 0" height section, listed as a group, asc
order

StartDate - 0" height section, listed as a group, desc
order

Detail - has the fields to print


***And - here is my code on the when clicking the print
report button:

DoCmd.OpenReport "rptMedications", acViewPreview, , " &
Forms![frmPatientRecord].txtPatient Number & """", ,
Me.opgMedRpt.Value


***And - here is the code in the subreport:

Dim Initialized As Boolean

Private Sub Report_Open(Cancel As Integer)
Dim strSortField1 As String
Dim strSortField2 As String

If Not Initialized Then
' determine the sorting field from
' Me.Parent.OpenArgs
If Reports![rptMedications].OpenArgs = 1 Then
strSortField1 = "DrugCodeDesc"
strSortField2 = "StartDate"
Else
strSortField1 = "StartDate"
strSortField2 = "DrugCodeDesc"
End If

Me.GroupLevel(1).ControlSource = strSortField1
Me.GroupLevel(2).ControlSource = strSortField2

Initialized = True
End If

End Sub


***Results***
Order is fine for OpenArg = 1 (in DrugCodeDesc order),
but for second option does not print out startdate order.

Again - thanks for your help. I've been chewing my arm
off on this one...


-----Original Message-----
phil said:
Sorry about the naming - I sometimes rename items when I
post questions in hopes of clarifying the issue and
sometimes I muddy the waters.

...drug name is actually DrugCodeDesc - no need to []
...date is actually StartDate - no conflicts within
Click to expand...
Access

Well that's good to hear, but we could have avoided the
confusion if you had just used the real names in the
question.

So, with that clarified I tested your code and it seems
to work in some aspects. I guess I not sure what the
initialize does, but I can look that up later.
Click to expand...

That's a module level variable and it's used to prevent the
code from running more than once. Actually, now that I
think about it a little, it would be cleaner to make it a
Static variable within the procedure,

What I didn't post here (posted on another thread), and
now comes into play, was that I already have a dummy
header as a group level in order to have a header on the
subreport when it spills out onto another page of the
main report. With the code you describe here, I then get
the header printed out numerous times within the
subreport when I then sort by either DrugCodeDesc or
StartDate
Click to expand...

You were supposed to create a new sorting level **below**
any existing groups. If you already have one group
(GroupLevel zero), then the new sorting level would be 1
(the N in my earlier post).

Isn't there a way to manipulate the OrderBy on a
supreport, or perhaps change the recordsource? (both of
which I tried and have not been too successful)
Click to expand...

Like I said before, Sorting and Grouping takes precedence
over those other sorting techniques.

-----Original Message-----
Phil wrote:

I have a report with three subreports. The user runs the
report from a form and based on a choice within an option
group, the user requests sort by either drug name or by
date.

I use the OpenArgs during the OpenReport process to pass
the option group choice, and I have an if/then statement
on the OnOpen event of the subreports to determine what
OpenArgs choice is made and then how to sort it, by drug
or date. So far, so good.

I tried sorting using the Me.OrderBy = "drug name, date"
or "date, drug name", and I get the error "2101 - the
setting you entered isn't valid for this property". OK,
the syntax appears correct, so I looked around and didn't
find much on what I needed to do to change it.

Someone suggested changing the recordsource, so I tried
that, adding an "ORDER BY drug, date" onto the string for
the record source, and the code runs, it just doesn't
complete the sort in the way I ask it (for example, when
I ask it to sort by drung name it doesn't sort by drug
name, asc or desc, it sorts by date every time).


Several issues going on here. First, anytime you use
Click to expand...
a
name
that contains a space or other funky character, you must
enclose the name in square brackets [ ] E.g.

Me.OrderBy = "[drug name], date"

The next issue is that Date is the name of an Access
function. Your use of it as a field name is bound to
confuse either you and/or Access. Although you can
usually(?) get around this issue by enclosing it in square
brackets, you should change the field's name in the table
(better now than later when you're under the gun).

Another issue is that the record source query's Order By
clause and the report's OrderBy property are
Click to expand...
overridden
by
the Sorting and Grouping settings.

A fourth issue is that a subreport's Open event fires for
each instance in the main report, BUT you can only change
several of the subreport's properties (Filter, OrderBy,
RecordSource, etc) the first time it's opened.

So, the way to do what you want is to first use
Click to expand...
Sorting
and
Grouping to specify a sort level on a field, it doesn't
matter which field since it'll be changed when the
subreports are opened. With that in place, you can
Click to expand...
use
the
subreports' Open event to set the sorting level's
ControlSource property

Dim Initialized As Boolean

Sub Report_Open()
Dim strSortField As String

If Not Initialized Then
' your code to determine the sorting field from
' Me.Parent.OpenArgs
strSortField = ???
Me.GroupLevel(N).ControlSource = strSortField
Initialized = True
End If
End Sub

The N above needs to be replaced by the level (zero based)
you're using for the sorting field.
Click to expand...
Click to expand...
Click to expand...
 
phil said:
This works fine for sort by either StartDate or
DrugCodeDesc, but my particular need is to sort by both
(DrugCodeDesc, StartDate) and (StartDate, DrugCodeDesc).
It doesn't seem to function well with this.

***I have, in my subreport, the following sections:

Dummy Header - has all the column headings, list as a
group, asc order

DrugCodeDesc - 0" height section, listed as a group, asc
order

StartDate - 0" height section, listed as a group, desc
order

Detail - has the fields to print


***And - here is my code on the when clicking the print
report button:

DoCmd.OpenReport "rptMedications", acViewPreview, , " &
Forms![frmPatientRecord].txtPatient Number & """", ,
Me.opgMedRpt.Value


***And - here is the code in the subreport:

Dim Initialized As Boolean

Private Sub Report_Open(Cancel As Integer)
Dim strSortField1 As String
Dim strSortField2 As String

If Not Initialized Then
' determine the sorting field from
' Me.Parent.OpenArgs
If Reports![rptMedications].OpenArgs = 1 Then
strSortField1 = "DrugCodeDesc"
strSortField2 = "StartDate"
Else
strSortField1 = "StartDate"
strSortField2 = "DrugCodeDesc"
End If

Me.GroupLevel(1).ControlSource = strSortField1
Me.GroupLevel(2).ControlSource = strSortField2

Initialized = True
End If

End Sub


***Results***
Order is fine for OpenArg = 1 (in DrugCodeDesc order),
but for second option does not print out startdate order.
Click to expand...


I have played around with this scenario for hours and can
not get it to mess up. Could you provide more details? Are
you sure the start date field is a Date/Time field and not a
Text field? Maybe a sample of the report's output with
notes about what's wrong with it might help.
 
Excellent! I figured what the issue was. My second
group of the subreport, DrugCodeDesc, was group on Prefix
Characters, and when I set it to sort on Each Value, the
code below worked like a charm. I guess in Friday's mad
scramble for clarity I switched this.

Not sure why it won't work with prefix character as the
group on. Something to store and investigate for later.

Thanks for your help, Marsh!

-----Original Message-----
Thanks for your time into this - yes, the field StartDate
is a Date/Time field. How can I attach a sample of the
report?

-----Original Message-----
phil said:
This works fine for sort by either StartDate or
DrugCodeDesc, but my particular need is to sort by both
(DrugCodeDesc, StartDate) and (StartDate, DrugCodeDesc).
It doesn't seem to function well with this.

***I have, in my subreport, the following sections:

Dummy Header - has all the column headings, list as a
group, asc order

DrugCodeDesc - 0" height section, listed as a group, asc
order

StartDate - 0" height section, listed as a group, desc
order

Detail - has the fields to print


***And - here is my code on the when clicking the print
report button:

DoCmd.OpenReport "rptMedications", acViewPreview, , " &
Forms![frmPatientRecord].txtPatient Number & """", ,
Me.opgMedRpt.Value


***And - here is the code in the subreport:

Dim Initialized As Boolean

Private Sub Report_Open(Cancel As Integer)
Dim strSortField1 As String
Dim strSortField2 As String

If Not Initialized Then
' determine the sorting field from
' Me.Parent.OpenArgs
If Reports![rptMedications].OpenArgs = 1 Then
strSortField1 = "DrugCodeDesc"
strSortField2 = "StartDate"
Else
strSortField1 = "StartDate"
strSortField2 = "DrugCodeDesc"
End If

Me.GroupLevel(1).ControlSource = strSortField1
Me.GroupLevel(2).ControlSource = strSortField2

Initialized = True
End If

End Sub


***Results***
Order is fine for OpenArg = 1 (in DrugCodeDesc order),
but for second option does not print out startdate
Click to expand...
order.


I have played around with this scenario for hours and can
not get it to mess up. Could you provide more
Click to expand...
details?
Are
you sure the start date field is a Date/Time field and not a
Text field? Maybe a sample of the report's output with
notes about what's wrong with it might help.
Click to expand...
.
Click to expand...
 
Excellent! I figured what the issue was. My second
group of the subreport, DrugCodeDesc, was group on Prefix
Characters, and when I set it to sort on Each Value, the
code below worked like a charm. I guess in Friday's mad
scramble for clarity I switched this.

Not sure why it won't work with prefix character as the
group on. Something to store and investigate for later.

Thanks for your help, Marsh!
Click to expand...


Great news Phil, glad I could help.
 
Back
Top