Good morning!
I am attempting to do something that I think should be simple, but I have been failing for days and I think my approach must be flawed and I was hoping for some input. Please excuse the length of the post, I wanted to be as detailed as possible in the steps I have taken and my reasoning.
I want to be able to reorder a list in a listbox by dragging rows, update the integer that I am using to sort the list by, and write the new order to the database. This seems really simple, but I have not been able to achieve it nor can I find any examples for this - all the listbox dragging examples I have been able to find are for much more complicated cases. If I have overlooked a sample or example file (likely) I would be happy to be pointed in the right direction.
I have a non-hierarchal listbox, with a list of categories. There are two columns - the categoryName and the categoryRank. When the listbox is populated from the database, the recordID is written to the rowTag, and the name and category are written to cellTags for cells 0 and 1 respectively. The listbox is sorted by categoryRank (an integer). The cellTextPaint event copies the data to the corresponding cells to be visible to the user. This all works fine.
In the open event, I have EnableDragReorder = True. When I run the file, I am able to drag and reorder the rows. What I can’t seem to figure out is how to then correctly adjust the categoryRank to reflect the new order.
In the DragReorderRows event, I have the following:
// in the initial load, categoryRank is sequential
// and equal to row number +1 - row 0 = rank 1, row 1 = rank 2, etc
me.CellTag(newPosition, 1) = newPosition +1 // compare the categoryRank to the new row
me.cell(newPosition, 1) = me.celltag(newPosition, 1) // write the new cellTag value to the corresponding cell so it is visible
MsgBox(me.CellTag(newPosition, 1)) // I have this in temporarily to get some visual feedback
If I run just this code, it seems to work mostly as I expect. I can drag a row to a new spot in the list, and the msgBox tells me that the cell tag value (categorySort) is what I expect it to be. The value visible in the cell, however, does not update.
Now that I have moved a row, I want to renumber everything else, so that the values for categoryRank are once again sequential with no repeats, update the listbox to reflect the new order, and update the database. To do that, I added the code below, which follows the above code in the DragReorderRows event:
for i as integer = 0 to categoryListbox.ListCount -1 // loop thru the listbox
if i +1 <> categoryListbox.CellTag(i,1) then // check the categorySort value against the current position
categoryListbox.CellTag(i,1) = i +1 // if they don't align, make the rank equal the apparent row number
me.cell(i, 1) = me.celltag(i,1) // write the new cellTag value to the corresponding cell so it is visible
app.db.sqlExecute("BEGIN TRANSACTION") // update the database record
app.db.SQLExecute ("UPDATE itemCategories SET categoryRank = '%"+ categoryListbox.CellTag(i,1) +"%' WHERE IDX = '%"+ categoryListbox.RowTag(i) +"%' ")
app.db.Commit
end if
next
me.Sort
me.Refresh
With this code added, the following happens:
If I drag the first row (item 1, listbox row 0, apparent row 1, with a rank of 1) to the last position (listbox row 4, apparent row 5), the msgBox tells me the cellTag corresponding to rank is now 5, and the listbox looks like this:
Item 2 | Rank 2
Item 3 | Rank 3
Item 4 | Rank 4
Item 5 | Rank 5
Item 1 | Rank 1
The list is in the order I wanted, but the visible values are from the previous state.
If I then drag the new first row (item 2, listbox row 0, apparent row 1) to the last position (listbox row 4, apparent row 5), the msgBox tells me the cellTag corresponding to rank is now 5, and the listbox looks like this:
Item 3 | Rank 2
Item 4 | Rank 3
Item 5 | Rank 4
Item 1 | Rank 5
Item 2 | Rank 1
Again, the list is in the order I wanted, but the visible values are from the previous state.
If I do that same action again, I get the same thing:
Item 4 | Rank 2
Item 5 | Rank 3
Item 1 | Rank 4
Item 2 | Rank 5
Item 3 | Rank 1
So it seems like maybe the renumbering is happening, but not refreshing right. The database is also not updating, so maybe the data is not being changed at all and only the UI is being changed.
To try and determine which of those was true, I added a second msgBox to get some feedback, and now the whole block of code looks like this:
// in the initial load, categoryRank is sequential
// and equal to row number +1 - row 0 = rank 1, row 1 = rank 2, etc
me.CellTag(newPosition, 1) = newPosition +1 // compare the categoryRank to the new row
me.cell(newPosition, 1) = me.celltag(newPosition, 1) // write the new cellTag value to the corresponding cell so it is visible
MsgBox(me.CellTag(newPosition, 1)) // I have this in temporarily to get some visual feedback
for i as integer = 0 to categoryListbox.ListCount -1 // loop thru the listbox
if i +1 <> categoryListbox.CellTag(i,1) then // check the categorySort value against the current position
categoryListbox.CellTag(i,1) = i +1 // if they don't align, make the rank equal the apparent row number
me.cell(i, 1) = me.celltag(i,1) // write the new cellTag value to the corresponding cell so it is visible
MsgBox( "listcount = " + i.totext + " and cell tag = " + val(categoryListbox.CellTag(i,1)).totext )
app.db.sqlExecute("BEGIN TRANSACTION") // update the database record
app.db.SQLExecute ("UPDATE itemCategories SET categoryRank = '%"+ categoryListbox.CellTag(i,1) +"%' WHERE IDX = '%"+ categoryListbox.RowTag(i) +"%' ")
app.db.Commit
end if
next
me.Sort
me.Refresh
With this code, if I drag the first row to the bottom, the first msgBox fires and tells me the new cellTag is 5, as expected. The second msgBox does not fire. The database does not update.
If I repeat that, and drag item 2 (which is now at the top) to the bottom, I am told that it’s cellTag is now 5, and NOW the second msgBox starts firing, and as far as I can tell it is reporting the state of everything as I expect. However the visible numbers do not update.
If I comment out the if / then, and just let the loop fire on everything no matter what, it fires on the first attempt to reorder but the listbox still does not accurately display the numbers nor does the database update.
So - as I said at the beginning, I feel like this should be simple, so I think my approach is fundamentally flawed, but I don’t see where. Any feedback would be greatly appreciated.
Thanks
Andrew