Solved Move row up and down in Listbox (1 Viewer)

[arnelgp]

hello.
Please note that on the RowSource of your combobox, you need to
put [Position] column as 1st in the Select statement.

Select [position], [field1], [field2] from yourTable Order by [position];

next on the property sheet (data tab) you set the Bound Column to 1.
then you hide [position] column of the list by setting the
Column Widths property (Format tab) to 0:

Column Widths: 0";1";1"

i update the code:

Private Sub MoveUpDown(value As Integer)
'
' Note:
'
' The RowSource of the listbox should make [Position] field
' the 1st field in the select query.
'
' Select [position], [field1], [field2] from yourTable Order by [position];
'
' Set the Bound Column of the listbox to 1.
'
' You "hide" [Position] Column on the Listbox by
' setting it's ColumnWidth to 0.
'
Dim newpos As Long
Dim oldpos As Long
Dim ok As Boolean
Dim index As Long
If Me.Liste13.ListIndex = -1 Then
    'do nothing nothing is selected
Else
    If value = -1 Then 'move up
        If Me.Liste13.ListIndex <> 0 Then
            index = Me.Liste13.ListIndex - 1
            oldpos = Me.Liste13
            newpos = Me.Liste13.ItemData(index)
            ok = True
        End If
    Else 'move down
        If Me.Liste13.ListIndex <> Me.Liste13.ListCount - 1 Then
            index = Me.Liste13.ListIndex + 1
            oldpos = Me.Liste13.ItemData(index)
            newpos = Me.Liste13
          
            ok = True
        End If
    End If
    If ok Then
        DBEngine(0)(0).Execute "update ProjekteName set position=9999 " & _
        "where position = " & newpos & ";"
        DBEngine(0)(0).Execute "update ProjekteName set position= " & newpos & " " & _
        "where position = " & oldpos & ";"
        DBEngine(0)(0).Execute "update ProjekteName set position= " & oldpos & " " & _
        "where position = 9999" & ";"
        Me.Liste13.Requery
        Me.Liste13 = Me.Liste13.ItemData(index)
    End If
End If
End Sub

 

[Gasman]

If you use my class module this is the entire code that is required to make this work on any listbox.

Public WithEvents lstUpDwn As ListMoveUpDown 'Using the event
Private Sub Form_Load()
  Set lstUpDwn = New ListMoveUpDown
  lstUpDwn.InitializeList Me.lstSort, Me.cmdup, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect
  'Initialize the second
End Sub

The class controls all the buttons, enables drag and drop (with limits), and exposes events and other properties to track the reordering.

@MajP
Any chance of a 2007 friendly version please, or just the class code for the listbox ?

TIA

 

[MajP]

@Gasman
Try this one.

 

[Gasman]

That works fine.
Thank you very much.