Move Item in Listbox Function

[mresann]

A common occurrence in working with Access interface forms is to be able to move a Listbox item up or down a list, sometimes to the top or bottom of the list. The programming for such a relatively benign function is involved. The Listbox must use a Row Source Type of Value/List with delimiters of either a comma ( , ) or semi-colon ( ; ) in the Row Source assignment. Using command buttons to move data up or down, or to the top or bottom, makes it easy for the user to position data within the listbox.

Here is a typical Listbox with navigation buttons for items:

Let's say you want to move the selected item down one position. Clicking the "Move Down" button produces the following result:

Clicking the "Move Top" button produces the following result:

Clicking the "Move Bottom" button produces the following result:

Clicking the "Move Up" button produces the following result:

The function is called by passing the listbox control and an increment amount. In addition, the navigation buttons for moving the list item are typically called so that their enabled properties can be properly assigned. The function calls are done through any control or programmatic process that alters the list order of the Listbox, most commonly through the navigation command buttons shown above. Here is how the function would be called from the "Move Up" command button, which moves the selected list item up one position:

Call fncMoveListItem( _
      ctlListBox:=lstExample01, _
      lngIncrement:=cglngMoveUp, _
      cmdMoveUp:=cmdMoveUp01, _
      cmdMoveDown:=cmdMoveDown01, _
      cmdMoveTop:=cmdMoveTop01, _
      cmdMoveBottom:=cmdMoveBottom01)

Note that lngIncrement is passed with a constant. The four constants that are located on the public module "basMoveListItem" are assigned as follows:

Public Const cglngMoveUp      As Long = -1
Public Const cglngMoveDown    As Long = 1
Public Const cglngMoveTop     As Long = -99999999
Public Const cglngMoveBottom  As Long = 99999999

Note that the Move Top and Move Bottom constants are very high long values, but the constants make the code easy to follow. If you choose not to use constants, then make sure that you use the exact numbers indicated.

Back to the function call: Note that the command buttons are passed as well, so that they are enabled or disabled depending on the resulting position of the selected item in the Listbox. You can see it in the examples above as well.

In addition, if you select a list item, the listbox AfterUpdate procedure passes the command buttons as well, using an increment value of zero (0) as no items were moved. Here is an example:

Call fncMoveListItem( _
      ctlListBox:=lstExample01, _
      lngIncrement:=0, _
      cmdMoveUp:=cmdMoveUp01, _
      cmdMoveDown:=cmdMoveDown01, _
      cmdMoveTop:=cmdMoveTop01, _
      cmdMoveBottom:=cmdMoveBottom01)

In the sample database, there are two listbox examples. The second example just uses two command buttons to move the list item up or down, but does not include command buttons that move an item to the top or bottom of a list directly. In this case, the function can be called with just the Move Up and Move Down values as shown:

   Call fncMoveListItem( _
         ctlListBox:=lstExample02, _
         lngIncrement:=cglngMoveUp, _
         cmdMoveUp:=cmdMoveUp02, _
         cmdMoveDown:=cmdMoveDown02)

The function is generously commented through each process, so you can follow the logic more easily. You can just port the entire bas file to your project, or just use the function in your own modules, making sure you also include the public constants.

The attachment contains a sample database in MDB format, as well as the

 

[Galaxiom]

You can just port the entire bas file to your project, or just use the function in your own modules, making sure you also include the public constants.

You appear to have zipped your phone format module instead of the one with the list box functions.

 

[Galaxiom]

Another way to do this is with the Sort Method of a Disconnected ADO Recordset set as the listbox's recordset. A field in the recordset is updated with the new order then the Sort applied.

This technique also provides a Filter Method allowing items to be removed which is great when using two linked textboxes to move items between Selected and Unselected lists.

BTW However it is done internally, it is also a good idea to implement this type of thing as a Class to encapsulate the code. Essentially the class is a wrapper that associates the objects with each other. The class instance is initialized and passed the listbox and button objects that are associated with the various functions. This is a lot more orderly than using wider scoped functions, especially when more than one set of listboxes and buttons is used on a form.

 

[mresann]

Another way to do this is with the Sort Method of a Disconnected ADO Recordset set as the listbox's recordset. A field in the recordset is updated with the new order then the Sort applied.

This technique also provides a Filter Method allowing items to be removed which is great when using two linked textboxes to move items between Selected and Unselected lists.

BTW However it is done internally, it is also a good idea to implement this type of thing as a Class to encapsulate the code. Essentially the class is a wrapper that associates the objects with each other. The class instance is initialized and passed the listbox and button objects that are associated with the various functions. This is a lot more orderly than using wider scoped functions, especially when more than one set of listboxes and buttons is used on a form.

Fair enough. In fact, the module was created with the idea that others can follow the logic of the code and how it integrates with what they can accomplish in their own projects, which is why it is heavily commented as well. In more commercially distributed applications, I always break down my modules to their bare necessity.

