Maintaining a Custom Sorted List (1 Viewer)

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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.

1646840752916.png

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.
 

Attachments

  • CustomeSortOrder.zip
    96.2 KB · Views: 261

Ranman256

Well-known member
Local time
Today, 15:32
Joined
Apr 9, 2015
Messages
4,339
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

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2002
Messages
42,973
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

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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

Registered User.
Local time
Today, 22:32
Joined
Dec 7, 2018
Messages
481
... 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 :
 

Attachments

  • SortLentForm_by_Mouse_Drag&Drop_v014_ENG.zip
    138.4 KB · Views: 281
  • SortLentForm_by_Mouse_Drag&Drop_Eng_v14.png
    SortLentForm_by_Mouse_Drag&Drop_Eng_v14.png
    112.9 KB · Views: 176

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
Trying out your demo Eugene, am I missing a reference or something?
1646848382436.png
 

Eugene-LS

Registered User.
Local time
Today, 22:32
Joined
Dec 7, 2018
Messages
481
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 ...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:32
Joined
May 21, 2018
Messages
8,463
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.
Code:
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

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

sorter.jpg
 

Attachments

  • MajP_CustomeSortOrder.accdb
    2.8 MB · Views: 274

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:32
Joined
May 21, 2018
Messages
8,463
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.
 

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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

You've got your good things, and you've got mine.
Local time
Today, 15:32
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • ListBoxSorter V1.accdb
    1.3 MB · Views: 272

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Feb 19, 2002
Messages
42,973
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

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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

Registered User.
Local time
Today, 22:32
Joined
Dec 7, 2018
Messages
481
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.
 

Attachments

  • SortLentForm_by_Mouse_Drag&Drop_Eng_v17.zip
    45.2 KB · Views: 266

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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.
 

Mike Krailo

Well-known member
Local time
Today, 15:32
Joined
Mar 28, 2020
Messages
1,030
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.
 

Users who are viewing this thread

Top Bottom