Report Description Property

[JimS]

I'm trying to build a table of available reports. I've been able to build the
table with report names pretty easily, but I can't seem to get the
"description" property from the AllReports collection. The description is
entered by right-clicking on the report name and clicking "Properties". It
opens a box where you can type in a description. I want to make that
description a field in my table. How do I reference it?

For whatever value, here's the code so far:

Sub AllReports()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete * from [reportlist]")
For Each obj In dbs.AllReports
DoCmd.RunSQL ("insert into [ReportList] ([ReportID]) values ('"
& obj.Name & "')")
Next obj
DoCmd.SetWarnings True
End Sub

 

[Marshall Barton]

I'm trying to build a table of available reports. I've been able to build the
table with report names pretty easily, but I can't seem to get the
"description" property from the AllReports collection. The description is
entered by right-clicking on the report name and clicking "Properties". It
opens a box where you can type in a description. I want to make that
description a field in my table. How do I reference it?

For whatever value, here's the code so far:

Sub AllReports()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete * from [reportlist]")
For Each obj In dbs.AllReports
DoCmd.RunSQL ("insert into [ReportList] ([ReportID]) values ('"
& obj.Name & "')")
Next obj
DoCmd.SetWarnings True
End Sub

The description is a property of the report Document.

Its not clear to me what environment you're woring in, but
in DAO the reference would be:

CurrentDb.Containers!Reports.Documents!reportname.Properties("Description")

with the caveat that you will get an unknown property error
if you did not enter a description for a report. Error
handling can be used to ignore that situation or provide a
default description.

 

[tina]

hi Marsh. i've been following this thread, trying to figure out how to get
that description. i fiddled with your reference awhile, trying to figure out
how to loop through all reports in the db rather than expliciting naming the
report in the reference. i'm not too swift at this <g> but i finally came up
with something that works. but i'm really fumbling around blindly, so i'm
wondering if there's a better way to get there? i'd be grateful for your
comments/suggestions, etc. tia, tina

On Error Resume Next

Dim rpt As Object

For Each rpt In CurrentProject.AllReports
Debug.Print rpt.Name ' just so i know whose description i'm seeing.
Debug.Print
CurrentDb.Containers!Reports.Documents(rpt.Name).Properties("Description")
Next

I'm trying to build a table of available reports. I've been able to build the
table with report names pretty easily, but I can't seem to get the
"description" property from the AllReports collection. The description is
entered by right-clicking on the report name and clicking "Properties". It
opens a box where you can type in a description. I want to make that
description a field in my table. How do I reference it?

For whatever value, here's the code so far:

Sub AllReports()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete * from [reportlist]")
For Each obj In dbs.AllReports
DoCmd.RunSQL ("insert into [ReportList] ([ReportID]) values ('"
& obj.Name & "')")
Next obj
DoCmd.SetWarnings True
End Sub

The description is a property of the report Document.

Its not clear to me what environment you're woring in, but
in DAO the reference would be:

CurrentDb.Containers!Reports.Documents!reportname.Properties("Description")

with the caveat that you will get an unknown property error
if you did not enter a description for a report. Error
handling can be used to ignore that situation or provide a
default description.

 

[Marshall Barton]

hi Marsh. i've been following this thread, trying to figure out how to get
that description. i fiddled with your reference awhile, trying to figure out
how to loop through all reports in the db rather than expliciting naming the
report in the reference. i'm not too swift at this <g> but i finally came up
with something that works. but i'm really fumbling around blindly, so i'm
wondering if there's a better way to get there? i'd be grateful for your
comments/suggestions, etc. tia, tina

On Error Resume Next

Dim rpt As Object

For Each rpt In CurrentProject.AllReports
Debug.Print rpt.Name ' just so i know whose description i'm seeing.
Debug.Print
CurrentDb.Containers!Reports.Documents(rpt.Name).Properties("Description")
Next

Don't waste your time using AllReports, the Documents
collection also contains all the reports.

Dim doc As Document
Dim strDescr As String
On Error GoTo ErrHandler
For Each doc In CurrentDb.Containers!Reports.Documents
strDescr = doc.Properties("Description") 'might fail
Debug.Print doc.Name & " - " & strDescr
Next doc

Outahere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 3270
strDescr = "<No Description>"
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume Outahere
End Select
End Sub

 

[tina]

thanks for responding, Marsh. i pasted your code into my standard module,
verbatim, and ran it. i'm getting an error message on the line

For Each doc In CurrentDb.Containers!Reports.Documents

which is the same error message i kept getting last night:

3420 - Object invalid or no longer set.

