Sorting Report Names in a Combo Box

  • Thread starter Thread starter Aliza Klein
  • Start date Start date
A

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
 
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
Click to expand...

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;
 
AHA!

I knew there had to be a better way!

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

fredg said:
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
Click to expand...

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;
Click to expand...
 
Back
Top