How to sort a report in code? 1

[marcello62]

Hi there,

I have an MSAccess 2000 database with a report which I want to sort in code. I converted this database from MS Access 97 to MS Access 2000. The report has the query queryname as it's recordsource. When it ran under MS Access 97, I sorted the report in code by setting the orderByOn property to true and the orderBy property to the string "queryname.queryfieldname". To get this code running without runtime errors under 2000 I had to change it to " queryname![queryfieldname] ". I did get no run-time errors after doing so, but the sorting did not take place anymore.
When clicking the left square button in report design mode, you get a context menu containing the option Sorting and Grouping. By selecting the appropriate field after activating this menu I get the report sorted again, but now I want to do this programmatically. Does anyone know how to do this?
Any help will be greatly appreciated.
Thanks.

 

[genomon]

Sorry haven't the time right now to be more descriptive; do an Access help search on Me.GroupLevel(0).ControlSource and Me.GroupLevel(0).SortOrder properties......

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[Eupher]

Hi, marcello62,

Since the report is bound to queryname, seems superfluous to include it in the QueryBy property. Have you tried simply "queryfieldname"?

Another option would be to dynamically change the SQL of the query to include the ORDER BY statement you wish.

Ken S.

 

[marcello62]

Hi Genomon and Eupher,

Thanks for the tips. I started with the most simple one, which was just Eupher's specifying queryfieldname in stead of [query]!queryfieldname, and it worked!! Thanks very much Eupher, and Genomon also, I am certainly going to try out your tip also, I'm very curious if and how this works.

 

[dhookom]

Eupher's solution isn't very robust. Reports don't generally obey the record source's sort order.

It is always best to set the grouplevel control sources. Allen Browne has sample code at

http://www.allenbrowne.com/ser-33.html.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Eupher]

I respectfully defer to Duane's superior expertise on this - my reporting skills are, well... not my strongest suit. I would like to correct a typo in my earlier post:

seems superfluous to include it in the QueryBy property

should be:

seems superfluous to include it in the [red]OrderBy[/red] property

...but you probably figured that out. And I retract the bit about setting the sort order in the underlying query. Bad advice there.

A question of my own... Is setting the the OrderByOn and OrderBy properties in code necessarily a bad idea (if the sort is simple, and no grouping is needed)? I understand that anything more complex would require the GroupLevel control sources...

Ken S.

 

[dhookom]

Setting the Order By property and OrderByOn will correctly sort the report only if there is nothing in the Sorting and Grouping dialog. Sorting and Grouping always "trumps" all other sorting.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Eupher]

Got it, Duane, thanks for the explanation.

Ken S.