Anyone know command to get all reports in a db? 1

[WhiteZiggy]

I want to get all the reports in my db into a recordset so I can poplate a drop down.

Any ideas on how to get this info? I could make it a table, but then i have to update it.

I would prefer to do something like

rsReports = currentdb.listreports

(I know thats not real, but just as an example)


I can hardcode if i need .. Just curious if there's a way to get all the reports from the db at runtime..

Thanks..

 

[WhiteZiggy]

Got it....Here it is for all interested...

Sub AllReports()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject


For Each obj In dbs.AllReports
' Print name of obj.
Debug.Print obj.Name

Next obj
End Sub

 

[jrbarnett]

You will need a reference to the Microsoft DAO 3.6 Object library if using Access 2000, XP or 2003, but the following code will work:

Dim Db As DAO.Database
Dim doc As DAO.Document
Dim cnt As DAO.Container

Set db = CurrentDb
Set cnt = CurrentDb.Documents!Reports
For Each Doc In cnt.Documents
  Debug.Print doc.Name
Next
Set db = Nothing

John

 

[rdodge]

You can try something like:

Private Sub ComboBox_GotFocus()
Dim Rept as Report
ComboBox.RowSource = ""
For Each Rept in Application.CurrentProject.AllReports
ComboBox.AddItem(Report.Name)
Next
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[JeremyNYC]

Howzabout
Select Name FROM msysObjects WHERE Type = -32764

You can just put that in a combo box's rowsource, if that's what you're looking to do.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[WhiteZiggy]

Thanks all..

Note .. John, I think that is the one that only works on open reports...
Got this to do all reports even if not open..

Sub AllReports()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject


For Each obj In dbs.AllReports
' Print name of obj.
Debug.Print obj.Name

Next obj
End Sub

Thanks all...

Jeremy,

Select Name FROM msysObjects WHERE Type = -32764

I think i will try that...

Thanks...

(from Jeremy too )

 

[georgp]

Jeremy,

very nice. Do you have also the type numbers for the other db objects (or how can I find them)?

Thanks, georgp

 

[danvlas]

Geez I love that MSysObjects table (using it for ages) [LOL]

Table type: 1 (or 6 for linked tables)
Queries: 5
Forms: -32768

Go to Tools-Options and make sure you show system objects.
Then open it and see what goodies it has there...

Or...create a new query:
Select * From MSysObjects;
and run it

Jeremy, I apologize...but just couldn't resist the temptation...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[JeremyNYC]

Dan,

No need to apologize--you're helping keep my posts/stars ratio nice and low. Oooh, blew that, didn't I? <G>

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[georgp]

Daniel,

thanks for the info. May be this is a too long story but since you use them for ages - what do you do with these tables?

georgp