Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

Symptoms

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.

↑ Back to the top


Cause

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.

↑ Back to the top


Resolution

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.

↑ Back to the top


More information

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.

↑ Back to the top


References

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.

↑ Back to the top


Keywords: KB275563, kbprb

↑ Back to the top

Article Info
Article ID : 275563
Revision : 6
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 427