Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Ordering a Listbox where RowSourceType = "Value List"

    Hi,

    I have a listbox which is populated by a value list like so
    Code:
    For i = 0 To CurrentDb.TableDefs(tableName).Fields.Count - 1
        Me.MyListBox.AddItem (CurrentDb.TableDefs(tableName).Fields(i).Name)
    Next
    Imagine the resulting listbox contents were like this
    Code:
    Abc
    Def
    Ghi
    Xyz
    Def
    How would I go about ordering these items alphabetically?
    I figure I might have to put the values into an array and bubble sort; but that seems like overkill to me!

    And just to extend the problem further:
    Once they are in alphabetical order I wish to add a new item "Tuv" - how would I go about adding this in the correct place?

    Cheers
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    How would I go about ordering these items alphabetically?
    I figure I might have to put the values into an array and bubble sort; but that seems like overkill to me!

    And just to extend the problem further:
    Once they are in alphabetical order I wish to add a new item "Tuv" - how would I go about adding this in the correct place?
    TNK an array and sorting algorithm is your best bet. Alternatively, maybe you can populate a recordset and sort that. Or populate a table and set that as record source.

    Check out my code for twin listboxes in the code bank - that includes moving stuff around in value lists. I cannot remember if it is all that efficient but you might get ideas from it at least.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd populate a table and sort it that way.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's my working bubble sort algorithm so far; going to play with it and it's performance before deciding if it's the right method
    Code:
    Public Function bubbleSort()
    
    Dim i As Integer
    Dim j As Integer
    Dim temp As String
    
    Dim fullStr As String
        fullStr = "5,2,9,1,3,6,7,4,5"
    
    Dim recordArray As Variant
        recordArray = Split(fullStr, ",")
                
    Dim numRecs As Integer
        numRecs = UBound(recordArray)
                
    Dim pointer As Integer
        pointer = 0
                
        For i = 0 To numRecs
            pointer = 0
            
            For j = 0 To numRecs - 1
                If recordArray(j) >= recordArray(j + 1) Then
                    temp = recordArray(j)
                    recordArray(j) = recordArray(j + 1)
                    recordArray(j + 1) = temp
                Else
                   pointer = pointer + 1
                End If
                
                If pointer = numRecs Then
                    GoTo DisplayResults
                End If
            Next j
            
        Next i
    
    DisplayResults:
        For i = 0 To UBound(recordArray)
            Debug.Print recordArray(i)
        Next i
        
    End Function
    Bubble sorts aren't exactly efficient, but for this purpose I should never have more than 1000 records, so it shouldn't be terrible. I'm going to play with the numbers when I can.

    Thanks for your suggestions so far guys
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's my improved (and commented) BubbleSort function for a 1 dimensional array
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function BubbleSort(delimitedString As Variant, inputDelimiter As String, Optional outputDelimiter As String, Optional sortDesc As Boolean) As String
    
    On Error GoTo ErrorHandler
    
    Dim i As Integer
    Dim j As Integer
    Dim holdRec As Variant
    Dim sortedResults As String
    Dim direction As Integer
    
    'Create an array from the delimitedstring and delimiter passed as arguements to this function
    Dim recordArray As Variant
        recordArray = Split(delimitedString, inputDelimiter)
        
    'Calculate number of records in the array
    Dim numRecs As Integer
        numRecs = UBound(recordArray)
    
    'Declare our pointer. When pointer = numRecs then the bubblesort is complete
    Dim pointer As Integer
        
        'If no output delimiter specified, use the input one
        If Nz(outputDelimiter) = "" Then
            outputDelimiter = inputDelimiter
        End If
            
        'Start sorting!
        For i = 0 To numRecs
            'Reset pointer for each run
            pointer = 0
            
            'Loop through array
            For j = 0 To numRecs - 1
                'If current record is greater than or equal to the next record
                If recordArray(j) >= recordArray(j + 1) Then
                    'Swap current and next records
                    holdRec = recordArray(j)
                    recordArray(j) = recordArray(j + 1)
                    recordArray(j + 1) = holdRec
                Else
                    'items in correct order; increment pointer
                    pointer = pointer + 1
                End If
                
                'Check pointer/sort complete
                If pointer = numRecs Then
                    'Exit loops
                    GoTo DisplayResults
                End If
            Next j
            
        Next i
    
    DisplayResults:
        'Iitialise sortedResults variable
        sortedResults = ""
            
            If sortDesc Then
                'Loop through our sorted array and create delimited string for output
                For i = numRecs To 0 Step -1
                    sortedResults = sortedResults & recordArray(i) & outputDelimiter
                Next i
            Else
                For i = 0 To numRecs
                    sortedResults = sortedResults & recordArray(i) & outputDelimiter
                Next i
            End If
            
            'Remove final delimiter
            sortedResults = Left(sortedResults, Len(sortedResults) - 1)
        
        'Return result
        BubbleSort = sortedResults
    
        Exit Function
    
    ErrorHandler:
        MsgBox Err.Description, vbCritical, "Unhandled Error"
        BubbleSort = ""
        
    End Function
    Example of how to call this
    Code:
    Dim i As Integer
    Dim items As String
    
        items = ""
        For i = 0 To Me.myListBox.ListCount - 1
            items = items & Me.myListBox.Column(0, i) & ";"
        Next i
        
        items = Left(items, Len(items) - 1)
        
        MsgBox BubbleSort(items, ";", , True)
    All and any comments appreciated
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for a thousand records I'd be minded to bung 'em in a local table and rely on JET's sorting / marshalling capabilites.. bound to be faster than a bubble sort, or even a comb sort, especially if the data doesn't change just the required order.....

    just out of curiosity is there no way you can tweak the original collection to see if there is a another method of retrieving the data.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The package I am developing is a very generic system - it is meant to be able to be attached to any ms access database as an add-on...

    Because of it's generality, I think a temp table should be avoided (certainly if I'm having to create and destroy them on the fly!

    I'll be posting the app in the code bank, when it's finished, for comments and general use - it might interest you to take a look for alternate methods

    If you MOD-er-ific types fancy a preview then PM me and I'll sort something out!
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh, and before I forget;

    I'm working ona 2 dimensional bubble sort; which I don't want to abandon, but might have to because it gets wildly confusing and complicated very quickly.

    That sounds like a challenge to me... Get to it!
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Isn't the two dimension requirement enough to tip things in the favour of a table? Doesn't need to be created & trashed on the fly (although you could of course) - just a few utility columns of type variant.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - it means you won't have a heart attack when someone one day says "actually we need a three\ four\ n dimension array sorting".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •