Sorting Report Names in a Combo Box

[Aliza Klein]

Hi.

I would like to provide my user with a list of the available reports in a
drop-down combo that can then be selected and printed (after clicking on a
button).

Currently, to populate the combo (cbReports) I use a Value List as a
rowsource, get each report name and build a string with the names. The
problem is the resulting list is not alphabetized. I suspect there is a
more efficient way of doing this that would also result in an alphabetized
list.

Any suggestions? The following is the code I currently use:
-------------------------

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

'clear out the old list
cbReports.RowSourceType = "Value List"
cbReports.RowSource = ""
cbReports.Requery

sRpts = ""

For Each obj In dbs.AllReports
'get the name, make a string with the name and add it
If sRpts = "" Then
sRpts = obj.Name
Else
sRpts = sRpts + ";" + obj.Name
End If
Next obj

cbReports.RowSource = sRpts
cbReports.Requery

 

[fredg]

Hi.

I would like to provide my user with a list of the available reports in a
drop-down combo that can then be selected and printed (after clicking on a
button).

Currently, to populate the combo (cbReports) I use a Value List as a
rowsource, get each report name and build a string with the names. The
problem is the resulting list is not alphabetized. I suspect there is a
more efficient way of doing this that would also result in an alphabetized
list.

Any suggestions? The following is the code I currently use:
-------------------------

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

'clear out the old list
cbReports.RowSourceType = "Value List"
cbReports.RowSource = ""
cbReports.Requery

sRpts = ""

For Each obj In dbs.AllReports
'get the name, make a string with the name and add it
If sRpts = "" Then
sRpts = obj.Name
Else
sRpts = sRpts + ";" + obj.Name
End If
Next obj

cbReports.RowSource = sRpts
cbReports.Requery

Change the Combo Row Source Type property to Table/Query.
Change the Row Source to:

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

 

[Aliza Klein]

AHA!

I knew there had to be a better way!

Thanks for the quick (and helpful!) response!
Aliza

Hi.

I would like to provide my user with a list of the available reports in a
drop-down combo that can then be selected and printed (after clicking on a
button).

Currently, to populate the combo (cbReports) I use a Value List as a
rowsource, get each report name and build a string with the names. The
problem is the resulting list is not alphabetized. I suspect there is a
more efficient way of doing this that would also result in an alphabetized
list.

Any suggestions? The following is the code I currently use:
-------------------------

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

'clear out the old list
cbReports.RowSourceType = "Value List"
cbReports.RowSource = ""
cbReports.Requery

sRpts = ""

For Each obj In dbs.AllReports
'get the name, make a string with the name and add it
If sRpts = "" Then
sRpts = obj.Name
Else
sRpts = sRpts + ";" + obj.Name
End If
Next obj

cbReports.RowSource = sRpts
cbReports.Requery

Change the Combo Row Source Type property to Table/Query.
Change the Row Source to:

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