Microsoft KB Archive/275563

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

Article ID: 275563

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q275563



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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.



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.



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
 Open the sample database Northwind. In the Database window, click Forms, and then click New. In the New Form dialog box, click Design View, and then click OK. Add a combo box control to the form.  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&quot; Top: 0.5&quot; Width: 2&quot; </li>  Right-click the label, click Properties, and then set the following properties for the label: <pre class="fixed_text">  Name: lblReports Caption: Reports: Left: 1&quot; Top: 0.5&quot; Width: 1&quot; FontWeight: Bold TextAlign: Right </li>  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 </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.</li> On the View menu, click Form View. Note that the reports are listed in alphabetical order.</li></ol>

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. <ol> Open the sample database Northwind.</li> In the Database Window, click Forms, and then click New.</li> In the New Form dialog box, click Design View, and then click OK.</li> Add a combo box control on the form.</li>  Right-click the combo box, click Properties, and then set the following properties for the combo box: <pre class="fixed_text">Name: cmbReports RowSourceType: Table/Query LimitToList: Yes Left: 2&quot; Top: 0.5&quot; Width: 2&quot; </li>  Assign the following SQL statement to the RowSource property: SELECT Name, Type FROM MSysObjects WHERE Type=-32764 ORDER BY Name; </li>  Right-click the label, click Properties, and then set the following properties for the label: <pre class="fixed_text">Name: lblReports Caption: Reports: Left: 1&quot; Top: 0.5&quot; Width: 1&quot; FontWeight: Bold TextAlign: Right </li> On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.</li> On the View menu, click Form View. Note that the reports are listed in alphabetical order.</li></ol>

<div class="moreinformation_section">

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. <ol> Open the sample database Northwind.</li> In the Database window, click Forms, and then click New.</li> <li>In the New Form dialog box, click Design View, and then click OK.</li> <li>Add a combo box control to the form.</li> <li> Right-click the combo box, click Properties, and then set the following properties for the combo box: <pre class="fixed_text">  Name: cmbReports RowSourceType: fncGetReports LimitToList: Yes Left: 2&quot; Top: 0.5&quot; Width: 2&quot; </li> <li> Right-click the label, click Properties, and then set the following properties for the label: <pre class="fixed_text">  Name: lblReports Caption: Reports: Left: 1&quot; Top: 0.5&quot; Width: 1&quot; FontWeight: Bold TextAlign: Right </li> <li> 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 </li> <li>On the File menu, click Close and Return to Microsoft Access.</li> <li>On the File menu, click Save, type frmListReports in the Form Name box, and then click OK.</li> <li>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.</li></ol>

<div class="references_section">