OrderBy in Reports does not work

[Gerry Verschuuren]

In Access 2007 VBA, I try to sort a Report by using OrderBy. These are the
steps:
1. In a form, a button click calls the report with the sort field in OpenArgs
Sub cmdOne_Click()
DoCmd.OpenReport sReport,,,sFilter,,sSort
2. In the report, I try to sort the field as passed thru OpenArgs:
Sub Report_Open()
Me.OrderBy = OpenArgs
Me.OrderByOn = True

Why do I not get the proper sorting order?

Thanks in advance for your response.

Gerry

 

[Marshall Barton]

In Access 2007 VBA, I try to sort a Report by using OrderBy. These are the
steps:
1. In a form, a button click calls the report with the sort field in OpenArgs
Sub cmdOne_Click()
DoCmd.OpenReport sReport,,,sFilter,,sSort
2. In the report, I try to sort the field as passed thru OpenArgs:
Sub Report_Open()
Me.OrderBy = OpenArgs
Me.OrderByOn = True

Why do I not get the proper sorting order?

I don't know, unless you have something/anything specified
in Sorting and Grouping (which takes precedence over the
OrderBy property).

 

[Gerry Verschuuren]

No, Sorting-and-Grouping is clear. Perhaps I should use that feature but then
in VBA. However, I have no grouping in that report.

 

[Gina Whipp]

Gerry,

What does sSort translate to? In other words, where and what is that?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Marshall Barton]

As Gina points out, I presumed you had verified that
openargs contains the **name** of the field that you want
the sort to use. If not, set a breakpoint in the report's
Open event and double check what is placed in the OrderBy
property.

I always use Sorting and Grouping to sort reports. The
trick in your case is to precreate a sorting level so there
is a place to put the field name. The code would be like:

Me.GroupLevel(0).ControlSource = Me.OpenArgs

Again, OpenArgs must contain the name of the record source
field to sort by.

If you have some situation where you do not want the report
to be sorted, then provide more details about what you are
doing to put a value in OpenArgs.

 

[Gerry Verschuuren]

sSort is s String type variable that holds the name of a field (e.g. LastName)

 

[Gerry Verschuuren]

sSort is a string type variable with an existing field name in it. I had
tried breakpoints already long ago, and they do show the correct field name.
The name even show up later on in the properties listing of the report.

I guess I have to try the grouplevel option, but I still wonder why my way
wouldn't work. But thanks for that alternative.

As Gina points out, I presumed you had verified that
openargs contains the **name** of the field that you want
the sort to use. If not, set a breakpoint in the report's
Open event and double check what is placed in the OrderBy
property.

I always use Sorting and Grouping to sort reports. The
trick in your case is to precreate a sorting level so there
is a place to put the field name. The code would be like:

Me.GroupLevel(0).ControlSource = Me.OpenArgs

Again, OpenArgs must contain the name of the record source
field to sort by.

If you have some situation where you do not want the report
to be sorted, then provide more details about what you are
doing to put a value in OpenArgs.
--
Marsh
MVP [MS Access]

Sorting-and-Grouping is clear. Perhaps I should use that feature but then
in VBA. However, I have no grouping in that report.

 

[Gina Whipp]

Gerry,

What I was hoping to see was...

Dim sSort As String
sSort = txtLastName (or something like that)

And while you're at it what is sFilter. Now you can go on to use the
Grouping and Sorting BUT if you're interested, it ight be worth discovering
why your original way did not work.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Douglas J. Steele]

Hopefully it would be

sSort = "txtLastName" (or something like that)

If you had sSort = txtLastName, then sSort would contain the value contained
in txtLastName, not the name of the field itself!

 

[Gina Whipp]

Oops... correct.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gerry Verschuuren]

What I actually had was this:
Sub Button_Click
Dim sSort As String
sSort = ComboOne (which has a listing of field names)
End Sub

It seems to me that would be correct. Yet, OrderBy sSort won't work (unless
I use GroupLevel(0).Controlsource = sSort)

 

[Gina Whipp]

And what is sFilter? Though I must say I have never tried to set a Sort
using a Combo Box. Is it bound or unbound?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gerry Verschuuren]

sFilter is a string like "LastName='XYZ'"
I actually did bind it to a combobox and a textbox: Combo1 & "='" & Text1 &
"'"