Move rows up/down in an ubound list box

[dogdays]

win 7 64bit, access 2007
Is there a simple way to move a row up/down in a list box?

tia Jack

 

[portews]

Here's a good article on the subject with the full code.

Move ListBox Items Up & Down. Change Order Of List Box Values

 

[dogdays]

Thanks for the reply. Unfortunately the example is for Excel not Access.

Jack

 

[mole999]

maybe wrong, but surely the box needs to be sorted in the order you want, as the table in the background is unsorted. If there is a specific order for the list, maybe add a control to the ID and build your prefered order in ther and then display by that order, showig the list as you want it

 

[Micron]

win 7 64bit, access 2007
Is there a simple way to move a row up/down in a list box?

tia Jack

I presume you don't mean a simple sort ascending or descending.
I'd say not really simple but it could be done. However, I've never done it beyond having a sort order field with increments in magnitudes of 10 or something like that so that I could insert a value in between others. If you want to move item 6 into position 4 (and not just swap), what I think you'd need is a sort column in the listbox and its row source, which would not have to be visible. To resort, open a form datasheet and lock all fields except the sort order. The user can manually resort the items by reassigning numbers and clicking a button to resort the list. You would have to check that no two items have the same sort order number before closing the form and requerying the listbox.
As I wrote this, the control tab order dialog sheet comes to mind. In that, you can drag a control name from one line to the next to alter the tab order. Perhaps this is an Add-in that is available or can be replicated.

 

[dogdays]

Micron, thanks for the reply. The tab order dialog sheet is what I wanted to replicate.
Have no idea where the source could be found.

Jack

 

[Micron]

I was thinking it might be a listview control, but I confess I've never used one. You've got me intrigued, though. If I wasn't working outside all day every day building a deck (in the heat and humidity- poor me!) I would spend more time on it. In the meantime, I can think of two ways that don't provide the ability to drag like the dialogs do.

If you wanted to allow a user to select a listbox item to be moved, then another item to determine where to move it to, you could do that with a multi select list box if it also had a sort order field. In code, you'd limit the number of selections to 2 and swap the sort order values, then refresh the list box to display the new order. Only one value could be chosen for relocation at a time.

Another would be to have left and right listboxes. Left one populated. It has a sort order field and you double click the items to move them to the right box, which is linked to a temp table. When the ordering is complete, click a button and run a query to update the main table with the values in the temp table. You could use the listbox index values (which are 0 based). If the main table sort order is also 0 based, you won't have to add 1 to the index value before you do the update. If you prefer, you could have small buttons with left/right arrows to allow user to move an item either way (left/right or right/left if a correction is needed).

 

[dogdays]

I implemented a simple way of doing. I have cmdMoveUp and cmdMoveDown, assume no headings and move one row at a time.
Also have a non visible listbox of same size and shape of the listbox the user sees.
Not elegant but does work.
Thanks for the thoughts.

Jack