Get all reports

[nkuebelbeck]

Is it possible to populate a list box or some other control with all reports on opening of a form? Not even sure where to start here.....thanks in advance



update - I am using Access 2007

[nkuebelbeck]

http://www.everythingaccess.com/tuto...ilable-Reports

[khalid]

you can do this by the following code: this code will print All the reports names in immediate window in the current database:

Private Sub Command6_Click()
Dim doc As Document
Dim cont As Container

With CurrentDb
    For Each cont In .Containers
        If cont.Name = "Reports" Then
            For Each doc In cont.Documents
            Debug.Print doc.Name
        Next doc
        End If
    Next cont
End With
End Sub

change the Debug.Print doc.Name line to populate your list box with all the reports name in the database:

hope this would help.

[nkuebelbeck]

Would the same work for onopen event? Also will it work for Forms and Queris?

[khalid]

This will work for all the documents in the current database like Quires, reports, tables, pages, modules, relationships etc:

you can test it and by trying to change
If cont.Name = "Reports" Then
this line of code, change it to "Forms" etc.

Yes, on form opening event put this code to get the desire result.

Not tested for Queries...

[khalid]

Working for Queries also.

to add the doc names to a list box list0

add this line


Me.List0.AddItem (doc.Name)

--------
Dim doc As Document
Dim cont As Container

With CurrentDb
For Each cont In .Containers
If cont.Name = "Reports" Then
For Each doc In cont.Documents
Me.List0.AddItem (doc.Name)
Next doc
End If
Next cont
End With
End Sub

your list box should be set to Value List, to add the doc.name to it.

[boblarson]

This is another way that I do it. I use a query for the listbox's row source and then I have my reports so that until I add the rpt_ prefix to them they remain hidden so that I don't get subreports showing up in the list nor do I get reports I may not be ready to deploy. So, here's the query (and it can be used with other objects too):

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName 
FROM MsysObjects 
WHERE (((Left([Name],4))="rpt_")) 
ORDER BY MsysObjects.Name;

[khalid]

This will remove the first 4 letters of the MsysObjects.Name: (~sq) and will show where rpt_ is started.

WHERE (((Left([Name],4))="rpt_"))

but also all the others objects first names as well in the database if Remove the WHERE clause in the criteria.

[boblarson]

This will remove the first 4 letters of the MsysObjects.Name: (~sq) and will show where rpt_ is started.



but also all the others objects first names as well in the database if Remove the WHERE clause in the criteria.

You have me confused. I don't get what your post is trying to say. Sorry.

[khalid]

Sorry if I could not explain:

The code below:

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName
FROM MsysObjects
WHERE (((Left([Name],4))="rpt_"))
ORDER BY MsysObjects.Name;

is Selecting MsysObjects.Name and getting its 5th position, leaving the first 4 letters, and then getting its name's 4th letter WHERE its name starts with "rpt_".
If there is no prefix of "rpt_" in the objects name, it will not print anything, so if you omit the WHERE clause criteria, it truncates the objects names.
, Mid([Name],5) AS QueryName

astonishingly the posting numbers are same for both 189/189 with this post

[boblarson]

Yes, this particular code (as I explained) is for REPORTS only. I use it for reports only. Any other use would need to have it adjusted to use the correct object type instead or some other naming.

But what I was pointing out, is that (since REPORTS was the original question) how you could have a listbox with a row source that you never have to change and you can add or remove reports from the list as you need by just renaming them to either have the rpt_ as the first part of the name or to NOT have it as part of the name and then the list would update automatically. It works great. You can do the same with forms if you use frm_ and then change the where to look for frm_.

Many ways to do things and sometimes different ones can be useful if specific behavior is desired.

[khalid]

Is it possible to populate a list box or some other control with all reports on opening of a form? Not even sure where to start here.....thanks in advance

update - I am using Access 2007

The OP was requesting to have all the reports names in a list box. Getting names of objects through your Select Query is good idea, and of course this is new way for me to have all the objects names through query

I use the Reportname_rpt, FormName_frm, TableName_tbl,.... in my db

[boblarson]

I know they wanted all report names. And I was hopefully successful in pointing out that they would also have subreports there as well, which probably isn't a good thing.

[nkuebelbeck]

SELECT [Name] FROM MsysObjects
WHERE (([Type] = -32764) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];

Table 1
Query 5
Form -32768
Report -32764
Module -32761