Maintaining a Custom Sorted List (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
8,463
1. You start with a Query rowsource.
2. The code reads the rowsource and uses it to make a recordset
set RS = currentdb.openrecordset (listbox.rowsource)
3. The code then changes the rowsourcetype to valuelist
Code:
    strSql = Me.ListBox.RowSource
    Me.ListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    Me.ListBox.RowSourceType = "Value List"
4. It loops both the records and the fields because to do an "additem" to the list
you have to make a cocatenated string to add all the columns
"TheID; TheProductName; TheRankField"

Assuming the code does not error, the value list is not permanently saved when you close the form. Since it is a runtime change, it Basically reverts back to what you had originally. Then when you open the form again the steps are repeated.
However, if you have an error it is possible that after debugging and saving the value list gets saved. You need to go back and put the query in there. If not the value list never gets updated.
I am guessing you reset the rowsource, but left the rowsourcetype as valuelist. That will mess things up. Make sure to fix both.

There are a lot of advantages of the value list, and disadvantages.
Adv
1. You can move things easily using additem, deleteItem
2. You can make your changes not connected to a table. This can allow you to only update the table once complete, not after each movement.
3. Code works for both query and value list

Dis
1. Hard to edit a record and update the list. If you are adding and deleting at the same time as sorting then you probably need to save the original rowsource on load. Then to update you need to switch back to query rowsourcetype and repeat 1 to 4. This is fine for small lists. I tried this on yours and it is slow. So that code exists. The class has a RequeryMethod. You need to requery your instance not the actual listbox.
ListSorter.Requery

Code:
Public Sub Requery()
  'Converting to a value list can be very slow so consider this on big lists
  Me.ListBox.RowSource = mOriginalRowsource
  convertToValueList
End Sub

Again, I did the code this way for maximum flexibility. However, if you really have to requery a lot then maybe want to write code that does not use a value list and keeps it bound to the queries.
 
Last edited:

Mike Krailo

Well-known member
Local time
Today, 07:02
Joined
Mar 28, 2020
Messages
1,030
Ok I'll try and work on this in the morning. Thanks for the explanation.
 

Mike Krailo

Well-known member
Local time
Today, 07:02
Joined
Mar 28, 2020
Messages
1,030
I just looked at this and it appears that I put the table name in as the row source which is never going to works since it is unsorted. I put the sorted query in there and it worked properly again. You are right about the value list getting saved because that is exactly what happened when I attempted to test add and delete code. It just jacked everything up. The question now is how do I delete or add an item to the listbox? I have the same Add and Delete button on my original form that works properly.

Edit: I see how it works now, this works. It needs some further validation in there, but the basic functionality is there.
Code:
Private Sub DeleteBtn_Click()
   Dim SelectedRank As Long
   If Nz(Me!ITID) = 0 Then Exit Sub
   SelectedRank = Me!Rank
   If MsgBox("About to DELETE " _
            & DLookup("IDescription", _
            "ItemType", _
            "ITID=" & Me!ITID) & ", OK?", vbYesNo) = vbYes Then
      ListSorter.delItem (SelectedRank - 1)
      ListSorter.UpdateTableSortOrder "itemtype", "ITID", "RANK"
      UpdateRankColumn
      DoCmd.RunCommand acCmdDeleteRecord
   End If
End Sub
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
8,463
I think this is a simpler way, but not as efficient. Remember if you modify the underlying table, you can just reload the whole list. I have a custom that custom Requery method to do that. I probably should rename it Reload to be less confusing.

Code:
Private Sub DeleteBtn_Click()
  Dim ID As Long
  If Me.SortedList & "" <> "" Then
    ID = Me.SortedList.Value
    CurrentDb.Execute "Delete * from itemType where ITID = " & ID, dbFailOnError
    ListSorter.Requery
  End If
End Sub

Same thing after adding. Just use the Requery method. Although this is a brute force method, it was not slow on your demo. If the list was very long you might not want to reload it. You might want to do something more like you did and target the additions / deletions.
 

Mike Krailo

Well-known member
Local time
Today, 07:02
Joined
Mar 28, 2020
Messages
1,030
I see that now. I will continue experimenting to see what happens with different list sizes. It's just nice seeing how each different solution works and making decisions on their use as appropriate. Thank you again for your help on this.
 

Users who are viewing this thread

Top Bottom