Move items in listbox with autonumber?

[Asspin]

Ok, well I am not sure this is the best way to get this done, but here goes. I have a database which lets users select items from a list on the left which will be added to the list on the right. When the forms opens it loads their preferences from the table based on their windows login name using the following code.

Private Sub Form_Load()
    strAlias = VBA.Environ("UserName")
    lstStart.RowSource = "SELECT qData.AutoNumber, qData.sData, qData.sPath FROM qData WHERE (((tData.sUserName)='" & strUserName & "'));"
End Sub

The list, while having all 3 fields in it, only displays field 2 (sData) which is a program name. What I am trying to do is find a way to be able to move an item in the list up a row. I am wondering if there is a way to use code to get both of the autonumber values and switch them, then requery the table? Would this work? If so, how do I do that? If not, does anyone have a better idea for me?

Any help is appreciated!

Dan

 

[lameid]

Why not just sort by the program name and be done with it?

lstStart.RowSource = "SELECT qData.AutoNumber, qData.sData, qData.sPath FROM qData WHERE (((tData.sUserName)='" & strUserName & "')) Order By qData.sData;"

Alternately if you want to order other by a sort order, add a sequence field to the table and order them the way you want by sequence. Sort (order by) them in your SQL like I did above but for the new field. If using this method I recomend you keep the sequences far apart like number the first 100 and the second 200. Then when you add something before the second item you can give it a sequence of 150. This prevents you from having to resequence the entire list often.

 

[MajP]

http://www.4shared.com/get/91337896/8293ede9/SortList.html

See this example.

 

[Asspin]

Ok, so I figured it out, apparently I can't change the autonumber field, so I ended up just moving the program names around the box and updating it in the table. Works pretty slick.

Private Sub imgDown_Click()
Dim iTop As Integer, iBottom As Integer, sTopName As String, sBottomName As String
    iTop = lstStart.Column(0, lstStart.ListIndex)
    iBottom = lstStart.Column(0, lstStart.ListIndex + 1)
    sTopName = lstStart.Column(1, lstStart.ListIndex)
    sBottomName = lstStart.Column(1, lstStart.ListIndex + 1)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tData SET sData ='" & sTopName & "' WHERE AutoNumber =" & iBottom
    DoCmd.RunSQL "UPDATE tData SET sData ='" & sBottomName & "' WHERE AutoNumber =" & iTop
    DoCmd.SetWarnings True
    lstStart.Requery
End Sub

Thanks for the suggestions!

Dan

 

[PHV]

So, you don't care the value of sPath for any sData ?