[Last Call] Learn how to a build a cloud-first strategyRegister Now


List of of reports, captions and tags

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-06-27
I would like to display a list of reports in the database and display the report caption and also pick up the report tag (to filter items I don't want on list)

I would like to do this in a compiled ade.
I can loop through the reports collection and display the report name but I think I need to open the report to read the caption and tag.

Is there an easy way to do this - I could not get this to work for collection but even for an individual report opening the report started to trigger the on open events which I would prefer not to do as some require parameters.

Appreciate any ideas

Question by:donhannam
  • 3
  • 3
LVL 12

Expert Comment

ID: 35694255
Check below link out, I guess that would help you to accomplish that;

LVL 26

Expert Comment

ID: 35694935
You could open the reports to design view.
That triggers no code!

Proof of concept works

Public Sub TagsAndCaptions()
'On Error GoTo myerror
Dim rpt As AccessObject
For Each rpt In Application.CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign
    MsgBox Nz(Reports(rpt.Name).Caption, "No Caption") & " " & Nz(Reports(rpt.Name).Tag, "No tag") & " " & rpt.Name
    DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt

End Sub

Adapt it to your needs instead of msgboxing

Author Comment

ID: 35694942
Thanks Nick67:

I have tried this - works fine in an adp database but got an error trying to open a report in design view in an ade database.

danishani: that web site seems to be down at the moment - I'll try later.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 35695361
danishani: I had a look at this code - It does not work in an adp database - errors on all dim statements and I think even if I did get working it is just giving me the form name from the forms collection. i really need to get to the caption.
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 35695422
Ok, two plans of attack.
Since the ade won't change after deployment, can the stuff from my routine go into a table?
Or you have no control over the back-end?
I don't do ADO but

set rs = currentdb.openrecordset("select * from tblReportdetails where 1=2;",dbopendynaset, dbseechanges)
with rs
    !TheName =  rpt.Name
    !TheCaption = Nz(Reports(rpt.Name).Caption, "No Caption")
    !TheTag = Nz(Reports(rpt.Name).Tag, "No tag")
end with

shouldn't be that tough to set up ***If*** you can create a table.

The other attack:
Private Sub Report_Open(Cancel As Integer)
if me.openargs = "to steal details" then
    me.recordsource = "select from a dummy table where 1=2"
end if
'Now, load the name, caption and tag someplace
End Sub

Now, by rights, the reports will have no data and should close.

Catch my drift?


Author Closing Comment

ID: 35724807
Thanks Nick67:

I have had a go at these and whilst they work will require a bit of ongoing admin.

I decided it would be easier to set up a table in the database listing the reports and info I need so have gone with that option.
LVL 26

Expert Comment

ID: 35724884
It should be easy enough to set up a routine that you run just before you finalize that whacks the contents of a table, and then appends using the code above.
On my own stuff I have a button that runs such a routine.
There's a PAQ about the use of returnusername and doing that kind of stuff here


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

831 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