Set Order by

  • Thread starter Thread starter Berny
  • Start date Start date
B

Berny

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.
 
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.
Click to expand...
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.
 
Van T. Dinh said:
Are you allowing the users to change the *design* of the Form?

This is not recommended in fully developed databases.
Click to expand...

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.
 
"Van T. Dinh" wrote
Dirk said:
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.
Click to expand...


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.
 
Thank you

That was what I was looking for

Jim Allensworth said:
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
Click to expand...
 
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 said:
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.
Click to expand...


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.
 
Marshall Barton said:
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).
Click to expand...

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.
 
Back
Top