By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,695 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,695 IT Pros & Developers. It's quick & easy.

Prevent OrderBy from sticking?

P: n/a
By default in more recent versions, Access forms keep users' last sort
request in the "orderby" property. That can slow things down
considerably, especially when the last user has chosen a non-indexed
field.

I have put in a lot of
OrderBy = ""
code in OnOpen form events. Being able to disable that "service"
(seems like more and more Microsoft insists on helping when we don't
need or want the help) would be a much more efficient solution.

Ideally, this solution would also prevent query's "orderby" property
form keeping sort requests that are not explicitly included in the
query design.

Any ideas?

P. Emigh
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The OrderBy property "sticks" only when the form was opened in design
view - then form view & a filter/sort applied - then saved. To fix:
open form in design view & clear out the Filter & OrderBy properties.
Save the form. This should remove any filter/sort when the form is
reopened in form view.

Alternatively:
Right-click on the form, when it is open, select "Remove Filter/Sort."

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP84zyIechKqOuFEgEQIwqQCbBY9i4FcwwvYpeIXROX+mW8 8EUAIAnRto
TG5Qs5v2Zo7UbtnkjZ+6wek6
=PHRg
-----END PGP SIGNATURE-----

P. Emigh wrote:
By default in more recent versions, Access forms keep users' last sort
request in the "orderby" property. That can slow things down
considerably, especially when the last user has chosen a non-indexed
field.

I have put in a lot of
OrderBy = ""
code in OnOpen form events. Being able to disable that "service"
(seems like more and more Microsoft insists on helping when we don't
need or want the help) would be a much more efficient solution.

Ideally, this solution would also prevent query's "orderby" property
form keeping sort requests that are not explicitly included in the
query design.

Any ideas?

P. Emigh

Nov 12 '05 #2

P: n/a
The theory that:
The OrderBy property "sticks" only when the form was opened in design
view - then form view & a filter/sort applied - then saved.


is simply not true in my situation.

I just verified this by setting up a little test database in Access
2000. If there's nothing in the Order By property, opening the form
in normal view and sorting will cause the Order By property to be set.
It persists without any confirmation message.

If you can't replicate this behavior, then there's apparently some
setting that differs, and that's what I'm looking for.

P. Emigh
Nov 12 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Confirmation of your first post observations.

I've just tested a form in Access XP and found that the OrderBy
property does stick w/o any logical reasons.

I had set up a Me.OrderBy = "" in the both the form's OnClose and
OnLoad events, but the form continued to open in the form's previously
opened, user-selected order! It would only revert to the default (no
Order By) if I opened the form in Design View (the OrderBy property
was then empty) and then into Form View. This is strange behavior -
especially since the Me.OrderBy = "" doesn't seem to have any effect.
I even put in a Me.Requery after the Me.OrderBy command - same
results: no effect.

Why doesn't a valid command, which doesn't error on running, have any
effect? Is this a possible bug in Access?

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9eIoIechKqOuFEgEQJ9sQCgrwK/MNvB9ZTAT6wFmqfJSYgbsa0AoIRc
b3H+lPMnRqj2JpuiLS4a+bph
=y4DV
-----END PGP SIGNATURE-----

P. Emigh wrote:
The theory that:
The OrderBy property "sticks" only when the form was opened in design
view - then form view & a filter/sort applied - then saved.

is simply not true in my situation.

I just verified this by setting up a little test database in Access
2000. If there's nothing in the Order By property, opening the form
in normal view and sorting will cause the Order By property to be set.
It persists without any confirmation message.

If you can't replicate this behavior, then there's apparently some
setting that differs, and that's what I'm looking for.

P. Emigh


Nov 12 '05 #4

P: n/a
The problem is even more insidious than I realized when I first posted
it. I've now seen a situation in which coding the OrderBy and
OrderByOn properties causes Access to ignore the Where condition in
the OpenForm command.

Still looking for solutions,

P. Emigh
Nov 12 '05 #5

P: n/a
More investigations:

Microsoft recommends putting
me.OrderBy = ""
in the OnOpen event to prevent the previous sort from sticking.

In my example, setting the OrderBy for a SUBFORM, either in the parent
form's OnOpen event or the subform's OnOpen event, caused the WHERE
condition to be ignored when opening the parent form with the OpenForm
command.

I tried to replicate the situation in a small sample database, and was
not able to do that. Perhaps there's some interaction with other code
in my primary example that causes the problem.

So, my "solution" to the critical problem -- the Where statement being
ignored -- is to remove the OrderBy code for the subform. That's not
too big a problem in this instance since there are only a few records
in the subform.

And apparently the "solution" to Microsoft's persistent sort is to
remember to put that me.OrderBy = "" code in the OnOpen event of every
form where the sort slows things down. Unless it breaks other code.
Chalk it up to another instance of "Thanks, but no thank, Microsoft."

P. Emigh
Nov 12 '05 #6

P: n/a
TC

"P. Emigh" <pe****@mindspring.com> wrote in message
news:6d**************************@posting.google.c om...

(snip)
In my example, setting the OrderBy for a SUBFORM, either in the parent
form's OnOpen event or the subform's OnOpen event, caused the WHERE
condition to be ignored when opening the parent form with the OpenForm
command.


Jumping in here:

(1) Main form and subform events do not fire in the expected common-sense
order when the two forms open. The subform events first first, then the main
form ones. This can lead to tons of problems if you assume they fire in the
opposite order.

(2) You may have more luck using: Me.OrderByOn=False (not Me.OrderBy="").
Put that in the main form >and< subform Form_Open events, & see if that
works.

HTH,
TC

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.