Build Query to Collect Names of Report Objects

[S Jackson]

Is there a way to build a table or make table query that would give you a
list of all your reports in your database. And, could you some sort of
criteria selection, for example, Like "rpt*" ???

S. Jackson

 

[John Vinson]

Is there a way to build a table or make table query that would give you a
list of all your reports in your database. And, could you some sort of
criteria selection, for example, Like "rpt*" ???

S. Jackson

It's undocumented and may change with versions, but:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
AND [Name] LIKE "rpt*";

will do it.

Alternatively, write VBA code to loop through the Containers
collection.

 

[S Jackson]

Works like a charm! Thanks!

You guys are the best.

S. Jackson

Is there a way to build a table or make table query that would give you a
list of all your reports in your database. And, could you some sort of
criteria selection, for example, Like "rpt*" ???

S. Jackson

It's undocumented and may change with versions, but:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
AND [Name] LIKE "rpt*";

will do it.

Alternatively, write VBA code to loop through the Containers
collection.

 

[Sandra Daigle]

In addition to John's method, you can loop through the documents
collection - there is an example of this in my report dialog sample on
http://www.daiglenet.com/msaccess.htm. Here is the code:

Public Sub LoadRptTable()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
On Error GoTo Proc_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Document
Dim rpt As Report
Dim prop As DAO.Property
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblReports")
With rst
For Each doc In db.Containers("Reports").Documents
.AddNew
.Fields("Rptfilename") = doc.Name
.Fields("rptName") = doc.Name
.Fields("RptDescription") = doc.Properties("description")
.Update
Next doc
End With

Proc_Exit:
Exit Sub

Proc_Err:
Select Case Err.Number
Case 3270
'Description not set on Current report
Resume Next
Case 3022
'Report is already in table
Resume Next
Case Else
MsgBox "An error has occurred: " & vbCrLf _
& "Error Number: " & Err.Number & vbCrLf _
& "Description: " & Err.Description
End Select

End Sub

 

[S Jackson]

Thanks Sandra. I originally looked at your example when I designed my
database. I incorporated your example to the best of my understanding - my
experience is minimal and I was unable to understand it completely. I did
not use much of your coding as I did not understand it and I didn't want to
put myself into a bad position if something went wrong and I couldn't fix it
because I didn't understand it. What I did do is follow your example by
creating a reports table, groups table and a group members table.

I am building a form that will step the user through creating a report,
adding the reports to the reports table. I created a second form where the
user then assigns the report to a group (or creates a new group if desired).

On the form to add the newly created report to the reports table I am having
problems. I have not added all the controls yet, but started off with just
three:

RptFileName, RptName and RptDescription

I used a query as the control source of the form. Here is the sql:

SELECT MSysObjects.Name, tblReports.*
FROM MSysObjects LEFT JOIN tblReports ON MSysObjects.Name =
tblReports.RptFileName
WHERE (((MSysObjects.Type)=-32764));

This gives me a list of all the reports in my db without duplicates.

I want the user to select the RptFileName from a drop down list. I used an
unbound combo box (RptFilecmbo) and its source is:
SELECT MsysObjects.Name
FROM MsysObjects LEFT JOIN tblReports ON MsysObjects.Name =
tblReports.RptFileName
WHERE (((MsysObjects.Type)=-32764));

The problem is that after the user selects a report from the RptFilecmbo, I
want the RptName and RptDescription controls to update themselves based on
the value selected in the RptFilecmbo control. Here's what I have so far on
the AfterUpdateDate event property of the RptFilecmbo combo box:

strFilter = "RptFileName = Forms!fsubRpts!RptFilecmbo"
Me.RptName = DLookup("RptName", "tblReports", strFilter)
Me.RptDescription = DLookup("RptDescription", "tblReports", strFilter)

Of course this does not work because I get a message I cannot update the
record source - I assume this is referring to MSysObjects, right? How can I
make this work? Am I making this more difficult than it has to be? I've
gone around and around with this for the past 3 hours and I am getting more
confused by the minute.

Any thoughts are greatly appreciated!
TIA, S. Jackson

In addition to John's method, you can loop through the documents
collection - there is an example of this in my report dialog sample on
http://www.daiglenet.com/msaccess.htm. Here is the code:

