Setting an Initial Sort Order on Form & Re-sort 1

[gallas]

I have a subform which is for view only (Legacy Job Info for users reference) reading rows from an underlying query. (Subform not linked to main form)The query is based on a single table. Two fields which are not shown on the form but are in the query comprise the composite primary key. The query sorts on one half of the key - the numeric field. A third field is a calculated field which displays to the user the two key fields together.

My problem is that although I want the users to be able to change the sort order using the sort buttons at form level, when the form is closed & re-opened I want the query sort order restored. In addition they need a cmd button on the form to reset the sort(default)order whilst viewing the form because they do not have access to the field to be sorted on (maybe something like clear Orderby value & requery?).

(The same problems seem to arise with a main form and single field primary key - so those bits are probably irrelevant)

Any suggestions?

 

[FancyPrairie]

I may be over simplifying this, but this is how I do it. All my forms display a toolbar that contains only the items I want the user to use. One of these items (controls) is the A-Z and Z-A buttons (which allows the user to sort either ascending or descending). All the user has to do is to select the field they want to sort on and press one of the buttons. I don't have to do any coding. Access takes care of it. When the user closes the form and then opens it again, the sort order is as orginally designed.

If you don't want to use the toolbar concept (real easy to create and use, though), then create a command button that does the same thing.

 

[gallas]

Tks FancyP. I think that the behaviour that you see occurs when other users have the same form open so that when you close the form Access cannot update the OrderBy value. Try it using a database that only you have open and the sort order you set during the session will be saved.

Gallas.

 

[FancyPrairie]

You can get around the problem by adding the following code to your OnOpen event of the form.

Private Sub Form_Open(Cancel As Integer)

Me.OrderByOn = False

End Sub

 

[gallas]

Tks FancyP, I read the description of OrderByOn and thought that it would prevent any user sorting (so I didn't try!)but it doesn't. Yr code works fine. Any chance of some code for a button to re-sort / requery when the user wants to revert to the initial sort order without closing the form?

Gallas.

 

[FancyPrairie]

In the OnClick event of the command button, add the same code. Like this;

Sub cmdButton_Click()

Me.OrderByOn = False

End Sub

 

[gallas]

Works perfectly. Tks.