Preview/Print List Box by Report Caption

I want to put a list box (on a Print Form) of all of the reports with a Preview and Print Command Button.  Rather than have the report list show the actual report name, I want the list to show the report caption.  The user can then select a report and click Preview or Print and it will do it.

I'd like to put this code in multiple databases that I've designed.

SPRUCE22: For some reason I am unable to add comments.  I had to go back and edit the original question.  Parcing the Report Names is an option I'm looking at but I'd like to get some more ideas.

Just a note:
I have have succeeded in doing this through using a reports table where I have the name and caption of each report in a table (t_report).  I created a list box which is bound to the report name but displays the caption.  The code on the preview:

Private Sub cmdPreview_Click()
    Dim strReportName As String
    strReportName = lstReports.Value
    DoCmd.OpenReport strReportName, acViewPreview
End Sub

The problem with doing it this way is I have to add each report and caption to the table as I build them.  There has to be a way to capture each report and its caption in a record using code.

I also know how to do a query of report names:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764));

But - The names are carefully named with prefixes - I would rather display the caption of each report.

SPRUCE22: Thanks for the help - I agree that opening the report to get data would be innefficient.  Is there anyway to store a comment in the msysobjects table?  I noticed that if I added a comment to the report properties, a "Long..." showed up in one of the table's records for that report.  Any ideas?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

repsteinConnect With a Mentor Commented:
To sluggo:

You have posed an interesting problem.

  I do not believe there is any way to access the caption property of the report without opening the report.
  Given this, you have some decisions to make about how you want to populate your list box. I think that the best thing to do is to create a table with two fields such as ReportName and ReportDescription. This table will then be used to populate your listbox. There are several options on how to maintain this table:

1. If the table is on a server, you could update the table when you add a new report to the application (work for you, transparent to the users)

2. You could have the application automatically update the table as the form's On Open event  (no work for you, modest performance hit on the users since most of the time no update will be needed)

3. You could provide the application with a button to do the update on a manual basis (no work for you, minimal perormance hit on the users, but risk of their not having access to a new report)

Since you understand collections, I am going to assume that you know how to create the listbox and have a report run when the user clicks on a row in the list, and that you know how to setup a radiobutton group to allow the user to print to the screen or to paper.

Here is a short subroutine that you can run on the server, as the On Open event for the form, or as a button event. The procedure will only open new forms and add their information to the table; existing table entries will not be modified. Assuming that you don't want to change the existing description of the report, this should work fine.

The assumtpions are:
1. A table named Reports containing the fields ReportName and ReportDescription

2. A button named CommandListReports on the form (this can obviously be changed)

3. The list box is named ListReports and it RecordSource is set to the table Reports with 2 columns, no header, bound to column 1 (the report name), and column widths set to only display the 2nd column (report description)
For those reading this answer who are not at your level of sophistication, the name of the report in response to a click on a row in ListReports will be:

sReportName = ListReports.ItemData(ListReports.ListIndex)

The code example follows:

Private Sub CommandListReports_Click()
Dim dbs as Database
Dim rstReports as Recordset
Dim doc as Document
Dim ctr as Container
Dim sReportName as string
Dim sReportDescription as string

Set dbs = CurrentDB
Set ctr = dbs.Containers("Reports")
set rstReports = dbs.OpenRecordset("Reports",dbOpenDynaset)

with rstReports
For Each doc in ctr.Documents
  sReportName = doc.Name
  .FindFirst ("ReportName = '" & sReportName & "'")
  if .NoMatch then
       DoCmd.OpenReport, acViewPreview
       !ReportName = sReportName
       !ReportDescription = Reports(sReportName).caption
    DoCmd.Close acReport,
  End If
End With
end sub

Note that reports will only be opened if they don't already exist in the Reports table. If a report is open when the subroutine is run, it will be closed.

Hope this helps.




You probably don't want the listbox to display the actual caption of the report.  In order to get the actual caption you'd have to have the report open.

One way to accomplish the task you want is to have a table, tblReports with two fields, RptName and RptCaption.  You'd store the actual report name in RptName and the caption as you'd want it dispayed on the listbox.  You could then make the listbox show the caption field, but when the button is pressed you'd open the associated RptName.  This method isn't very dynamic, as you'd have to change the values in the table if there are any report name changes.