For instance, this function should be broken into at least three different functions that incorporate the three processes that list box movement and their associated navigation buttons must entail, but the ability to explain the process is much easier through a single function. In addition, unnecessary labels and comments should be eliminated, or pared to the bare minimum.

Theoretically, most modules could (should?) be converted to Classes for greater encapsulation, particularly those that use public constants and variables, or in the case of this function, passing controls as variants instead of their own class. The concepts of the process are more difficult to explain, though, so I left the function as is and heavily commented it.

 

[MajP]

One thing to add a lot of utility to your code is to allow the user to use a table or query as the rowsource. From what I can tell your code only supports a value list. I would think the majority of listboxes are built on a query. You can convert a user query into a value list pretty easy. Here is the code you could adapt to add this functionality. Just check the rowsourcetype on load and convert to value list if needed.

Public Sub convertToValueList()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
    intColCount = Me.ListBox.columnCount
    strSql = Me.ListBox.RowSource
    Me.ListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    Me.ListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       Me.ListBox.addItem (strLstValue)
       strLstValue = ""
    Loop
End Sub

Your code works fine, but you may find it simpler to use the AddItem and RemoveItem Properties of the listbox instead of saving to an array. You remove it from where it is and add put it back to where you want it. This is all my code to move up, down, top, bottom

Private Sub moveItem(TheDirection As Direction)
  Dim ind As Long
  Dim newIndex As Long
  Dim val As String
  Dim col As Integer
  ind = Me.ListBox.ListIndex
  Select Case TheDirection
    Case Up
      newIndex = ind - 1
    Case down
      newIndex = ind + 1
    Case Top
      newIndex = 0
    Case bottom
      newIndex = Me.ListBox.ListCount - 1
  End Select
     For col = 0 To Me.ListBox.columnCount - 1
       val = val & Me.ListBox.Column(col, ind) & ";"
     Next col
     Call delItem(ind)
     Call addItem(newIndex, val)
  EnableDisableButtons
End Sub

Private Sub delItem(ind As Long)
  Me.ListBox.RemoveItem ind
End Sub

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

Also you could really simplify your button enabling disabling by having a central Procedure. I just call this procedure after making any moves or selection.

Private Sub EnableDisableButtons()
  Me.ButtonUp.Enabled = Me.ListBox.ListIndex > 0
  Me.ButtonDown.Enabled = Me.ListBox.ListIndex < Me.ListBox.ListCount - 1 And Me.ListBox.ListIndex <> -1
  
  If Not Me.ButtonBottom Is Nothing Then
   Me.ButtonBottom.Enabled = Me.ListBox.ListIndex < Me.ListBox.ListCount - 1 And Me.ListBox.ListIndex <> -1
  End If
  If Not Me.ButtonTop Is Nothing Then
    Me.ButtonTop.Enabled = Me.ListBox.ListIndex > 0
  End If
  If Not Me.ButtonDeselect Is Nothing Then
    Me.ButtonDeselect.Enabled = Me.ListBox.ListIndex <> -1
  End If
End Sub

As Glaxiom pointed out having a class really makes it easy on the user. In my demo I have two listboxes, both with multiple fields visible. One is based on a large query the other a value list.

This is the entire code in the Form to provide all the functionality for two listboxes, the class handles all events.

Option Compare Database
Option Explicit

Public lstUpDwn As New ListMoveUpDown
Public lstUpDwn2 As New ListMoveUpDown


Private Sub Form_Load()
  lstUpDwn.InitializeList Me.lstSort, Me.cmdup, Me.cmdDown, Me.cmdTop, Me.cmdBottom, Me.cmdDeselect
  'Initialize the second
  lstUpDwn2.InitializeList Me.lst2, Me.cmd2Up, Me.cmd2Down
End Sub

class ListMoveUpDown

Option Compare Database
Option Explicit

Private WithEvents mButtonUp As Access.CommandButton
Private WithEvents mButtonDown As Access.CommandButton
Private WithEvents mButtonBottom As Access.CommandButton
Private WithEvents mButtonTop As Access.CommandButton
Private WithEvents mButtonDeselect As Access.CommandButton
Private WithEvents mListBox As Access.ListBox

Private Enum Direction
  Up = 0
  down = 1
  Top = 2
  bottom = 3
End Enum
'---------------------------------------------------------------- Class Method to Initialize ---------------------------------------------------------------

Public Sub InitializeList(TheListBox As Access.ListBox, TheUpButton As Access.CommandButton, TheDownButton As Access.CommandButton, Optional TheTopButton As Access.CommandButton, Optional TheBottomButton As Access.CommandButton, Optional TheDeselectButton As Access.CommandButton)
  Set Me.ListBox = TheListBox
  Set Me.ButtonUp = TheUpButton
  Set Me.ButtonDown = TheDownButton
  Me.ButtonUp.OnClick = "[Event Procedure]"
  Me.ButtonDown.OnClick = "[Event Procedure]"
  Me.ListBox.AfterUpdate = "[Event Procedure]"
  Me.ListBox.OnClick = "[Event Procedure]"
  If Not (TheTopButton Is Nothing) Then
    Set Me.ButtonTop = TheTopButton
    Me.ButtonTop.OnClick = "[Event Procedure]"
  End If
  If Not (TheBottomButton Is Nothing) Then
     Set Me.ButtonBottom = TheBottomButton
     Me.ButtonBottom.OnClick = "[Event Procedure]"
  End If
  If Not (TheDeselectButton Is Nothing) Then
     Set Me.ButtonDeselect = TheDeselectButton
     Me.ButtonDeselect.OnClick = "[Event Procedure]"
  End If
  
  If Me.ListBox.RowSourceType = "Table/Query" Then convertToValueList
  EnableDisableButtons
