Help with sorting and rearranging items in listboxes

[rikklaney1]

Here's the challenge I have. I import data from an excel spreadsheet to one table called tbl_pendingschedule and add a yes/no field called scheduled. Then I have a form with two list boxes. The left one is all the units in tbl_pendingschedule with a no value in the scheduled field. On the right is a list box connected to a table called tbl_schedule. When I doubleclick an item in the left listbox, or a button when a group of items are selected, they are added to tbl_schedule and the scheduled field in tbl_pendingschedule is marked yes for the item.

Now the tough part. I need them to add to tbl_schedule IN ORDER and I can't use an autonumber because I also need to be able to click on a unit in the right listbox and move it up or down by a button click.

I have attached what I have so far. Any ideas or suggestions would be appreciated.

 

[isladogs]

You've started a new thread as requested by Static but
1. You failed to refer back to that one - add the link
2. You didn't attach anything to this one!

 

[rikklaney1]

Aww damn. Didn't realize the attachment didn't send. lol. I'll try again when I get back to my pc at work. Didn't think to refer back to the other thread. Here it is... https://www.access-programmers.co.uk/forums/showthread.php?p=1534801#post1534801

 

[rikklaney1]

Ok. lets try again. I seem to have most of it working (somewhat). I can select a group in the left and move it to the right.. although I've noticed that with large groups it doesn't always keep them in order. Bu the up and down, I've tried several different methods and can't seem to get a reliable way to move a single selection up and down and have no ideas on how to do a group.

 

[static]

see attached.

I put everything in one table.
I added an autonumber id. The 'scheduled' field which was boolean is now numeric. When a record is scheduled the order is initially taken from the id.

View attachment sort.accdb

 

[rikklaney1]

That is awesome. I'm like a monkey pounding on a keyboard with a stick compared to that. Lol. Okay, now to look through your code and see what I can learn from it. I don't know access well yet so I do what I can get to work or what I can find on google. This will give me something to work through and learn something, Thanks.

 

[static]

NP. There might be a bug or two but it's a base to work from.

 

[rikklaney1]

This is working great so far but... (there's always a but). I also need it to keep the items in the scheduled listbox in the order I move them over to it. I've added a rownum field that is created when adding something to the listbox using...

rownum2: DCount('[SORT]','[TBL_schedule]','[SORT]<=' & [sort])

this works great and I use Statics code to move items up and down. It works great. Until I add anything new to the list and it reverts back to it's original position. So I thought Static or someone else might have a suggestion as to a better way to do it.

 

[static]

change btnAdd_Click to

Private Sub btnAdd_Click()
    'get last rownum
    Dim rownum
    rownum = Nz(CurrentDb.OpenRecordset("select top 1 rownum from sched order by rownum desc")(0), 0) + 1
    
    For Each itm In Pending.ItemsSelected
        'set scheduled/rownum
        CurrentDb.Execute "update sched set scheduled=id, rownum=" & rownum & " where id=" & Pending.ItemData(itm)
        Pending.Selected(itm) = False
    Next
    Me.Refresh
End Sub

and in form_open

Scheduled.RowSource = "select id,scheduled,modeldescription & serial from sched where scheduled is not null order by rownum,scheduled"