The most dynamic way to accomplish this is to have a function as the recordsource of your listbox.  The format of this function is defined by access, but you populate the listbox with whatever you want.  You could then write some code to iterate throught all reports in your database.  If you named your reports in a specific format, for example rptSalesTotal then you could parse out the characters you don't want, ("rpt") and if you wanted to get really fancy write a function that put a space in the reportname whenever an uppercase letter is found.

This shows you how to list all the reports in the current database

Sub ListReports()

Dim db as Database
Dim doc as Document
Dim cntr as Container

Set db = currentdb
set cntr = db.Containers("Reports")
For each doc in cntr.Documents
  debug.print doc.Name

End Sub

These are some general ideas, I didn't go to far into the specifics because I didn't know if you'd want to follow either approach.
Wow, I completely missed the second portion of your question.  That's really weird.  When I read the last line displayed was:

"I'd like to put this code in multiple databases that I've designed."

You've obviously gotten farther with your endeavor than my previous answer goes into.

But you could change you query to include a call to the function that modified report name and bind the listbox to this column.  For example:

SELECT MSysObjects.Name, MSysObjects.Type, ChangeReportName([MSysObjects].[Name]) as NewReportName
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764));

Then you could write a function to modify the report names.

Public Function ChangeReportName(sRptNm as string) as String

const strPrefix = "rpt"

ChangeReportName = Right(sRptNm, Len(sRptNm) - Len(strPrefix))

End Function

Sorry if the code isn't 100%, I don't have Access on my machine in order to verify syntax.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

sluggo050499Author Commented:
Edited text of question.
Other than parsing the report name the query or in the listbox function I don't think there's going to be any way to make it dynamic:

"There has to be a way to capture each report and its caption in a record using code."

You could definitly do this. But the reports have to be opened to get to the caption.  You'd have to write a process that opened every report in design view, capture it's caption, and then add your record.  This doesn't seem like a good solution to me.  It'd be much easier to maintain the information in the table.

Sluggo:  Thanks for noting that you had to edit the text of the question, I thought I was quite the fool for a moment(not that that proves I'm not quite the fool).

sluggo050499Author Commented:
Edited text of question.

I'm not sure on this one, but you could find out.  I don't think you have any design or update permissions on MSysObjects, in which case you couldn't add any comments.  One way to test this would be to open MSysObjects in design view and try to add a column.  You'll probably get an error saying you don't have design permissions, but maybe you'll get lucky!  Just in case you don't know how to get the MSys tables to show in your database click on Tools, Options, and make sure the Hidden and System Objects check boxes are checked.

Also, I couldn't add comments to EE until I registered as an expert - if you haven't already you could probably do so quickly.  I think there was a little hyperlink to register as an expert at the bottom of the questions I was reading before becoming an Expert.

each report object in the reports collection has both the name and the caption properties. looping through the collection and filling a table would be an independent way.
Hello lina10:

Do you mean the actual Reports collection?  Such as Reports("TestReport").Name and Reports("TestReport").Caption.  If this is the collection you're referring to these  properties are only available when the report is open.  Since sluggo is trying to open the reports from his form, this wouldn't quite work out.  
sluggo050499Author Commented:
That will work.  Thanks.  It sounds like a recommendation for Microsoft should be in order.  I'm surprised it is not easier to do this sort of thing.  In the msysobject table, it would be nice if there was a "caption" property that was accessible external to the report.

But this will work.  I believe I will just make it a command that is only accessible to the administrator.  A nice easy way to update the List Box.

Thank-you to everyone for the help.  Curious question:  Why use the containers property when the msysobject table is accessible?  Also,  I am a junior programmer - could you please remark on the major portions of this code to explain to me what is occurring?  I like to learn as I go - not just cut and paste! :)

Glad my answer was of help.

As to why use containers rather than  msysobject tables, there is no intrinsic reason, as both give the same information.

The msysobjects table is a bit more obscure, in that there is no Online documentation within Access, and one has to go out to the MSDN library disk 3 to find it. In my mind it is a big problem that there is no help within Access to provide guidance with the syntax.

In your original example, you find reports by examining msysobjects.type = -32764

Personally I find the container syntax (e.g., Set ctr = dbs.Containers("Reports") much easier to remember.

If you use the msysobjects table in your work, just be sure that you document the hell out of the applications :)


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.