End Sub
'------------------------------------------------------------------- Class Properties -----------------------------------------------------------------
Public Property Get ListBox() As Access.ListBox
  Set ListBox = mListBox
End Property
Public Property Set ListBox(TheListBox As Access.ListBox)
  Set mListBox = TheListBox
End Property
Public Property Get ButtonUp() As Access.CommandButton
  Set ButtonUp = mButtonUp
End Property
Public Property Set ButtonUp(TheUpButton As Access.CommandButton)
  Set mButtonUp = TheUpButton
End Property
Public Property Get ButtonDown() As Access.CommandButton
  Set ButtonDown = mButtonDown
End Property
Public Property Set ButtonDown(TheDownButton As Access.CommandButton)
  Set mButtonDown = TheDownButton
End Property
Public Property Set ButtonBottom(TheBottomButton As Access.CommandButton)
  Set mButtonBottom = TheBottomButton
End Property
Public Property Get ButtonBottom() As Access.CommandButton
  Set ButtonBottom = mButtonBottom
End Property
Public Property Get ButtonTop() As Access.CommandButton
  Set ButtonTop = mButtonTop
End Property
Public Property Set ButtonTop(TheTopButton As Access.CommandButton)
  Set mButtonTop = TheTopButton
End Property
Public Property Get ButtonDeselect() As Access.CommandButton
  Set ButtonDeselect = mButtonDeselect
End Property
Public Property Set ButtonDeselect(TheDeselectButton As Access.CommandButton)
  Set mButtonDeselect = TheDeselectButton
End Property
'------------------------------------------------------------------------- Class Events -----------------------------------
Private Sub mButtonUp_Click()
  If Me.ListBox.ListIndex > 0 Then moveItem Up
End Sub
Private Sub mButtonDown_Click()
  moveItem down
End Sub
Private Sub mButtonTop_Click()
  If Me.ListBox.ListIndex > 0 Then moveItem Top
End Sub
Private Sub mButtonBottom_Click()
  moveItem bottom
End Sub
Private Sub mButtonDeselect_Click()
  Me.ListBox.Value = ""
  EnableDisableButtons
End Sub
Private Sub mListBox_Click()
  EnableDisableButtons
End Sub
'--------------------------------------------------------------------------- Class Methods ----------------------------------
Private Sub moveItem(TheDirection As Direction)
  Dim ind As Long
  Dim newIndex As Long
  Dim val As String
  Dim col As Integer
  ind = Me.ListBox.ListIndex
  Select Case TheDirection
    Case Up
      newIndex = ind - 1
    Case down
      newIndex = ind + 1
    Case Top
      newIndex = 0
    Case bottom
      newIndex = Me.ListBox.ListCount - 1
  End Select
     For col = 0 To Me.ListBox.columnCount - 1
       val = val & Me.ListBox.Column(col, ind) & ";"
     Next col
     Call delItem(ind)
     Call addItem(newIndex, val)
  EnableDisableButtons
End Sub

Private Sub delItem(ind As Long)
  Me.ListBox.RemoveItem ind
End Sub
Public Sub addItem(ind As Long, val As String)
  Me.ListBox.addItem val, ind
  Me.ListBox.Selected(ind) = True
End Sub

Public Sub convertToValueList()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
    intColCount = Me.ListBox.columnCount
    strSql = Me.ListBox.RowSource
    Me.ListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    Me.ListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       Me.ListBox.addItem (strLstValue)
       strLstValue = ""
    Loop
End Sub
Private Sub EnableDisableButtons()
  Me.ButtonUp.Enabled = Me.ListBox.ListIndex > 0
  Me.ButtonDown.Enabled = Me.ListBox.ListIndex < Me.ListBox.ListCount - 1 And Me.ListBox.ListIndex <> -1
  
  If Not Me.ButtonBottom Is Nothing Then
   Me.ButtonBottom.Enabled = Me.ListBox.ListIndex < Me.ListBox.ListCount - 1 And Me.ListBox.ListIndex <> -1
  End If
  If Not Me.ButtonTop Is Nothing Then
    Me.ButtonTop.Enabled = Me.ListBox.ListIndex > 0
  End If
  If Not Me.ButtonDeselect Is Nothing Then
    Me.ButtonDeselect.Enabled = Me.ListBox.ListIndex <> -1
  End If
End Sub