Form's datasheet view - sort order problem

[Guest]

Hi all

I got stuck in the following situation.

I have a query which have several columns.
In the query's design view, I have set sort order for several (about six)
columns according to their priority, which is a little bit complicated.
So in the datasheet view, the data are sort in the desired order.

Then I creat a form base on this query,
and in the datasheet view of this form
the data follows the sorting pattern of the query, which is fine.

But the problem comes that when I perform a A-Z or Z-A sorting on any one
column,
The original sort order is over-rided and cannot be reversed.
Even when I close the form and reopen it, the sorting order still follow the
last sorting order, i.e, the unwanted one.

I wish to know if there is a way to have the sort order be changed back to
the original setting.



Simon Wong

 

[Van T. Dinh]

Try using the Form_Unload Event to set the Form's OrderBy Property to "" (an
empty String) and OrderByOn Property to False. Basically, this will
deactivate the Form's sorting when you close the Form and when you open the
Form the next time, the Query's sorting should come back.

 

[fredg]

Hi all

I got stuck in the following situation.

I have a query which have several columns.
In the query's design view, I have set sort order for several (about six)
columns according to their priority, which is a little bit complicated.
So in the datasheet view, the data are sort in the desired order.

Then I creat a form base on this query,
and in the datasheet view of this form
the data follows the sorting pattern of the query, which is fine.

But the problem comes that when I perform a A-Z or Z-A sorting on any one
column,
The original sort order is over-rided and cannot be reversed.
Even when I close the form and reopen it, the sorting order still follow the
last sorting order, i.e, the unwanted one.

I wish to know if there is a way to have the sort order be changed back to
the original setting.

Simon Wong

You can undo the second sorting at any time by simply right-clicking
anywhere on the form and selecting Remove Filter/Sort.
Whatever the underlying sort order is will be restored.

 

[Guest]

Dear Van

Thank you for your reply.

I have tried your suggestion but it doesn't work.

I went to the form's property sheet, moved to Event page, chose the "On
Unload" event, insert a macro there for the following action :
SETVALUE -- OrderBy -- ""
SETVALUE -- OrderByOn -- False
After the above is done the problem remains.

Meantime, when I move to the Menu bar, choose RECORDS then FILTERS then
ADVANCED FILTER/SORT...
It opens a window which looks like the design view of a query
In the first column shows the sorting done by A-Z in the datasheet view
I delete this column and click apply filter
The sorting goes back to original sorting.
I save the form (hoping to save the sorting), close the form and reopen it,
The sorting doesn't goes back to original sorting
But the unwanted one
I check the Advanced Filter/Sort to check and happened to see that the
sorting criteria which I have previous deleted have revived.
I.e., my problem is not solved.

To get around this problem I turn the .mdb file to .mde file. In the .mde
file I open the form and turn it to datasheet view. For any further sorting
done by A-Z or Z-A icon, the original sorting will be restored after the form
is closed and reopened.

Nevetheless I would like to know if, other than the .mde method, there is
another way to solve the problem.


Best regards
Simon Wong

 

[Guest]

You can undo the second sorting at any time by simply right-clicking
anywhere on the form and selecting Remove Filter/Sort.
Whatever the underlying sort order is will be restored.

Hi Fred

Your suggestion is not suitable in my situation because by applying Remove
Filter/Sort, not only the sort order is reset but it also removes the
criteria set to the query in limiting which records are to be displayed


Best regards
Simon Wong

 

[Brett Collings [429338]]

Simon,

Van's suggestion should work, he is suggesting you reset the
Properties via VBA, not use a Macro.

Macros are bad, bad, bad as they have no error checking and no way to
debug them. They're a crutch that should have been tossed out of
Access after Acc 2. That may or may not be the cause of it not
working, but with a Macro, you have no way of knowing. You do if
you're using VBA.

The VBA Method for doing it in the Event Procedure of Form_Unload is

Private Sub Form_Unload(Cancel As Integer)
Me.OrderBy = " "
Me.OrderByOn = False
End Sub

Now I haven't tested this but it may be that the "Me." predicate
doesn't work because the form is already unloaded and therefore not
addressable by the shortcut in which case you will have to do so
explicitly

Private Sub Form_Unload(Cancel As Integer)
Forms!MyForm!OrderBy = " "
Forms!MyForm!OrderByOn = False
End Sub

The other possibility is that the Unload event is too late for the
same reasons above and you may have to try it in the On Lost Focus
event.

Brett

 

[Guest]

Hi Brett and Van

I have tried Brett's suggestion but of no help.
Then I do some experiments and happened to get to the following to solve the
problem.

In the Form's property sheet I go to the OnLoad property
and put a macro there to Set Value for Orderby = ""

Then I open the form in datasheet view,
the data are sort in original order,
I perform some sorting by A-Z or Z-A button on serveral columns
Then I close the form and the next time I open it
the sorting returns to the original setting,
and those settings done in previous actions are cancel.
The above is tried many times and it works.
and the problem seems solved.

I wish to thank you for bringing to me the "Orderby" and "On Load" terms.
In fact what does OrderBy = "" implies?
And if the above has any downside to the form.


Best regards
Simon Wong

 

[Van T. Dinh]

Normally, the OrderBy Property contains the Names of the Fields you want to
sort by (ascending sort; for descending add the keyword DESC).

When you set it to empty String, it has no Field to sort by, hence no
sorting by the Form but there still the sorting in the Query / SQL String if
applicable.

You certainly can use the Form_Load Event. The difference is that Brett's
and my suggestion (Unload Event) tried to remove the sorting when the Form
is closed so that no (Form's) sorting is saved with the Form so that when
the Form is opened again, there is no (Form's) sorting. Using the Form_Load
Event means that the sorting is saved with the Form but then you remove the
sorting in the Form_Load Event.

Not sure why the Unload Event doesn't work for you, though.