How do you sort a combo box that uses an edit list?
How do you sort a combo box that uses an edit list?
Can you provide some sample data/combo?
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
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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:
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).Code: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
call FormMod("frmExample","cboList")
The call requires 2 parameters, the form name and the combobox name.
Before
After
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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.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.
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.
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?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
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Still be helpful if list is large and unsorted.