Combo Box with edit List

[Jmccomas]

How do you sort a combo box that uses an edit list?

[orange]

Can you provide some sample data/combo?

[Micron]

If you mean when you're using an Edit List Items form then base the list on a query that sorts your list items the way you want. When you close that form, requery the combobox.
See Enter data by using a list
https://support.microsoft.com/en-us/...e-07d7200980ce

but think twice before using a multi value list

[Jmccomas]

Thanks for the responce but I am using a Combo Box that saves list for later use. Not using a query for the source.

[Micron]

OK, then you will have to edit that list manually. If you have A;B;C;G;H;I and you want to interject D;E;F in the correct sequence, put the cursor after C, hit return and type D on the new line and so on. Or you can open the form with the combo box in design view and edit the list in the property sheet. VBA code would be a much more complicated solution.

[davegri]

If you really want to sort the combo's actual value list instead of creating a lookup table, then put this in a code module:

Option Compare Database
Option Explicit


Dim newArray() As String


Public Sub BubbleSort(sourceArray() As String, MaxItem As Long)
    Dim strTemp As String, i As Long, j As Long
    For j = 0 To MaxItem - 1
        For i = 0 To MaxItem - 1
            If sourceArray(i) > sourceArray(i + 1) Then     'swap
                strTemp = sourceArray(i)
                sourceArray(i) = sourceArray(i + 1)
                sourceArray(i + 1) = strTemp
            End If
        Next i
    Next j
    newArray = sourceArray
End Sub


Public Sub FormMod(fName As String, cboName As String)
    DoCmd.OpenForm fName, acDesign
    '  "OUTBACK";"TRIM";"PLANT";"CLEAN";"SHIP+";"HANG";"PRE-BOOK";"EMPTY";"SPACE";"MOVE";"TOP DRESS"
    Dim aList() As String, i As Long
    Dim SortedRowSource As String
    Dim nLen As Long
    aList = Split(Forms(fName).Controls(cboName).RowSource, ";")
    nLen = UBound(aList)
    Call BubbleSort(aList, nLen)
    For i = 0 To nLen
        If Len(newArray(i)) > 0 Then
            SortedRowSource = SortedRowSource & newArray(i) & ";"
        End If
    Next
    'Debug.Print SortedRowSource
    Forms(fName).Controls(cboName).RowSource = SortedRowSource
    DoCmd.Close acForm, fName, acSaveYes
End Sub

Then at BOJ, call the code like this from the autoexec macro, or in the open event of your main form (not the target form).

call FormMod("frmExample","cboList")

The call requires 2 parameters, the form name and the combobox name.

Before


After

[Micron]

I don't see the point of all that, but maybe that's just me. What is shown in post 4 is a highly simplified and built in version of all that code and form, and does not use a lookup table either. Perfect enough for value list items IMO, but I consider queries with lookup tables the way to go. Much more versatile.

[davegri]

I don't see the point of all that, but maybe that's just me. What is shown in post 4 is a highly simplified and built in version of all that code and form, and does not use a lookup table either. Perfect enough for value list items IMO, but I consider queries with lookup tables the way to go. Much more versatile.

If items need be added manually, just use the built in editor to add to the bottom and the list will be sorted at next BOJ. Saves you the trouble of a manual rearrange.
However, as stated, I wouldn't do it this way. I would use a lookup table and a Not-in-List event for the combo.

[Micron]

If items need be added manually, just use the built in editor to add to the bottom and the list will be sorted at next BOJ

Or put cursor where you want to add an item below, hit enter, type in item - done. No editing form, no BOJ procedures to maintain?

[davegri]

Still be helpful if list is large and unsorted.