Solved

Preview/Print List Box by Report Caption

Posted on 2000-02-20
11
578 Views
Last Modified: 2012-05-04
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?
0
Comment
Question by:sluggo050499
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 1

Expert Comment

by:spruce22
ID: 2540239

Sluggo,

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
Next

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.
0
 
LVL 1

Expert Comment

by:spruce22
ID: 2540280
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.
0
 

Author Comment

by:sluggo050499
ID: 2540296
Edited text of question.
0
 
LVL 1

Expert Comment

by:spruce22
ID: 2540330
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).




0
 

Author Comment

by:sluggo050499
ID: 2540356
Edited text of question.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:spruce22
ID: 2540387
sluggo:

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.

 
0
 
LVL 1

Expert Comment

by:lina10
ID: 2540392
Hello!
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.
lina
0
 
LVL 1

Expert Comment

by:spruce22
ID: 2540404
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.  
0
 
LVL 2

Accepted Solution

by:
repstein earned 200 total points
ID: 2540947
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
    .AddNew
       DoCmd.OpenReport doc.name, acViewPreview
       !ReportName = sReportName
       !ReportDescription = Reports(sReportName).caption
    .Update
    DoCmd.Close acReport, doc.name
  End If
Next
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.

Richard



0
 

Author Comment

by:sluggo050499
ID: 2540960
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! :)
0
 
LVL 2

Expert Comment

by:repstein
ID: 2541018
sluggo:

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 :)

Richard


0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now