Listbox order

[Ken Warthen]

I use the following code to populate a listbox with report names on an
Access 2007 form.

Dim objReport As Object
For Each objReport In CurrentProject.AllReports
Me.lstReports.AddItem (objReport.Name)
Next objReport

The code adds report items as it finds them so there is no order to the
displayed report names. Is there any way to alpha sort the report names?

TIA,

Ken

 

[Maurice]

Ken,

I use a query to get the same result. This way you can set the sort order as
well.

Create a new query based on the Msysobjects table (maybe you have to make it
visible first via the nav.pane)

drag the fields type and name to the grid.
set a criteria to the type field being -32764 (this displays all reports)
set the sort order the way you like it

Because you don't want the users to see this query you could also set this
via code if you prefer this. In that case set the rowsource of the listbox to:

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

set number of columns to 2 where the first column width is set to 0

if you set it via code you don't have to save a query and users will never
know it's there. Furthermore you don't have to show the systemtables as well.

hth

 

[Klatuu]

There is no Order property for a list box. When using a value list, the only
thing you could do would be to read the items into an array, sort the array,
then add the values to the list from the array.

 

[Ken Warthen]

Maurice,

Thanks for the advice and solution.

Ken

 

[fredg]

I use the following code to populate a listbox with report names on an
Access 2007 form.

Dim objReport As Object
For Each objReport In CurrentProject.AllReports
Me.lstReports.AddItem (objReport.Name)
Next objReport

The code adds report items as it finds them so there is no order to the
displayed report names. Is there any way to alpha sort the report names?

TIA,

Ken

The following works in previous versions. I would expect it to work in
Access 2007 as well.

just use SQL to load the listbox.

Set the ListBox RowsourceType to:
Table/Query
Set the ListBox Rowsource to:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;