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
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
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.
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"
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: