Access 2010 report orderby property no longer applies to open report if there are existing group level sorts?

Question

I am working on a database that was written in Access 2003. It has reports which when open can have the sorting adjusted via the OrderBy and OrderByOn properties in code. This feature of Access 2003 seems to have been broken in Access 2010 (the version that the db is now being run in) if the report has GroupLevel sorts defined. The OrderBy setting is ignored. I found one discussions of this issue 'out there' but that's it (per that discussion this loss of functionality may have started with Access 2007).

Has anyone else run into this and do you have a workaround?

One possibility might be to delete the grouplevel sort orders; but that has to be done when the report is closed, not while it is running. The old behavior was great...I'd like to keep it if possible, without closing and reopening the report.

Ah - just occurred to me while writing this out - for these particular reports, there is no grouping set on the GroupLevel property, only sorting. So I might be able to remove the grouplevel sorts that are defined for the report by default, and use the OrderBy property instead. Hopefully the orderby settings can be bumped at runtime via code...?

Interested in any input on this.

Answer 1

So far my tests show that if there is a saved OrderBy value for the report, OrderBy values applied via vba are not applied. However, if there is no saved OrderBy property value, setting OrderBy via vba is effective.

Answer 2

Hello,

Does "group level sorts" mean

I test with Office 2007 and 2010. The result shows that the sort would take effect no matter what I set for OrderBy manually or set by code.

Could you share detail repro steps here?

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Yes that's what I meant by group level sort.

The detailed steps to reproduce...the db which I discovered this issue with is not possible to share. It would be interesting if you shared the db you have already created to test it. I could at least see if we're testing the same features.

Answer 4

Hello,

It seems that the issue could not be reproduced using other new database. Do you create a new db to test if the issue exists?

Here is my file.  https://1drv.ms/u/s!AkcxDWH1nFmJpUzUSrOZZUWmk9Y6 I add a sort from the ribbon. Then try to edit the property OrderBy manually or code.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

I only get a blank page via that link.

Answer 6

Setting a report's OrderBy property, whether at design time or in code at run time, has never had any effect if the report has sorting or grouping specified by GroupLevels, as the specified sorting & grouping overrides whatever may be specified in the OrderBy property. In such cases, if you want to modify the sorting & grouping at run time, you have to modify the group levels themselves.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 7

Hello,

You could copy/paste to open the URL, then it would download the Access DB. 

What Dirk said is same as my test result. 

Please create a new form or new database to test if your issue causes from some custom code. 

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

The "I" was included in your link; that didn't work of course. I didn't notice the first time around.

Answer 9

I experience what you both describe in the Celeste example db, and in a test db I made from scratch using Access 2003. OrderBy followed by OrderByOn = True does not resort the report if there is a grouplevel sort in place.

However, it is a fact that the re-sort behavior I have described DOES work in a db that has been in production for over a decade. I had never personally tried to ORDERBY when a GroupLevel sort was in place, someone else wrote this database. But after the db was moved to run under Access 2010 (mdb) the orderby stopped working and I heard about it from the end users. It's completely reproducable in the db which I cannot share. The orderby invocation does overrule the grouplevel sort when run under Access 2003, but does not when run under Access 2010.

I have a workaround for the immediate need. If I can ever share a test db that shows the behavior I will.

Answer 10

However, it is a fact that the re-sort behavior I have described DOES work in a db that has been in production for over a decade. [...] It's completely reproducable in the db which I cannot share. The orderby invocation does overrule the grouplevel sort when run under Access 2003, but does not when run under Access 2010.

I have a workaround for the immediate need. If I can ever share a test db that shows the behavior I will.

I would very much like to see a reproduction scenario, if you are ever able to create one.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html