i figured i must be doing something wrong. now i'm wondering if there's a
problem with my copy of Access? i'm testing the code in an A2000 db running
in A2003 on Win2KPro. the code does compile successfully, there are no
MISSING references listed in References, and the Microsoft DAO 3.6 Object
Library is checkmarked. Microsoft ActiveX Data Objects 2.1 Library is also
checkmarked (below the DAO reference in the list order) - might that be a
problem? i'd be grateful for any suggestions you might have for me. tia,
tina

 

[Marshall Barton]

Arrggghhhh, I keep forgetting that CurrentDb is only good on
the line that it is used on. A database object needs to be
set:

Dim db As Database
Dim doc As Document
Dim strDescr As String
On Error GoTo ErrHandler
Set db = CurrentDb()
For Each doc In db.Containers!Reports.Documents
. . .

I never run with both DAO and ADO, but your arrangements
sounds right for this. You should think about qualifying
the declarations though:

Dim db As DAO.Database
Dim doc As DAO.Document
--
Marsh
MVP [MS Access]

thanks for responding, Marsh. i pasted your code into my standard module,
verbatim, and ran it. i'm getting an error message on the line

For Each doc In CurrentDb.Containers!Reports.Documents

which is the same error message i kept getting last night:

3420 - Object invalid or no longer set.

i figured i must be doing something wrong. now i'm wondering if there's a
problem with my copy of Access? i'm testing the code in an A2000 db running
in A2003 on Win2KPro. the code does compile successfully, there are no
MISSING references listed in References, and the Microsoft DAO 3.6 Object
Library is checkmarked. Microsoft ActiveX Data Objects 2.1 Library is also
checkmarked (below the DAO reference in the list order) - might that be a
problem? i'd be grateful for any suggestions you might have for me. tia,
tina

 

[JimS]

Thank you, Marsh. I appreciate your time and effort on my behalf! Looks like
Tina came out on the plus side as well!
--
Jim

Arrggghhhh, I keep forgetting that CurrentDb is only good on
the line that it is used on. A database object needs to be
set:

Dim db As Database
Dim doc As Document
Dim strDescr As String
On Error GoTo ErrHandler
Set db = CurrentDb()
For Each doc In db.Containers!Reports.Documents
. . .

I never run with both DAO and ADO, but your arrangements
sounds right for this. You should think about qualifying
the declarations though:

Dim db As DAO.Database
Dim doc As DAO.Document

 

[tina]

comments inline.

Arrggghhhh, I keep forgetting that CurrentDb is only good on
the line that it is used on.

well, i didn't forget that at all - i just never knew it in the first place!

A database object needs to be
set:

Dim db As Database

Set db = CurrentDb()
For Each doc In db.Containers!Reports.Documents

okay, got it now. i've seen people set a database object many times, but i
never knew there was a specific reason to - beyond a preference for working
with variables instead of direct references in the body of the code. this
bit of knowledge is going to help me a lot, i know!

. . .

I never run with both DAO and ADO, but your arrangements
sounds right for this. You should think about qualifying
the declarations though:

Dim db As DAO.Database
Dim doc As DAO.Document

okay, i'll make a practice of doing that from now on. i always define my
Recordsets as DAO, but didn't realize i should when working with these other
database objects as well. i've done very little with this type of
programming, and your help today has given me a big boost along this road.
thanks so much!

 

[Marshall Barton]

"Marshall Barton" wrote
[snip]

okay, i'll make a practice of doing that from now on. i always define my
Recordsets as DAO, but didn't realize i should when working with these other
database objects as well. i've done very little with this type of
programming, and your help today has given me a big boost along this road.
thanks so much!

You're welcome tina, glad to provide what little help I can.

It's not strictly necessary to qualify the objects that
don't occur in more than one library. However, since it's
difficult to remember where the conflicts might appear or
what libraries may needed in the future while working on a
specific problem, it's probably safest to qualify most
things, except maybe the VBA And Access libraries.

 

[tina]

"Marshall Barton" wrote [snip]

I never run with both DAO and ADO, but your arrangements
sounds right for this. You should think about qualifying
the declarations though:

Dim db As DAO.Database
Dim doc As DAO.Document

okay, i'll make a practice of doing that from now on. i always define my
Recordsets as DAO, but didn't realize i should when working with these other
database objects as well. i've done very little with this type of
programming, and your help today has given me a big boost along this road.
thanks so much!

You're welcome tina, glad to provide what little help I can.

It's not strictly necessary to qualify the objects that
don't occur in more than one library. However, since it's
difficult to remember where the conflicts might appear or
what libraries may needed in the future while working on a
specific problem, it's probably safest to qualify most
things, except maybe the VBA And Access libraries.

okay, sounds sensible to me. i probably walk a lot of iffy lines just from
lack of knowledge to do otherwise, so i like to stick with safe wherever i
can find it! <happily gathers newest pearls of wisdom, thinks that necklace
just might become long enough to wear someday>