Drag and drop to reorder lists

[scoutime]

I am trying to find a way to reorder items in a list as easily as possible. Do you have any suggestions. What I was hoping for was a way to create a drag and drop list so that the user can re-sort or order the items in the list on-the-fly.

Something like the list box under the <tab order> button on the Ribbon.

Is there anything that I could reference or examples of a form I could download?

 

[Mr. B]

Although the solution that I am going to propose does not include Drag and Drop at this time, it does provide a very easy way for users to change the sort order of one or more lists used in an application.

You did not indicate which version of Access you are currently using but I have attached a demo version of a 2010 database file. Take a look at it and see if is will help you get to where you want to go.

 

[Jake Jeon]

Although the solution that I am going to propose does not include Drag and Drop at this time, it does provide a very easy way for users to change the sort order of one or more lists used in an application.

You did not indicate which version of Access you are currently using but I have attached a demo version of a 2010 database file. Take a look at it and see if is will help you get to where you want to go.

Thank to this code, i could made multi select sorter

 

[rikklaney1]

That is cool but to take it farther...how do I set the sort number automatically? I'm doing something right now where I select items from the left listbox and a button click saves them to a table and they show in the right listbox. I can use an autonumber to get a sort value but I can't change that. So how do I set the sort number to something editable later?

 

[static]

Get highest value plus 1

Private Sub btnAdd_Click()
    SQL = "INSERT INTO tblSorter (  SortValue,[Name] ) " & _
        "SELECT TOP 1 SortValue+1,'" & InputBox("add item") & "' " & _
        "FROM (SELECT SortValue from tblsorter order by SortValue desc)"
        
    CurrentDb.Execute SQL
    lstListValues.Requery
End Sub

 

[rikklaney1]

Thanks Static. That works well. But, if I may ask for your expertise a little more. Can that be adapted to work with this


DoCmd.SetWarnings False
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM [tbl_pendingschedule]"
'Build the IN string by looping through the listbox
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
If List0.Column(2, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & List0.Column(2, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [serial] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qry_pendingtoschedule"
Set qdef = MyDB.CreateQueryDef("qry_pendingtoschedule", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.RunSQL "insert into tbl_schedule select * from qry_pendingtoschedule"


I insert things into the table based on the selection of items in a list box. Can I add the row sort number at the same time? I admit I'm getting a little out of my depth. Your help would be greatly appreciated.

 

[static]

Probably need to see the database. If you are adding multiple records then...I don't know. Maybe. Hard to say from that bit of code.

Please don't switch warnings off. And if you must, at least turn them back on again. I guess you do it because you used runsql but you should just use db.execute instead.

Dim i As Integer
Dim strSQL As String
Dim strIN As String
Dim flgSelectAll As Boolean

strSQL = "SELECT * FROM [tbl_pendingschedule]"

'Build the IN string by looping through the listbox
For i = 0 To List0.ListCount - 1
    If List0.Selected(i) Then
        If List0.Column(2, i) = "All" Then
            flgSelectAll = True
        End If
        If Len(strIN) Then strIN = strIN & ","
        strIN = strIN & "'" & List0.Column(2, i) & "'"
    End If
Next

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
    strSQL = strSQL & " WHERE [serial] in (" & strIN & ")"
End If

'Open the query, built using the IN clause to set the criteria
CurrentDb.Execute "insert into tbl_schedule select * from (" & strSQL & ")"

 

[rikklaney1]

Well dang. I was hoping you'd be able to save me. lol. Ok. I'll think around it some more. For the warnings. I do turn them back on before the end sub... but that execute is a handy new piece of info for me. Thanks.

 

[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.

Any ideas or suggestions would be appreciated.

 

[static]

Make an example version of the database and upload it to a new thread.