code for user defined sort

[Mark Andrews]

Anybody have some good code to control user defined sorting. Let me give
you an example of what I mean:

In my program the user can setup various lookup values (used for dropdowns
etc...). Sometimes they like to see those dropdowns in a certain order.
They might add (Blue, Green, Red) and like to see it as (Green, Red, Blue).

Normally these lookup tables are pretty simple (key, text value), so now I'm
adding a sort column and would like some nice code for arrows to move values
up or down in the sort order.

Thanks in advance,
Mark

 

[Jeanette Cunningham]

Hi Mark,
try Rogers Access Library, if not found, try some of the other developer
libraries on Roger's site.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[KARL DEWEY]

If the list does not change then here is a way.
Create a Sort table like this --
Color Sort1 Sort2 Sort3
Red 1 3 2
Blue 2 1 3
Green 3 2 1
Add an object to your for named PickSort to output 1, 2, or 3 for sort
selection.
Then create a MultiSort union query like this --
SELECT Color, Sort1 AS Sort
FROM Sort
WHERE [Forms]![YourForm]![PickSort] = 1
UNION ALL SELECT Color, Sort2 AS Sort
FROM Sort
WHERE [Forms]![YourForm]![PickSort] = 2
UNION ALL SELECT Color, Sort3 AS Sort
FROM Sort
WHERE [Forms]![YourForm]![PickSort] = 3
UNION ALL SELECT Color, Sort1 AS Sort
FROM Sort
WHERE [Forms]![YourForm]![PickSort] Is Null; This last is default sort.

Join this query in the one that you want to sort on Color. Add
MultiSort.Sort field to sort on.