Keep the highest priority for the initial sort

[bikcina]

Hello everyone.

I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.


Regards,
bikcina!

 

[vbaInet]

Basically what you're saying is that you would like to add any other sort to your initial sort?

You can probably use the On Filter and On Current events of the form to catch the new filter, and apply it to yours.

 

[bikcina]

Exactly.
I would like to add any other sort to my initial sort (but my initial sort must have highest priority).
Something like this:

Initial sort | Added sort

1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3

I'll try to do this with OnFilter and OnCurrent events.
Thank you for reply.

 

[gemma-the-husky]

unless you have duplicates within your primary sort, you will not be able to achieve anything by resorting.

eg in your example the 6 items you show are uniquely sorted. adding further sort parameters can have no further effect

 

[CJ_London]

you would need to disable the sort buttons available in the ribbon and on rightclick so the user cannot use them. Then based on some event (say dblclick) on the control the user wants to sort on you would need to create a sort string which is like the order by in a query

primarysort="[Col1]"
me.orderby=primarysort & ", [" & screen.activecontrol.controlsource & "]"
me.orderbyon=true

for the user to sort on multiple columns and/or choose ascending/descending and remove sort is more complex but doable

Alternatively, disable the ribbon sort and create your own shortcut menus selected by right clicking on a control.

 

[vbaInet]

I would like to add any other sort to my initial sort (but my initial sort must have highest priority).
I'll try to do this with OnFilter and OnCurrent events.

Sorry, it should have been the Apply Filter event that should do it. Here's a way:

1. In the Load event, save your original sort string from the OrderOn property into a String variable
2. When the Apply Filter event fires, i.e. when a sort or any other filter criteria is called, compare your variable from step 1 to the OrderOn property.
3. If it differs, do the needful on the resultant string - replace() and concatenate.

 

[bikcina]

Thank you all for your help.
I ' am trying to implement vbaInets suggestion, but I'm having problem with OrderBy.

My code:
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

Me.OrderBy = "[frm].[col1], [frm].[col2] "
Me.OrderByOn = True
EndSub

This code doesn't work. Forms sort works exactly like it did before, however this code does execute. When i try to sort form it triggers event but it has no effect on sort.
I wrote this code just for testing purposes (to see if this approach can help me).

 

[CJ_London]

[frm].[col1] etc need to be the names of the fields in your form recordsource

 

[bikcina]

Yes I'm aware of that. I checked names, that shouldn't be a problem.

 

[CJ_London]

in that case suggest try my suggestion a few posts back

 

[bikcina]

After a lot of debugging I came to next conclusion.

Code in my previous post is correct but access sort comes after ApplyFilter code and override my sort, but I'm not sure that this is correct assumption.

So CJ_Londons suggestion will probably work.

I'll try it later today.

 

[vbaInet]

Attached is an example. You can build on that.

 

[bikcina]

I managed to do this a little bit different. On ApplyFilter I form and run sql query.
When user clicks on sort in OrderBy I get which column needs to be sorted and how (asc or des), and concatenate that on my default query in ORDER BY clause.

Thank you all for your help.

 

[vbaInet]

Good to hear!