Programmatically Populated Combo Box or List Box Is Not Sorted in the Order Expected

You are using Visual Basic for Applications (VBA) programming code to assign values to a combo box or a list box. After the code runs, you view the contents of the combo box or the list box. You see that the values listed are not in numeric or alphabetical order.

The order in which the values are assigned is the order in which the values will appear in the combo box or in the list box.

To resolve this unexpected sort order, you can sort an array, and then assign the array to the combo box or the list box. To use this method, follow the steps in Method 1.

If you were assigning database objects to your combo box or list box when you encountered this behavior, another method is to assign the appropriate system table to the RowSource property of the combo box or the list box in sorted order. Be advised, however, that although this method works in Microsoft Access 2000, it is not guaranteed to work with future versions of Access because system tables may change. To use this method, follow the steps in Method 2.

Method 1: Assigning Sorted Array to a Combo Box or a List Box

1. Open the sample database Northwind.
2. In the Database window, click Forms, and then click New.
3. In the New Form dialog box, click Design View, and then click OK.
4. Add a combo box control to the form.
5. Right-click the combo box, click Properties, and then set the following properties for the combo box:

      Name: cmbReports
      RowSourceType: fncGetReports
      LimitToList: Yes
      Left: 2"
      Top: 0.5"
      Width: 2"
   					

6. Right-click the label, click Properties, and then set the following properties for the label:

      Name: lblReports
      Caption: Reports:
      Left: 1"
      Top: 0.5"
      Width: 1"
      FontWeight: Bold
      TextAlign: Right
   					

7. On the View menu, click Code, and then copy or paste the following code:

   Option Compare Database
   Option Explicit
   
   Dim strArray() As String
   
   Function fncGetReports(Ctrl As Control, varID As Variant, _
            varRow As Variant, varCol As Variant, varCode As Variant) _
            As Variant
   
       'Assign all report names to the combo box.
       Dim proj As CurrentProject
   
       Set proj = Application.CurrentProject
   
       Select Case varCode
           Case acLBInitialize
               fncGetReports = True
           Case acLBOpen
               fncGetReports = Timer
           Case acLBGetRowCount
               fncGetReports = proj.AllReports.Count
           Case acLBGetColumnCount
               fncGetReports = 1
           Case acLBGetColumnWidth
               fncGetReports = -1
           Case acLBGetValue
               fncGetReports = strArray(varRow + 1)
       End Select
   End Function
   
   Sub subAddItem(MyList() As String, MyItem As String)
       ReDim Preserve MyList(UBound(MyList) + 1)
       MyList(UBound(MyList)) = MyItem
   End Sub
   
   Private Sub Form_Load()
       Dim obj As AccessObject
       Dim proj As CurrentProject
   
       ReDim strArray(0)
   
       Set proj = Application.CurrentProject
   
       'Search for open AccessObject objects in AllReports collection.
       For Each obj In proj.AllReports
           subAddItem strArray, obj.Name
       Next obj
       
       subSort strArray
   End Sub
   
   Sub subSort(MyList() As String)
       Dim intRet As Integer, intCompare As Integer, intLoopTimes As Integer
       Dim strTemp As String
   
       For intLoopTimes = 1 To UBound(MyList)
           For intCompare = LBound(MyList) To UBound(MyList) - 1
               intRet = StrComp(MyList(intCompare), MyList(intCompare + 1), _
                                vbTextCompare)
               If intRet = 1 Then  'Current string is greater than previous.
                   strTemp = MyList(intCompare)
                   MyList(intCompare) = MyList(intCompare + 1)
                   MyList(intCompare + 1) = strTemp
               End If
           Next
       Next
   End Sub
   					

8. On the File menu, click Close and Return to Microsoft Access.
9. On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.
10. On the View menu, click Form View. Note that the reports are listed in alphabetical order.

Method 2: Assigning Sorted System Table to Combo or List Box

Although this method will work with Microsoft Access 2000, it is not a highly recommended method, as the structure of system tables may change within future versions of Access.

1. Open the sample database Northwind.
2. In the Database Window, click Forms, and then click New.
3. In the New Form dialog box, click Design View, and then click OK.
4. Add a combo box control on the form.
5. Right-click the combo box, click Properties, and then set the following properties for the combo box:

   Name: cmbReports
   RowSourceType: Table/Query
   LimitToList: Yes
   Left: 2"
   Top: 0.5"
   Width: 2"
   					

6. Assign the following SQL statement to the RowSource property:

   SELECT Name, Type FROM MSysObjects WHERE Type=-32764 ORDER BY Name;
   					

7. Right-click the label, click Properties, and then set the following properties for the label:

   Name: lblReports
   Caption: Reports:
   Left: 1"
   Top: 0.5"
   Width: 1"
   FontWeight: Bold
   TextAlign: Right
   					

8. On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.
9. On the View menu, click Form View. Note that the reports are listed in alphabetical order.

Steps to Reproduce the Behavior

To illustrate this behavior, assume that a user wants to create a form that displays all of the reports within a database that are available to be printed.

1. Open the sample database Northwind.
2. In the Database window, click Forms, and then click New.
3. In the New Form dialog box, click Design View, and then click OK.
4. Add a combo box control to the form.
5. Right-click the combo box, click Properties, and then set the following properties for the combo box:

      Name: cmbReports
      RowSourceType: fncGetReports
      LimitToList: Yes
      Left: 2"
      Top: 0.5"
      Width: 2"
   					

6. Right-click the label, click Properties, and then set the following properties for the label:

      Name: lblReports
      Caption: Reports:
      Left: 1"
      Top: 0.5"
      Width: 1"
      FontWeight: Bold
      TextAlign: Right
   					

7. On the View menu, click Code, and then copy or paste the following code:

   Function fncGetReports(Ctrl As Control, varID As Variant, _
            varRow As Variant, varCol As Variant, varCode As Variant) _
            As Variant
   
       'Assign all report names to the combo box.
       Dim obj As AccessObject
       Dim proj As CurrentProject
   
       Set proj = Application.CurrentProject
   
       Select Case varCode
           Case acLBInitialize
               fncGetReports = True
           Case acLBOpen
               fncGetReports = Timer
           Case acLBGetRowCount
               fncGetReports = proj.AllReports.Count
           Case acLBGetColumnCount
               fncGetReports = 1
           Case acLBGetColumnWidth
               fncGetReports = -1
           Case acLBGetValue
               Set obj = proj.AllReports(varRow)
               fncGetReports = obj.Name
       End Select
   End Function
   					

8. On the File menu, click Close and Return to Microsoft Access.
9. On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.
10. On the View menu, click Form View. Note that the reports are not listed in alphabetical order. For example, the Employee Sales by Country report appears second from the bottom instead of immediately following the Customer Labels report.

For more information about populating a combo box or list box programmatically, click Microsoft Access Help on the Help menu, type RowSourceType, RowSource Properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.