CODEWITHSUNDEEP
ms-accessms-access-2007vba
JeffO
JeffO

Reputation: 8043

List reports along with their description

Variety of ways to get a list of report names: Query

SELECT [Name] FROM MsysObjects
    WHERE ([Type] = -32764)

Or VBA 

Dim rpt As AccessObject
Dim dB As Object

On Error GoTo Error_Handler

Set dB = Application.CurrentProject
For Each rpt In dB.AllReports

   Debug.Print rpt.Name
Next rpt

A report can have a Description under the Properties (Right-Click on report object), but I cannot access with code.

I'd like to have a listbox display a user-friendly report name associated with the actual report name. I'm trying to avoid creating a separate table to manage this at this point.

Upvotes: 3

Views: 5482

Answers (1)

HansUp
HansUp

Reputation: 97101

CurrentProject is an ADO object, and I don't know how to do what you want from ADO. You can use DAO to retrieve the Description property.

? CurrentDb.Containers("Reports").Documents("rptFoo").Properties("Description")
Foo Report

Since Description is a user-defined property, it doesn't exist until you assign a value for it. Therefore the next line triggers error 3270 (Property not found) for rptLinks, since it doesn't have a Description assigned.

? CurrentDb.Containers("Reports").Documents("rptLinks").Properties("Description")

You could trap that error. Or see if you can make use of Allen Browne's HasProperty function

A totally different approach would be to create tblReports with report_name and friendly_name fields. You would have to maintain that table, but the workload should be roughly equivalent to maintaining the Description properties on the report objects. Then you could use a simple SELECT on the table as the RowSource for your list box.

Update: You could also SELECT from MSysObjects with a custom function to return the Description for each report.

Public Function ReportDescription(ByVal pName As String) As String
    Dim strReturn As String
    Dim strMsg As String

On Error GoTo ErrorHandler

    strReturn = _
        CurrentDb.Containers("Reports").Documents(pName).Properties("Description")

ExitHere:
    On Error GoTo 0
    ReportDescription = strReturn
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 3270 'Property not found.'
        'strReturn = "(no Description)"'
        '* no Description -> just echo report name *'
        strReturn = pName
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.description _
            & ") in procedure ReportDescription"
        MsgBox strMsg
        strReturn = vbNullString
    End Select
    GoTo ExitHere
End Function

Revise your original query to use the function.

SELECT
    [Name] AS report_name,
    ReportDescription([Name]) AS friendly_name
FROM MsysObjects
WHERE ([Type] = -32764);

Upvotes: 1

Related Questions