Sorting a list box in MS Access

Discussion in 'other software & services' started by DolfTraanberg, Aug 7, 2003.

Thread Status:
Not open for further replies.
  1. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    I know, off topic, but do you know a way to sort the content of an unbound listbox in Access 2000

    Dolf
     
  2. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    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.
     
  3. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    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:
     

    Attached Files:

  4. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    I mean a mess like this :D
     

    Attached Files:

  5. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    honestly not really :doubt:

    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?
     
  6. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    :D 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
    
     
  7. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    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.
     
  8. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    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
    
    
    
    
     
  9. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    PERFECT,
    Thanks a lot.
    I was searching the net now for about two days and all I found were people with the same problem :D
    I wish I could give you a box of karma cookies!

    I'll pass this link on to Wayne :D
    Dolf
     
  10. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    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.
     
  11. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    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
     
  12. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    yes, the best one I know of is http://www.google.com/

    :D
     
  13. DolfTraanberg

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    Ok sorry. :oops:
    I did search google, but what I got was related to C+, pearl, PHP etc. , i'll try to narrow it down.
    Dolf
     
  14. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    http://www.joyofvb.com/JVB_AlgorithmToolkit.htm

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

    DolfTraanberg Registered Member

    Joined:
    Nov 20, 2002
    Posts:
    676
    Location:
    Amsterdam
    Thanks again :D
    Dolf
     
  16. UNICRON

    UNICRON Technical Expert

    Joined:
    Feb 14, 2002
    Posts:
    1,935
    Location:
    Nanaimo BC Canada
    Think nothing of it freind. Just happy to help :D
     
Loading...
Thread Status:
Not open for further replies.