Maintaining a Custom Sorted List

[Mike Krailo]

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.

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

 

[MajP]

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.

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]

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.