Reorder list rows 1

[Domino2]

Anyone know the easiest way to bove a listbox rowitem. I have a listbox, and two buttons, up and down. I want to select a list item and move it up or down the list. Any ideas how I do it, thanks

 

[PHV]

What is the RowSourceType of your ListBox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks. At the moment its a value list.

 

[PHV]

OK, so you have a global array holding the current value list, don't you ?
What have you tried so far and where in your code are you stuck ?
Are you confident with the ListIndex and ListCount properties ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

I do not have any arrays at this time, I am filling the list with:

Dim Rst As Recordset
Dim f As Field

Me.List1.RowSource = ""


Set Rst = CurrentDb.OpenRecordset("Buildings")
For Each f In Rst.Fields
Me.List1.AddItem f.Name
Next
Rst.Close
Set Rst = Nothing

 

[Domino2]

Dead end then? All I can find on searching is the Drag and Dropper product. Does anyone other than Peters Software know how to do it or is it a hidden secret? Thanks

 

[MajP]

Here is an example using the Northwind Products table.

Private Sub Form_Load()
  Dim lst As Access.ListBox
  Dim str As String
  Dim rs As DAO.Recordset
  Set lst = Me.lstSort
  Set rs = CurrentDb.OpenRecordset("Products")
  Do While Not rs.EOF
    str = str & rs!productid & ";" & rs!productname & ";"
    rs.MoveNext
  Loop
  lst.RowSource = str
End Sub
Private Sub cmdDown_Click()
  Call moveDown(Me.lstSort)
End Sub

Private Sub cmdup_Click()
  Call moveUp(Me.lstSort)
End Sub



Public Sub moveUp(lst As Access.ListBox)
  Dim ind As Long
  Dim val As String
  Dim col As Integer
  ind = lst.ListIndex
  If Not ind <= 0 Then
     For col = 0 To lst.ColumnCount - 1
       val = val & lst.Column(col, ind) & ";"
     Next col
     Call delItem(ind, lst)
     Call addItem(ind - 1, val, lst)
  End If
End Sub
Public Sub moveDown(lst As Access.ListBox)
  Dim ind As Long
  Dim val As String
  Dim col As Integer
  ind = lst.ListIndex
  If Not (ind = lst.ListCount - 1 Or ind < 0) Then
     For col = 0 To lst.ColumnCount - 1
      val = val & lst.Column(col, ind) & ";"
     Next col
     Call delItem(ind, lst)
     Call addItem(ind + 1, val, lst)
  End If
End Sub
Public Sub delItem(ind As Long, lst As Access.ListBox)
  lst.RemoveItem ind
End Sub

Public Sub addItem(ind As Long, val As String, lst As Access.ListBox)
  lst.addItem val, ind
End Sub

 

[Domino2]

Many thanks Majp, at last something to work with.

 

[Domino2]

Sorry to get back. I looked in the Northwind DB but could not find project.

I am getting a halt on:

Call Movedown(Me.lstSort)

Many thanks

 

[MajP]

If you were looking for an actual form in Northwind it does not exist, I just quickly wrote some code to do what you were asking. This code will work with any listbox that uses a value list. I just made a form in the Northwind database as an example.
1) Put a listbox on a form. I called mine "lstSort", but change this to your name if you want. Or just call your listbox "lstSort" and be done.
Call moveUp(Me.lstSort)
2)I have an up and down command button called, "cmdUp", "cmdDown".

Any form with a listbox called "lstSort" and two command buttons "cmdUp" and "cmdDown", this code will work without changes in the names within the code.

 

[Domino2]

Yipeee, I'm there. Thanks again for the clarification.

 

[Domino2]

Any suggestions? I am trying to keep the list row highlighted during moving up or down. I tried the following which did not seemed to work, the row had a dotted line around it.Thanks

Public Sub Movedown(ListBox1 As Access.ListBox)
Dim ind As Long
Dim val As String
Dim col As Integer
ind = ListBox1.ListIndex

If Not (ind = ListBox1.ListCount - 1 Or ind < 0) Then
For col = 0 To ListBox1.ColumnCount - 1
val = val & ListBox1.Column(col, ind) & ";"
Next col
Call delItem(ind, ListBox1)
Call addItem(ind + 1, val, ListBox1)
End If

Me.ListBox1.SetFocus
Me.ListBox1.ListIndex = ind + 1
Me.ListBox1.SetFocus
Call ListBox1_Click


End Sub

 

[MajP]

Public Sub addItem(ind As Long, val As String, lst As Access.ListBox)
lst.addItem val, ind
lst.Selected(ind) = True
End Sub

 

[Domino2]

That was quick, thanks. However although it does retain its highlighted clicked on appearance, the row has to re-clicked on before the up/down buttons come back to life. It might be losing one of the variable contents when trying to use the buttons again? Thanks

 

[Domino2]

Thanks again, fixed.

Public Sub Movedown(ListBox1 As Access.ListBox)
Dim ind As Long
Dim val As String
Dim col As Integer
ind = ListBox1.ListIndex

If Not (ind = ListBox1.ListCount - 1 Or ind < 0) Then
For col = 0 To ListBox1.ColumnCount - 1
val = val & ListBox1.Column(col, ind) & ";"
Next col
Call delItem(ind, ListBox1)
Call addItem(ind + 1, val, ListBox1)
End If

Me.ListBox1.SetFocus
Me.ListBox1.ListIndex = ind + 1
ListBox1.Selected(ind + 1) = True

End Sub

 

[MajP]

just use the original code and add the one line of code to the additem method. The other changes you made are not necessary and will make the code not work.

 

[Domino2]

I have tried using the one liner in the additem method, but as I said it leaves it selected okay, but to make the up/down command buttons to work, the list row has to be manually selected again. The code I added works all the time, and other than trapping the beginning and end of up and down positions (ie not exceeding listcount) Can you tell me the problem you can see.

 

[MajP]

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

Take a look at the demo. It works fine for me and accounts for beginning and end of the list. No issue with loss of focus. You may have yours set up differently

 

[Domino2]

Many thanks for doing that. It's strange, yours works perfectly. I am going to look line to line on codes and see where mine is different. Watch this space.