Filll listbox with the Caption of all the reports in a database

[Dale Fye]

I have a form with a listbox, which I want to populate with the Name of the
report(this will be hidden), and with the caption that is assigned to the
report.

The user will be able to select one or more reports from the listbox, and
run the report. I have figured out how to identify the reports that are
stored in the database:

SELECT [Name] as RptName
FROM mSysObjects
WHERE [TYPE] = -32764

but I have not been able to figure out get the Caption property assigned to
the report. I assume I will have to access the reports collection, but
everything I have tried has failed.

Thanks in advance.

 

[Graham Mandeno]

Hi Dale

To get to the report's Caption property, you need to open the report. This
could be a lengthy process - enumerate the reports, open each one in design
view, retrieve the Caption, and close it.

You might consider instead using the Description property of the report
document. This is what you see in the "Description" column of the database
window and you set it from right-click>Properties.

The following function will return a semicolon-delimited list of the names
and descriptions for all those reports that have a description.

Function ListReportsWithDescriptions() As String
Dim db As Database, doc As Document
Dim sDescr As String, sList As String
On Error GoTo ProcErr
Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
sDescr = doc.Properties("Description")
sList = sList & doc.NAME & ";" & sDescr & ";"
NextDoc:
Next doc
If Len(sList) Then
ListReportsWithDescriptions = Left(sList, Len(sList) - 1)
End If
ProcEnd:
Set db = Nothing
Exit Function
ProcErr:
With Err
If .Number = 3270 Then Resume NextDoc
MsgBox .Description, vbExclamation, "Error reading report descriptions"
Resume ProcEnd
End With
End Function

You can set your listbox's RowSourceType to Value List and in your Form_Load
procedure, add this line:
MyListBox.RowSource = ListReportsWithDescriptions

 

[fredg]

I have a form with a listbox, which I want to populate with the Name of the
report(this will be hidden), and with the caption that is assigned to the
report.

The user will be able to select one or more reports from the listbox, and
run the report. I have figured out how to identify the reports that are
stored in the database:

SELECT [Name] as RptName
FROM mSysObjects
WHERE [TYPE] = -32764

but I have not been able to figure out get the Caption property assigned to
the report. I assume I will have to access the reports collection, but
everything I have tried has failed.

Thanks in advance.

You can't using a query, but you can using VBA.

1) Set the ListBox RowSourceType property to Value List.
Set the BoundColumn to 1
Set the Column Count to 2
Set Column Widths to 0";1"
Set the MultiSelect to 'none'
Set Column Heads to No.

Code the List Box AfterUpdate event:
DoCmd.OpenReport Me!ListBoxName, acViewPreview

2) Code the Form's open Event:

GetReportCaption

3) Copy and paste the below into a Module:

Public Sub GetReportCaption()
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim strList As String
For Each doc In db.Containers("reports").Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
If IsNull(Reports(doc.Name).Caption) Or Reports(doc.Name).Caption
= "" Then
strList = strList & doc.Name & "," & doc.Name & ","
Else
strList = strList & doc.Name & "," & Reports(doc.Name).Caption
& ","
End If

DoCmd.Close acReport, doc.Name, acSaveNo
Next doc
strList = Left(strList, Len(strList) - 1)
Forms!FormName.ListBoxName.RowSource = strList

End Sub

Add whatever error handling you think you need.

As written above, if there is no report caption it will simply use the
report name as the caption. Making a selection in the list box will
open the report in preview.

 

[Dale Fye]

Thanks, guys.

I knew there had to be a way to get at this, just had the wrong object, and
never would have thought of the documents.

Dale

I have a form with a listbox, which I want to populate with the Name of
the
report(this will be hidden), and with the caption that is assigned to the
report.

The user will be able to select one or more reports from the listbox, and
run the report. I have figured out how to identify the reports that are
stored in the database:

SELECT [Name] as RptName
FROM mSysObjects
WHERE [TYPE] = -32764

but I have not been able to figure out get the Caption property assigned
to
the report. I assume I will have to access the reports collection, but
everything I have tried has failed.

Thanks in advance.

You can't using a query, but you can using VBA.

1) Set the ListBox RowSourceType property to Value List.
Set the BoundColumn to 1
Set the Column Count to 2
Set Column Widths to 0";1"
Set the MultiSelect to 'none'
Set Column Heads to No.

Code the List Box AfterUpdate event:
DoCmd.OpenReport Me!ListBoxName, acViewPreview

2) Code the Form's open Event:

GetReportCaption

3) Copy and paste the below into a Module:

Public Sub GetReportCaption()
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim strList As String
For Each doc In db.Containers("reports").Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
If IsNull(Reports(doc.Name).Caption) Or Reports(doc.Name).Caption
= "" Then
strList = strList & doc.Name & "," & doc.Name & ","
Else
strList = strList & doc.Name & "," & Reports(doc.Name).Caption
& ","
End If

DoCmd.Close acReport, doc.Name, acSaveNo
Next doc
strList = Left(strList, Len(strList) - 1)
Forms!FormName.ListBoxName.RowSource = strList

End Sub

Add whatever error handling you think you need.

As written above, if there is no report caption it will simply use the
report name as the caption. Making a selection in the list box will
open the report in preview.

 

[Marshall Barton]

I have a form with a listbox, which I want to populate with the Name of the
report(this will be hidden), and with the caption that is assigned to the
report.

The user will be able to select one or more reports from the listbox, and
run the report. I have figured out how to identify the reports that are
stored in the database:

SELECT [Name] as RptName
FROM mSysObjects
WHERE [TYPE] = -32764

but I have not been able to figure out get the Caption property assigned to
the report. I assume I will have to access the reports collection, but
everything I have tried has failed.

The Description property is in the report Document object so
you need to create a public function in a standard module to
retrieve it for you.

Public Function GetDescr(strReport As String) As String
On Error Resume Next
GetDescr = CurrentDb.Containers("Reports") _
.Documents(strReport).Properties!Description
End Function

Then your query can have a calculated field like:

SELECT [Name] as RptName, GetDescr([Name]) As Descr
FROM mSysObjects
WHERE [TYPE] = -32764