Sorting a list box in MS Access

I know, off topic, but do you know a way to sort the content of an unbound listbox in Access 2000

Dolf

 

not off topic anymore

what is the row source and row source type?

If you are using a table or query, use the query builder to add a sort order to the recordet.

if you are using a list, order the list as you write the values in.

 

what I want to accomplish, is transferring data from one listbox to a second one.
The first time it's working well, because in the first listbox, the data has already been sorted, but when you modify the first transfer, it becomes a mess, because with the second move the data will just be added to the list.
Does this make any sense?
Dolf

like this:

 

I mean a mess like this

 

honestly not really

I don't know what type of algorithm you are using to accomplish this. I you wish to send me what you have , I can try to get it working for you. Or you could post some more implementation details, that might help.

OH the pics help abit. That is TDS is it not?

 

It seems I'm not the only one, having this problem....

For now I got only way traffic:

Code:

Private Sub cmdSelect_Click()
    Dim strItems As String
    Dim strItems2 As String
    Dim intItem As Integer
    
    For intItem = Keuzelijst1.ListCount - 1 To 0 Step -1
        If Keuzelijst1.Selected(intItem) Then
            strItems = strItems & Keuzelijst1.Column(0, intItem) & ";" & _
                     Keuzelijst1.Column(1, intItem) & ";" & _
                     Keuzelijst1.Column(2, intItem) & ";"
         Else
            strItems2 = strItems2 & Keuzelijst1.Column(0, intItem) & ";" & _
                     Keuzelijst1.Column(1, intItem) & ";" & _
                     Keuzelijst1.Column(2, intItem) & ";"
        End If
        
    Next intItem
    If Keuzelijst3.ListCount > 0 Then
        For intItem = 0 To Keuzelijst3.ListCount - 1
            strItems = strItems & Keuzelijst3.Column(0, intItem) & ";" & _
                      Keuzelijst3.Column(1, intItem) & ";" & _
                      Keuzelijst3.Column(2, intItem) & ";"
         
        Next intItem
    End If
    Keuzelijst3.RowSource = ""
    Keuzelijst3.RowSourceType = "Value List"
    Keuzelijst3.RowSource = strItems
    Keuzelijst1.RowSource = ""
    Keuzelijst1.RowSourceType = "Value List"
    Keuzelijst1.RowSource = strItems2
    
End Sub

 

I think I understand your intentions:

Take all selected list items from one listbox ad add them to a second box.

Since the first box was sorted, the items selected should be gathered in sorted order. You purposely divide the list into two strings, both ordered in reverse sorted order.

you add whatever is already in the the second box to the one string, and set the other string to the fist box.

Assuming the second box starts off empty, any values in there would be in reverse sorted order.

the problem you are having is that you need to sort the contents of Keuzelijst3 with strItems, not just tack them on the end.

 

I created a table to add the value to, used it to sort the values and spit them back into your list. It could be done slicker, but we'd have to modify more of your other stuff. This should do you.

I used the DAO object model, if you use ADO, you'll have to change the recordset code or add the DAO object model into your references.

Code:

Private Sub cmdSelect_Click()
    Dim strItems As String
    Dim strItems2 As String
    Dim intItem As Integer
    
    Dim sql As String
    Dim rs As Recordset
    
    DoCmd.SetWarnings (0)
    On Error Resume Next
    sql = "CREATE TABLE tbl_Keuzelijst3 (col1 text(50), col2 text(50), col3 text(50))"
    DoCmd.RunSQL sql
    sql = "DELETE * FROM tbl_Keuzelijst3"
    DoCmd.RunSQL sql
    DoCmd.SetWarnings (1)
        
    sql = "SELECT * FROM tbl_Keuzelijst3"
    Set rs = CurrentDb.OpenRecordset(sql)
    
    
    For intItem = Keuzelijst1.ListCount - 1 To 0 Step -1
        If Keuzelijst1.Selected(intItem) Then
            rs.AddNew
            rs!col1 = Keuzelijst1.Column(0, intItem)
            rs!col2 = Keuzelijst1.Column(1, intItem)
            rs!col3 = Keuzelijst1.Column(2, intItem)
            rs.Update
 
        Else
            strItems2 = strItems2 & Keuzelijst1.Column(0, intItem) & ";" & _
                    Keuzelijst1.Column(1, intItem) & ";" & _
                    Keuzelijst1.Column(2, intItem) & ";"
        End If
        
    Next intItem
    
    If Keuzelijst3.ListCount > 0 Then
        For intItem = 0 To Keuzelijst3.ListCount - 1
            rs.AddNew
            rs!col1 = Keuzelijst3.Column(0, intItem)
            rs!col2 = Keuzelijst3.Column(1, intItem)
            rs!col3 = Keuzelijst3.Column(2, intItem)
            rs.Update
        
        Next intItem
    End If
    
    rs.Close
    sql = "SELECT * FROM tbl_Keuzelijst3 ORDER BY col3"
    Set rs = CurrentDb.OpenRecordset(sql)
        
    While Not rs.EOF
        strItems = strItems & rs!col1 & ";" & rs!col2 & ";" & rs!col3 & ";"
        rs.MoveNext
    Wend
    rs.Close
    
    Keuzelijst3.RowSource = ""
    Keuzelijst3.RowSourceType = "Value List"
    Keuzelijst3.RowSource = strItems
    Keuzelijst1.RowSource = ""
    Keuzelijst1.RowSourceType = "Value List"
    Keuzelijst1.RowSource = strItems2
    
End Sub

 

PERFECT,
Thanks a lot.
I was searching the net now for about two days and all I found were people with the same problem
I wish I could give you a box of karma cookies!

I'll pass this link on to Wayne
Dolf

 

if you want to do sorting outside of access (no tables to use), you could have just used a two dimentional array and any one of the sort algorithms like bubblesort, mergesort, quicksort, radixsort, heapsort etc etc.

I just used tables because in access that is fastest.

 

Yes, I like that idea to sort it without using tables, but I'm afraid I don't know any of those algorithms you suggested. Any links?
Thanks
Dolf

 

yes, the best one I know of is http://www.google.com/

 

Ok sorry.
I did search google, but what I got was related to C+, pearl, PHP etc. , i'll try to narrow it down.
Dolf

 

http://www.joyofvb.com/JVB_AlgorithmToolkit.htm

http://www.vb-helper.com/tut1.htm

 

Thanks again
Dolf

 

Think nothing of it freind. Just happy to help