Filtering a database report list in combo box 1

[ps40life]

Hi,
I have a combo box on a switchboard form that lists all of the reports in the database alphabetically. The code looks 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

It works great except there are some reports, like subreports, that I would like to leave out. If I gave them all specific prefixes like ZZNameofReport, is there a way to filter them out of the list? Also, there is more info about this on my original post: thread702-534808

Thanks for any help!

 

[legos]

you could use an if not statement with a wildcard character if you use the zznameofreport method. the only problem is that no other report could contain zz.

ifnot aryname(i) Like "ZZ" & * then
sort code
end if

Durible Outer Casing to Prevent Fall-Apart

 

[legos]

you know as far as your first question goes, why don't you just use SQL in the row source area of the combo box?

SELECT Tablename.Fieldname
FROM TableName
ORDER BY Tablename.Fieldname;

that will get all your records and put them in ascending order. If you make changes to the table, just put
me.comboname.requery
at the end of your table update code

if you need any more help, just ask.

Durible Outer Casing to Prevent Fall-Apart

 

[PaulF]

I perfer to query the MSysObjects table to capture names of Forms, Tables, Reports... etc

Select Name from MSysObjects Where Type = -32764 Order By Name


much easier to code. To see the System tables in your database window, select Tools - Options, click on the View tab, and check the System Objects checkbox.

PaulF

 

[dhookom]

I would never show my report object names to users. I create a table of reports that contains information such as ObjectName, ReportTitle, ReportStatus, Author, Description, and other info. This allows me to create a list box displaying the friendlier ReportTitle.

Duane
MS Access MVP

 

[ps40life]

To Lego:
Thanks for the reply. I will try the IF statement, and I am also going to look at PaulF's suggestion. FYI-I was previously pulling the names of the report from a table. Problem is, you have to constantly be adding new report names to keep the list updated. I wanted to automate for my end user, so that's why I want to pull through the database object.

To PaulF:
I hadn't thought about the hidden tables. That may work well. I will try it and get back to you. I may need help!

Thanks for the suggestions!

 

[ps40life]

To dhookom:

I had a table set up, but I am going to be relocating and my user is on her own after that. She is beginning to create her own reports now and I thought it would be easier to code the combo box list to update automatically as she adds new stuff. She is a new user, so she is not going to be using conventional naming standards and she knows nothing about programming.

Thanks!

 

[ps40life]

PaulF,

Thanks so much for the advice. I set up a query on the system table and it works perfectly. I can filter out whatever I don't want to show through the query. Excellent advice!!!

Susan