Maintaining a Custom Sorted List

[Mike Krailo]

I have various projects that require custom sorting on either alpha numeric lists of items or just custom groupings. This of course uses a helper column with a list of numbers that can force any sort order I want. Maintaining those lists has been a pain so I finally broke down and made a form for managing the sort order in a less painful way. Ideally, there would be a way to simply drag and drop any item in the list to a new location but I don't know how to code that.

I have attached the database with an example of how it works. Just select any item in the list box and that will go to the record on the form. Clicking Up/Down buttons adjusts the sort order by one record or if the x10 checkbox is ticked, then move up/down by 10 records at a time. This all works OK except for one annoying issue where the following message pops up if you click on any list item or even the checkbox itself to go back to moving up/down by just one record.

Would love to just trap that or do a On Error Resume Next but this is not really an error. It's more of an informational thing. If someone can look at this I would appreciate it.

 

[Ranman256]

if you make a 'list' table with an autonum field, then user can add item and gets a sort order in order added.
Note: you must have a copy of an unused tList table so the autonum always starts with 1.

I copy it before the user begins,like:
docmd.CopyObject ,"tList",acTable ,"tList-MT"

the MT is a fresh 'empty' table so the count will start at 1.

 

[Pat Hartman]

Nice sample. It works well for fine tuning. I use a more curmudgeon approach. I generate sequence numbers incremented by 10 or 100 as items are added. The user can then change a number from 460 to 125 to move an item. Then there is a renumber button to make the numbers all neat again.

 

[Mike Krailo]

Thanks Pat. I used to do it that way as well except for the re-query button. This is just a tad bit easier to work with, especially with longer lists.

 

[Eugene-LS]

... Just select any item in the list box and that will go to the record on the form. Clicking Up/Down buttons adjusts the sort order by one record or if the x10 checkbox is ticked, then move up/down by 10 records at a time. ...

Here an example for you :

 

[Mike Krailo]

Trying out your demo Eugene, am I missing a reference or something?

 

[Eugene-LS]

Trying out your demo Eugene, am I missing a reference or something?

Too bad.
But there's nothing unusual there.

Most likely you have 32-bit MS office and my code must be adapted for it - wait please ...

 

[Mike Krailo]

No, I have 64bit Office.

 

[MajP]

I do all of this with a class module, so I can build this capability easily. This is seriously all the code to do everything you do, plus drag and drop, and double click move. And a lot more.

Private WithEvents ListSorter As ListMoveUpDown

Private Sub Form_Load()
  Set ListSorter = New ListMoveUpDown
  ListSorter.InitializeList Me.SortedList, Me.cmdUp, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect, True, True, dl_After
End Sub

'-----------------------------------------------------  Update Table code
Private Sub ListSorter_ItemMoved()
  ListSorter.UpdateTableSortOrder "itemtype", "ITID", "RANK"
  UpdateRankColumn
End Sub
Public Sub UpdateRankColumn()
  ListSorter.InitializeList Me.SortedList, Me.cmdUp, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect, True, True, dl_After
  Me.Repaint
  'Me.Refresh
End Sub

'------------------------------------------- Additional Code ---------------------------------------------------------------

Private Sub SortedList_Click()
  Me.Recordset.FindFirst "ITID = " & Me.SortedList
End Sub

The drawback is that I convert the rowsource to a value list. This simplifies a lot of things, but to display your sort numbering would be a pain since it was converted to a value list. AFAIK there is no way to modify an item in a value list without adding and deleting. So it is doable, I just have to get to it.
Normally i do not resort the table immediately as I move. I make all of the sorts and then reorder the table based off the listbox on the forms close. Much more efficient, but you want to see the updated rank so you have to recorder the table.
I do not have a feature for it to move more than 1 increment, but you can double click on anything then move it anywhere in the list with a single click. That is faster for any large moves, just takes some use.
The drag and drop is problematic. See very length discussion

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

Works fine until you scroll beyond the visible window. Recommend the double click instead

 

[MajP]

FYI, I tried to figure out the original issue. I do not think this is a problem in your code, but an issue with timing in modifying the underlying recordset of the listbox. I figured that some DoEvents would fix it, but I could not. I actually was able to have my implementation return the same error. I do not think you can "trap" that error, but I think you can code around it. But I got tired searching for a work around.

 

[Eugene-LS]

No, I have 64bit Office.

Fine! Try that file please

 

[Mike Krailo]

MajP, that is awesome. It's going to take me a while to absorb what is going on in your class module, but that functionality works really well. Thanks for taking a look at the original problem I was having as well.

Eugene, I took a look at your new demo and I still cannot get it working at all. I like how the insertion line shows up in the form for drag and drop though.

 

[MajP]

It's going to take me a while to absorb what is going on in your class module, but that functionality works really well.

The code in the Class module is lengthy, but that is the hard part of doing this. Writing hard wired code for one time use is super easy. Writing generic, reusable, flexible code takes effort. The goodness in the code is not that it does those things well. It is the fact that I can reuse it almost anywhere with a few lines of code. It is a black box with inputs and outputs. Here is the actual demo database that shows some of the other features.

If I get time I will add the features to allow you to set a major movement. (move 2, 4, 10 x...)
As I said, because I convert to a value list (for coding ease) it is easy to update the underlying sort order, but hard to update and show in the value list. I will look at that. Again it is easy to code that for one time use, but hard to make that a generic feature.

FYI. The very simple procedure to update the values works if the original listbox has a PK field and a Rank field so you can write a generic update query.

 

[Mike Krailo]

Actually, I don't really have to see the sort order as in my form. That was only for visualizing what was happening while developing. I was going to zero out that field in the list anyways.

 

[Pat Hartman]

This is just a tad bit easier to work with, especially with longer lists.

Really, I would think the visual method would be harder with longer lists. I guess the increment by 10 speeds things up.

And MajP has posted yet another class module I think MajP is the only one here who actually builds classes for the right reasons. Well done again. You might want to start a collection for us so we don't need to keep track of them OR publish them in a database for sale

 

[Mike Krailo]

Pat, maybe not huge lists but a couple hundred was all I have used it on so far. I'll be switching to the MajP way for sure though. I really like the double click and single click to insert elsewhere.

 

[Eugene-LS]

Eugene, I took a look at your new demo and I still cannot get it working at all. I like how the insertion line shows up in the form for drag and drop though.

I think I know what's the matter, initially the example was in Russian and the names of some objects with Cyrillic remained - now they are gone
Please check the operation of the example.

 

[Mike Krailo]

That was it Eugene, works perfect now. I thought it was going to show the insertion point as you dragged, but it does work. I'm learning a lot today.

 

[Eugene-LS]

That was it Eugene, works perfect now.

Hurray!!!

 

[Mike Krailo]

MajP, I'm working through your code and trying to figure out the process for converting the listbox from a query to a value list. I changed it back to a query and thought it would automatically convert it back to value list but it did not. Is that an initial setup thing?

I'm trying to get the add and delete buttons to work and not having any luck. Probably because I jacked up the value list when I was adding items to the list.