Picking up a report’s description in VBA code (2K)

Topic

New Reply

Does anyone know of a way I can get VBA to pick up the descriptions I have entered manually in my report properties through the database window?

I wish to create a combo field in a dialog box, which will enable the user to view a sorted list of reports and select one to open and/or print. I have a couple of hundred reports whose descriptions have been carefully created for sorting and clarification purposes. I need to loop through the list of reports and store in an array the names and descriptions of any that are not hidden. This array will then be sorted for use in the combo list.

If I use the AllReports collection, I can access very little information without opening the reports; for example, I can get the name, but cannot check the Visible or Tag properties.

If I loop through AllReports and open each report in turn, I can access the Visible property and other properties such as Tag, but do not seem to be able to access the report description in any way. I also find that, even with Echo off, there is a lot of flicker as it opens, reads, then closes each report to build the list.

So far, my best solutions are to:

(a) Create a table with fields for the report name and description (this would allow me to create additional fields to assist selection, but would be very time-consuming, and require careful maintenance every time a report is added, modified or deleted); or

( Manually copy the Descriptions to the Tag fields, then loop through AllReports to open each report and put the name and Tag property into the array (which would also be time-consuming and require maintenance as for (a), as well as having the flicker problem).

Perhaps I am missing something – I have looked through the enormous list that is displayed when I click the More Controls button on the Toolbox, but nothing stands out as an obvious way of enabling the user to browse a list of reports.

I have searched back over a year of posts and don’t believe this question has already been raised, but apologise if it has been and I have just missed it. I’m sure there are brighter minds out there than mine, so hope someone can make sense of my question and point the way to a solution!

Thanks and regards

Reply | Quote

Replies

Here’s what you need (note: you don’t need to open the reports):

Public Sub GetReportDescriptions()

Dim objCurrentProject As Object
Dim objAllReports As AllReports
Dim objReport As AccessObject
Dim strReportDescription As String

Set objCurrentProject = Application.CurrentProject
Set objAllReports = objCurrentProject.AllReports

For Each objReport In objAllReports
strReportDescription = CurrentDb.Containers(“Reports”).Documents(objReport.Name).Properties(“Description”)
Debug.Print strReportDescription
Next objReport

End Sub

You may be able to simplify this further by simply stepping through the Containers(‘Reports”).Documents collection.

For those situations where you do need to open the report to look at other properties, you can avoid the “flickering” by specifying acHidden in the DoCmd.OpenReport statement.

Reply | Quote

Thank you… thank you… thank you!

I don’t know which impresses me more – the simplicity of the answer or the speed of your response! Either way, many thanks – the code you supplied is just what I need, and the extra hint about acHidden is something I will store away for next time I need it!

Cheers

Reply | Quote

Since you’re using the DAO Containers object anyway, you might as well use DAO throughout:

Public Sub GetReportDescriptions()
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim strReportDescription As String

Set dbs = CurrentDb
For Each doc In dbs.Containers(“Reports”).Documents
strReportDescription = doc.Properties(“Description”)
Debug.Print strReportDescription
Next doc
End Sub

Reply | Quote

Thanks, Hans, for your suggestion. It seems a succinct and logical extension of Tom’s code but.. when I try to run it, it stops on the line “strReportDescription = doc.Properties(“Description”)” and I get “Run-time Error ‘3270’: Property Not Found.”.

This makes absolutely no sense to me, as Tom’s code still runs fine, and a line-by-line comparison seems to indicate that both sets of code do exactly the same thing!

I have a working solution, so am not asking anyone to spend any more time on this (how do you find the time to make 50,000 posts in four and a half years?!?!), but thought I should let you know what happened, and thank you for your response.

Cheers

Reply | Quote

Can’t explain that – on my PC, both Tom’s code and mine bomb if a report doesn’t have a description. Here is a version that handles reports without a description:

Public Sub GetReportDescriptions()
Dim dbs As DAO.Database
Dim doc As DAO.Document
Dim strReportDescription As String

On Error GoTo ErrHandler

Set dbs = CurrentDb
‘ Loop through all reports
For Each doc In dbs.Containers(“Reports”).Documents
‘ Get description
strReportDescription = doc.Properties(“Description”)
‘ Do something with it
Debug.Print doc.Name & “: ” & strReportDescription
Next doc

Exit Sub

ErrHandler:
If Err = 3270 Then
‘ No Description property – show custom text (modify as desired)
strReportDescription = “(None)”
Resume Next
Else
‘ Display error message and get out
MsgBox Err.Description, vbExclamation
End If
End Sub

Reply | Quote

Aha – I hadn’t realised that Tom’s procedure was picking up the reports in a different sequence from yours – the first report in Tom’s procedure had a description, whereas yours picked up a hidden report with no description first.

I can actually use this to eliminate the hidden reports, by making sure all the visible reports have a description, but none of the hidden reports (I still have been unable to find a way of getting the visible property without opening the report, so this will serve as a workaround).

Thanks again for persevering – I was really bugged by what appeared to be an anomaly, but clearly hadn’t looked carefully enough for the answer!

Cheers

Reply | Quote