Set Order by

Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form keeps the
new order which causes problems when the form is opened the next time in the
wrong order.

On Sun, 11 Jul 2004 21:21:40 -0400, "Berny"
wrote:

Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form keeps the
new order which causes problems when the form is opened the next time in the
wrong order.


Assuming that your order is set in the Record Source, then just set
the OrderBy to an empty string. (By default Access keeps the last
order set)
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = ""
End Sub

If you want to set an order on open.

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "SomeField"
Me.OrderByOn = True
End Sub

- Jim

Are you allowing the users to change the *design* of the Form?

This is not recommended in fully developed databases.

--
HTH
Van T. Dinh
MVP (Access)


"Berny" wrote in message
...
Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form keeps the
new order which causes problems when the form is opened the next time in
the
wrong order.

"Van T. Dinh" wrote in
message
Are you allowing the users to change the *design* of the Form?

This is not recommended in fully developed databases.


"Berny" wrote in message
...
Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form
keeps the new order which causes problems when the form is opened
the next time in the wrong order.

But Van, you can't stop the users from clicking the "Sort
Ascending/Descending" toolbar buttons unless you modify or replace that
toolbar. When they do that, the form's OrderBy property is set, and
saved when the form is closed. All you can do to prevent it is clear
that property or set it yourself when the form is opened.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Van T. Dinh" wrote
Are you allowing the users to change the *design* of the Form?

This is not recommended in fully developed databases.


"Berny" wrote
Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form
keeps the new order which causes problems when the form is opened
the next time in the wrong order.

Dirk Goldgar wrote:
But Van, you can't stop the users from clicking the "Sort
Ascending/Descending" toolbar buttons unless you modify or replace that
toolbar. When they do that, the form's OrderBy property is set, and
saved when the form is closed. All you can do to prevent it is clear
that property or set it yourself when the form is opened.


IMO, this is just about the most anoying "feature" ever
invented.

I **think** you can avoid the form save by providing a
command button that explicitly uses:
DoCmd.Close acForm, Me.Name, acSaveNo

I usually disable the form's Control and [X] Close buttons
on the title bar, but unless the main menu is customized, I
don't go so far as to disable its Close menu item.

--
Marsh
MVP [MS Access]

Thank you

That was what I was looking for

"Jim Allensworth" wrote in message
...
On Sun, 11 Jul 2004 21:21:40 -0400, "Berny"
wrote:

Can anyone tell me how to set the order by of a form when it opens?

I created a form with an order by; users change it and the form keeps the
new order which causes problems when the form is opened the next time in
the
wrong order.


Assuming that your order is set in the Record Source, then just set
the OrderBy to an empty string. (By default Access keeps the last
order set)
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = ""
End Sub

If you want to set an order on open.

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "SomeField"
Me.OrderByOn = True
End Sub

- Jim

Thanks, Marsh.

acSaveNo is what I have used in the "Close" button. My users never
complained of this problem even those some of them use "Sort".

Will have to do some more test on this.

--
Cheers
Van T. Dinh
MVP (Access)


"Marshall Barton" wrote in message
...


IMO, this is just about the most anoying "feature" ever
invented.

I **think** you can avoid the form save by providing a
command button that explicitly uses:
DoCmd.Close acForm, Me.Name, acSaveNo

I usually disable the form's Control and [X] Close buttons
on the title bar, but unless the main menu is customized, I
don't go so far as to disable its Close menu item.

--
Marsh
MVP [MS Access]

Van T. Dinh wrote:

Thanks, Marsh.

acSaveNo is what I have used in the "Close" button. My users never
complained of this problem even those some of them use "Sort".

Will have to do some more test on this.


Van,

Please let us know what your test's do or don't tell you. I
have been very curious about this issue ever since I had a
client that had their app made unusable because Access
somehow or other managed to save a filter/orderby that was
illegal (preventing the form from opening).

Makes me wish that MS had never invented this "feature".

I also have a nagging suspicion that Access isn't really
saving the entire form when it saves the Filter and OrderBy
properties, because the bloat issue might be pretty severe.
--
Marsh
MVP [MS Access]

"Marshall Barton" wrote in message


Van,

Please let us know what your test's do or don't tell you. I
have been very curious about this issue ever since I had a
client that had their app made unusable because Access
somehow or other managed to save a filter/orderby that was
illegal (preventing the form from opening).

I had that happen because the client clicked the Save toolbar button,
thinking to save the record she'd been editing, after my code had opened
the form in Data Entry mode. She phoned me up to say "all my records
have disappeared!" My fault for giving her full menus.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)