List box move item up/down

[svjensen]

I am looking to make a form that will load data from a table into a list box.
Once the data is in the list box the user should be able to move a selected item up or down using to command buttons.
The result should then be written back to the table.

So far I have this:
Table:
'tblDataSortering' with the fields 'uid' (autonumbering), 'sort' (number) and 'data' (text)

Form:
'frmDataSortering' with a list box 'lstSorter'
The list box contains two columns 'sort' and 'data' into which the content of the table is loaded. The data is sorted using 'sort' ASC.
In addition I have two command buttons 'cmdMoveUp' and 'cmdMoveDown'.

The code that I have estalished so far is as follows:

Private Sub cmdMoveDown_Click()
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim lngTemp As Long
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblDataSortering", dbOpenDynaset)
    lngTemp = Me.lstSorter.Column(1) 'the column with the number field
    With rst
        .FindFirst "sort=" & lngTemp
        .Edit
        !sort = Me.lstSorter.Column(1, Me.lstSorter.ListIndex + 1)
        .Update
        .FindFirst "sort=" & Me.lstSorter.Column(1, Me.lstSorter.ListIndex + 1)
        .Edit
        !sort = lngTemp
        .Update
    End With
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Me.lstSorter.Requery
End Sub
Private Sub cmdMoveUp_Click()
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim lngTemp As Long
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblDataSortering", dbOpenDynaset)
    lngTemp = Me.lstSorter.Column(1) 'the column with the number field
    With rst
        .FindFirst "sort=" & lngTemp
        .Edit
        !sort = Me.lstSorter.Column(1, Me.lstSorter.ListIndex - 1)
        .Update
        .FindFirst "sort=" & Me.lstSorter.Column(1, Me.lstSorter.ListIndex - 1)
        .Edit
        !sort = lngTemp
        .Update
    End With
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Me.lstSorter.Requery
End Sub

Basically it works fine, but once I have moved one item around, I cannot move another item to the post in which previously moved items are placed.

Also I still need to write the end result back to the table.

I hope that you guys can help my take this one or two steps further

/Soren

 

[vbaInet]

Use an unbound listbox instead.

For moving the item UP or DOWN just use the ADDITEM method of the listbox. The second argument of that method is the Index property that specificies where in the listbox you would want that item added. But you would also need to remove the item from the list box as well and in that case you use the REMOVEITEM method.

I hope this gives you some idea.

 

[svjensen]

I did look into that option earlier on, but as I understand it, the list box RowSourceType must be set to 'value list'. And if that is the case, how do I then load my data into it?

 

[vbaInet]

That's correct. Populate the listbox on the load event of your form. Use a recordset to iterate through each item and then use the AddItem.

If you have more than one column then you would need the semi-colon to seperate each item during the AddItem call. Example:

List1.AddItem "Item 1; Item 2", 2

That will add the items 1 and 2 in columns 0 and 1 at index 2.

 

[svjensen]

Ok, so far I have this:

Private Sub Form_Open(Cancel As Integer)
    
    Dim qdfTemp As QueryDef
    Dim rstEmployees As Recordset
    Dim strSQL As String
    Dim i As Integer
    i = 0
    
    Set qdfTemp = CurrentDb.CreateQueryDef("")
    strSQL = "SELECT * FROM tblDataSortering ORDER BY sort"
    qdfTemp.SQL = strSQL
    Set rsData = qdfTemp.OpenRecordset
    With rsData
        Do While Not .EOF
            Me.lstSorter.AddItem !data, i
            .MoveNext
            i = i + 1
        Loop
        .Close
    End With
End Sub

But it is not working.
In the line with 'Me.lstSorter.AddItem !data, i' I am told, that the index is too large/big.

 

[vbaInet]

Just one note, do it on the LOAD event, not the OPEN event:

Private Sub Form_Load()
    
    
    Dim db as DAO.Database, rst As DAO.Recordset
    Dim strRow as String

    set db = CurrentDb
    set rst = db.openrecordset("SELECT * FROM tblDataSortering ORDER BY sort", dbOpensnapshot)
    strrow = ""

    Do While Not .EOF
        strRow = strRow & ";" & rst!data
        .MoveNext
    Loop
    
    strrow = right(strrow, len(strrow) - 1)
    lstsorter.rowsource = strrow

    set rst = nothing
    set db = nothing