Public Sub LoadRptTable()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
On Error GoTo Proc_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Document
Dim rpt As Report
Dim prop As DAO.Property
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblReports")
With rst
For Each doc In db.Containers("Reports").Documents
.AddNew
.Fields("Rptfilename") = doc.Name
.Fields("rptName") = doc.Name
.Fields("RptDescription") = doc.Properties("description")
.Update
Next doc
End With

Proc_Exit:
Exit Sub

Proc_Err:
Select Case Err.Number
Case 3270
'Description not set on Current report
Resume Next
Case 3022
'Report is already in table
Resume Next
Case Else
MsgBox "An error has occurred: " & vbCrLf _
& "Error Number: " & Err.Number & vbCrLf _
& "Description: " & Err.Description
End Select

End Sub



--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is there a way to build a table or make table query that would give
you a list of all your reports in your database. And, could you some
sort of criteria selection, for example, Like "rpt*" ???

S. Jackson

 

[S Jackson]

Correction:

The control source of the form is tblReports NOT qryReports with the sql I
included in my first message.

Thanks Sandra. I originally looked at your example when I designed my
database. I incorporated your example to the best of my understanding - my
experience is minimal and I was unable to understand it completely. I did
not use much of your coding as I did not understand it and I didn't want to
put myself into a bad position if something went wrong and I couldn't fix it
because I didn't understand it. What I did do is follow your example by
creating a reports table, groups table and a group members table.

I am building a form that will step the user through creating a report,
adding the reports to the reports table. I created a second form where the
user then assigns the report to a group (or creates a new group if desired).

On the form to add the newly created report to the reports table I am having
problems. I have not added all the controls yet, but started off with just
three:

RptFileName, RptName and RptDescription

I used a query as the control source of the form. Here is the sql:

SELECT MSysObjects.Name, tblReports.*
FROM MSysObjects LEFT JOIN tblReports ON MSysObjects.Name =
tblReports.RptFileName
WHERE (((MSysObjects.Type)=-32764));

This gives me a list of all the reports in my db without duplicates.

I want the user to select the RptFileName from a drop down list. I used an
unbound combo box (RptFilecmbo) and its source is:
SELECT MsysObjects.Name
FROM MsysObjects LEFT JOIN tblReports ON MsysObjects.Name =
tblReports.RptFileName
WHERE (((MsysObjects.Type)=-32764));

The problem is that after the user selects a report from the RptFilecmbo, I
want the RptName and RptDescription controls to update themselves based on
the value selected in the RptFilecmbo control. Here's what I have so far on
the AfterUpdateDate event property of the RptFilecmbo combo box:

strFilter = "RptFileName = Forms!fsubRpts!RptFilecmbo"
Me.RptName = DLookup("RptName", "tblReports", strFilter)
Me.RptDescription = DLookup("RptDescription", "tblReports", strFilter)

Of course this does not work because I get a message I cannot update the
record source - I assume this is referring to MSysObjects, right? How can I
make this work? Am I making this more difficult than it has to be? I've
gone around and around with this for the past 3 hours and I am getting more
confused by the minute.

Any thoughts are greatly appreciated!
TIA, S. Jackson

In addition to John's method, you can loop through the documents
collection - there is an example of this in my report dialog sample on
http://www.daiglenet.com/msaccess.htm. Here is the code:

Public Sub LoadRptTable()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
On Error GoTo Proc_Err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim doc As Document
Dim rpt As Report
Dim prop As DAO.Property
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblReports")
With rst
For Each doc In db.Containers("Reports").Documents
.AddNew
.Fields("Rptfilename") = doc.Name
.Fields("rptName") = doc.Name
.Fields("RptDescription") = doc.Properties("description")
.Update
Next doc
End With

Proc_Exit:
Exit Sub

Proc_Err:
Select Case Err.Number
Case 3270
'Description not set on Current report
Resume Next
Case 3022
'Report is already in table
Resume Next
Case Else
MsgBox "An error has occurred: " & vbCrLf _
& "Error Number: " & Err.Number & vbCrLf _
& "Description: " & Err.Description
End Select

End Sub



--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is there a way to build a table or make table query that would give
you a list of all your reports in your database. And, could you some
sort of criteria selection, for example, Like "rpt*" ???

S. Jackson