Sort dynamically filled combo box

[ps40life]

I have a combo box that looks up the reports available in the database. I saw a way of making this dynamic in a VBA book with the following code:

Private Sub Form_Load()
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String

Set objCP = Application.CurrentProject

For Each objAO In objCP.AllReports
strValues = strValues & objAO.Name & ";"
Next objAO

lstReports.RowSourceType = "value list"
lstReports.RowSource = strValues
End Sub

It was taking the names from a table and as new reports were added, I had to constantly update the table. This code works great. Only problem is the list in the combo is not in alphabetical order. How can I set the combo to sort??
Thanks!
Susan

 

[Mongooses]

ps40life,

In order to fill the combo box with sorted values you will have to sort them. Since I can't imagine an astronomical number of Reports, I suggest you use a bubble sort. So read all the Report names into an array, sort the array and then create a string with all the names in order. You can modify your code to reflect something like this:
Private Sub Form_Load()
Dim knt As Integer
Dim i As Integer
Dim j As Integer
Dim aryNames() As String
Dim temp As String
Dim values As String

knt = CurrentProject.AllReports.Count - 1
ReDim aryNames(knt)
For i = 0 To knt
aryNames(i) = CurrentProject.AllReports(i).Name
Next

'Sort the names
For i = 1 To Knt
For j = 0 To knt - i
If aryNames(j) > aryNames(j + 1) Then
temp = aryNames(j)
aryNames(j) = aryNames(j + 1)
aryNames(j + 1) = temp
End If
Next
values = aryNames(j) & ";" & values
Next
values = aryNames(0) & values

lstReports.RowSourceType = "value list"
lstReports.RowSource = values
End Sub
HTH,
jc

 

[ps40life]

Thanks. I will try this out.

 

[ps40life]

Mongoose,
It has been awhile since I posted this, but I finally got around to doing it. It works great!! I did have to change one small thing in the following line of code:

values = aryNames(j) & ";" & values

The first and second report were showing up on the same line, so I just put the ";" before aryNames(j) and it works perfectly.

values = ";" & aryNames(j) & values

So, I would like to take this a little further. Is there a way to filter out some of the reports, since there are subreports and just some that I don't want to show. Let's say I started them all with the same letter or something like that, how could I not show them in the combo box?

Thanks for your help. I really appreciate it!

 

[ps40life]

Can anyone else help me finish this code? Thanks!