End Sub

Untested.

You would only need the AddItem and RemoveItem methods for the UP and DOWN buttons.

 

[svjensen]

That is one step further (although you are missing 'With rst' and ' End With' around the loop that builds strRow

The script now writes one row of data to the list box, with each entry in separate columns. What I was trying to do, was write each entry to a separate row.

What do I need to change?

 

[svjensen]

Found the problem. I had three columns in my list box. When changing that to one it worked!!
It was the same issue, that caused the error in my own script.

I will continue working on the move up/down part of it, but it will not be until tomorrow.

Thanks so far

/Soren

 

[vbaInet]

Oki doki. That bit is sorted. I purposedly omitted the With statement because rst is only used twice in the block. It gets rid of two lines of code, that means two less lines for the compiler to read. I missed out the rst in the movenect function though

Your UP function could look something like:

Dim tempItem as string, tempIndex as integer

    tempitem = lstsorter.value
    tempindex = lstsorter.listindex

    lstsorter.removeitem lstsorter.listindex
    lstsorter.additem tempitem, tempindex - 1

To ensure that you don't go beyond 0 have a code to enable/disable the UP and DOWN buttons when necessary.

 

[svjensen]

Got the up/down functions to work.
The code is as follows:

Private Sub cmdMoveUp_Click()
    Dim tempItem As String, tempIndex As Integer
 
    tempItem = lstSorter.Value
    tempIndex = lstSorter.ListIndex
 
    lstSorter.RemoveItem lstSorter.ListIndex
    lstSorter.AddItem tempItem, tempIndex - 1
    
    Call ToggleButtons(tempIndex - 1)
End Sub
 
Private Sub cmdMoveDown_Click()
    Dim tempItem As String, tempIndex As Integer
 
    tempItem = lstSorter.Value
    tempIndex = lstSorter.ListIndex
 
    lstSorter.RemoveItem lstSorter.ListIndex
    lstSorter.AddItem tempItem, tempIndex + 1
    
    Call ToggleButtons(tempIndex + 1)
    
End Sub
 
Private Sub lstSorter_Click()
    Call ToggleButtons(lstSorter.ListIndex)
End Sub
 
Private Sub ToggleButtons(intIndex As Integer)
    Me.lstSorter.SetFocus

    Select Case intIndex
        Case 0
            Me.cmdMoveUp.Enabled = False
            Me.cmdMoveDown.Enabled = True
    
        Case lstSorter.ListCount - 1
            Me.cmdMoveUp.Enabled = True
            Me.cmdMoveDown.Enabled = False
        
        Case Else
            Me.cmdMoveUp.Enabled = True
            Me.cmdMoveDown.Enabled = True
    End Select
End Sub

One small follow up.
The list box presently has one column and the code works well with this.
Let's say that I need to have two columns, how do I retrieve the data from both columns in my up/down functions? In order to be able to write them to the list with another index.
(the line in question is 'tempItem = lstSorter.Value')

 

[vbaInet]

Investigate the column property of a listbox control. The index is zero-based (as usual) so the first column will be 0 instead of 1. For example, to get the value of the first column you would do this:

tempCol1 = lstSorter.column(0)

That would get you the value of the currently highlighted column.

The column property has two arguments.

 

[svjensen]

Seems that everything is working.
The last thing to do is to save the adjusted sorting back to the table.

I did this by adding a button to the form with the following code:

Private Sub cmdSaveSort_Click()
    Dim i As Integer
    Dim tmpUID As String, tmpSort As String, strSQL As String
    
    DoCmd.SetWarnings False
    For i = 0 To Me.lstSorter.ListCount - 1
        tmpUID = lstSorter.Column(0, i)
        tmpSort = i
    
        strSQL = "UPDATE tblDataSortering SET sort = " & tmpSort & " WHERE uid = " & tmpUID
        DoCmd.RunSQL strSQL
    Next i
    DoCmd.SetWarnings True
End Sub

This works fine
If there is an easier way to do it, I would be happy to hear about it.

vbaInet, thanks for all you help.

Cheers, Soren

 

[vbaInet]

Looking good. Just tidying up and getting rid of redundancies:

Private Sub cmdSaveSort_Click()
    Dim db as DAO.Database
    
    set db = CurrentDb

    For i = 0 To Me.lstSorter.ListCount - 1
        db.Execute "UPDATE tblDataSortering SET sort = " & i & " WHERE uid = " & lstSorter.Column(0, i)
    Next i

    set db = nothing

End Sub

Notice the introduction of db.Execute, with this you don't need to turn off warnings. You can only write this for action queries, meaning it won't work with SELECT queries.

 

[Shallo9]

Sorting List Box in MS Access VBA

Appears good but I was looking for sorting a List Box which is populated not from a query but Value List (To make it even worst this Value List is populated from a web page [Out of Scope] lets stick to Value List)

Below are the two UDF's I've coded which uses a Bubble sort algorithm as the columns could be Alphanumeric. This function works fine for N number of columns.

NB: cmbAdd is the name of the ListBox.

Sorting Procedure

Private Sub sortLst()
    Dim i As Integer, x As Integer, iString As String, xString As String
        For i = 0 To Me.cmbAdd.ListCount - 1
                For x = i + 1 To Me.cmbAdd.ListCount
            
                If (Me.cmbAdd.ItemData(i) > Me.cmbAdd.ItemData(x)) Then
                   iString = toString(i)
                    xString = toString(x)
                    Me.cmbAdd.RemoveItem (x)
                    Me.cmbAdd.AddItem iString, x
                    Me.cmbAdd.RemoveItem (i)
                    Me.cmbAdd.AddItem xString, i
                End If
            Next x
        Next i
       

        Me.cmbAdd.Requery
        
End Sub

Converts multiple column data into a single string

Private Function toString(i As Integer)
Dim j As Integer, Str As String
Str = ""
For j = 0 To Me.cmbAdd.ColumnCount - 1
Str = Str & Me.cmbAdd.Column(j, i) & ","
Next j
toString = Replace(Str, ",,", "")
End Function

The below procedure removes duplicates

Private Sub chkLst()
    Dim i As Integer, x As Integer
        For i = 0 To Me.cmbAdd.ListCount - 1
            For x = 0 To Me.cmbAdd.ListCount - 1
         
                If (Me.cmbAdd.ItemData(i) = Me.cmbAdd.ItemData(x)) And x <> i Then
                    Me.cmbAdd.RemoveItem (x)
                End If
            Next x
        Next i
        'MsgBox Me.cmbAdd.ListCount
        
        Me.cmbAdd.Requery
End Sub

 

[Galaxiom]

When I have wanted to move stuff afound in a listbox (or from one listbox to another) I set an ADODB disconnected recordset directly as the Recordset Propery of the listboxes.

The recordset includes a field for order and another for visibility so it is easy to reorder or hide any entry.

 

[Gasman]

Galaxiom,

My apologies for such a late post on this thread, but would you be able to point me in the direction on how to do that.?

When I have wanted to move stuff afound in a listbox (or from one listbox to another) I set an ADODB disconnected recordset directly as the Recordset Propery of the listboxes.

The recordset includes a field for order and another for visibility so it is easy to reorder or hide any entry.

 

[Galaxiom]

Any kind of recordset can be used to populate a listbox. Create a recordset and set it as the Recordset Property of the Listbox. The results of operations performed on the recordset will be shown in the listbox

ADODB recordsets can be disconnected after being created by setting the Connection property to Nothing. Thereafter they exist only in memory, facilitating some quite radical functionality. They reveal their true power when used as a Form Recordset where they are an elegant alternative to temporary tables.

 

[rikklaney1]

Stupid question here. This tells me that my list box rowsourcetype must be set to value list. So how can I convert a select statement based on a value enter by the operator into a value list from a table/query?