VBA to list Reports using ListBox 1

[eggy168]

Hi,

I finally found an easier way to list all the reports in the Access database instead of showing so many different forms to list the reports.
However, I am stuck into showing and listing the reports in the list box.

Here is the code in the Form_Load,

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

Here are the questions,
~I only want to show the main reports and no need to show those subreports that have the name beginning on srpt_xxx. Can I not show them in the List Box?
~Then I want to show the reports in ascending order.
~Is it workable?

Thanks you very much.

 

[Remou]

How about a query on the MSysObjects system table?

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name)<>"srpt_*") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

 

[RoyVidar]

I would usually establish a table for reports, where I either only added those reports users should be able to see, or use some status/category/type field on which to filter. That makes it possible to do lot of interesting stuff with reports dynamically. In that case, it would be very easy to also do the sort.

To your question, compare the string while concatenating, and for the sorting, well, I think you need to stuff it into an array or something to sort - anyway, here's a MS KB on the topic

http://support.microsoft.com/kb/275563

Note that the MS suggestion also mentions the method demonstrated by Remou, with a warning... I doubt MS will change the system tables, so it is probably safe, though.

Roy-Vidar

 

[eggy168]

How can I use Remou's method? Should I just place the sql script into the RecordSourceType in the ListBox?

I tried the method in the link, RoyVidar, however, it said the error after I changed the RecordSourceType into the code's name. I wonder did I do something wrong?

Thanks

 

[RoyVidar]

There's a description in the linked page about how to use it where it says "Method 2", but basically yes - "table/query" rowsourcetype and the query/sql as rowsource.

Roy-Vidar

 

[eggy168]

Now, it is in alphabetical order, but I still don't know how to eliminate the reports that I don't want to be list in the ListBox. Any ideas?
Thanks

 

[Remou]

Use the Where statement, or better yet, go with RoyVidar's idea of a table of reports. It may take a little time to set up, but so useful, once you have it.

 

[PHV]

I still don't know how to eliminate the reports that I don't want to be list in the ListBox
SELECT Name FROM MSysObjects WHERE Left(Name,5)<>"srpt_" AND Type=-32764 ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886