View the properties of all reports within a database without loading them

[George145]

Hello all,

I am trying to create a listbox which will show the captions of all reports in the database.

In order to do this, without having to load and unload each report by using the Reports collection and taking up time and resources, I think that I have to use the "Report_<Name of Report>" objects that appear under "Microsoft Office Access Class Objects" in the VBA Project Window (and then use "Report_<Name of Report>.Caption" to read the report's caption). I understand that these are objects belonging to a collection, but which collection is it?

It would be very helpful if somebody could share the answer to this question, or indicate another way to access a report's properties.

Thank you very much for your time and help,

George

 

[HiTechCoach]

An object's (like reports) properties as stored within the object. AFAIK, the only way to read the properties of an object is to first open the object.

 

[George145]

Thank you very much HiTechCoach for your fast response. This is also what I thought, until I tried the following, which worked:

Let's assume we have a report named rptReceipt.

In case the report doesn't have any code associated with it in VBA, just right click in any point within the report when in "Design View" --> "Buld Event" --> "Code Builder". The point is to show the report under "Microsoft Office Access Class Objects" in the VBA Project Window. Its name there will be Report_rptReceipt. This way, all the report's properties can be accessed by any module in the database. I.e. the statement

Report_rptReceipt.Caption

put in the code of a form's object, will return the report's caption, without actually having to load the report first. You can try this; it is fast and easy. This way you can access the properties of forms as well.

The problem is that I cannot get my job done if I do not know a priori the exact name of every report in the database. Since a user may add or delete reports or alter the names of the existing ones, such code is of no use.

I believe that there must be a way to make this work parametrically. I.e. write code that looks like this:

Sub FillReportCaptions()

Dim ReportCaptions(), strName as string
Dim i as integer

ReDim ReportCaptions(CurrentProject.AllReports.Count)

For i=0 to CurrentProject.AllReports.Count - 1
strName = "Report_" & CurrentProject.AllReports.Item(i).Name
ReportCaptions(i) = CLASS_OR_COLLECTION_NAME.Item(strName).Caption
Next i

End Sub

I am obviously missing CLASS_OR_COLLECTION_NAME.

Any ideas?

 

[LPurvis]

Hi.
There are a few problems with your position at present.

Firstly, the syntax you're using to refer to the Report without opening it e.g.
Report_rptReceipt.Caption
This is the class object of the report. It will only exist if the report in question has a class module. If it's HasModule property is False then such a reference to the Report class will fail.

The next problem is where you say
>> "will return the report's caption, without actually having to load the report first. ... it is fast and easy. This way you can access the properties of forms as well."

The syntax to which you refer (the class object) does actually open the report.
It creates an implicit instance of the report by creating an instance of the class (they're inextricably linked in Access, object and its class).
It will drop out of scope when Access sees fit (not necessarily when your procedure ends).
This invisible instance may not display in the UI, but it does load the same data and raise the same events and code as launching your report "normally". You are still opening it.
If you would like some proof - you can't make a visible an object which isn't open.
But if you execute

With Report_rptReceipt
    Debug.Print .Caption
    .Visible = True
End With

you'll get the caption printed as you did before - but then see the report displayed.
The impressive speed you're seeing is either because the report isn't very heavy anyway, has already been loaded and some disk caching has occured or just because the UI hasn't had to render it.

The final problem with the class object is the attempt to build it's instance by name.
You can't.
The class is an object in the VBA project - just like, say, DoCmd or CurrentProject.
(OK, not built in - but equally not part of an expression).

Just as we can't use code such as
Dim objCls As New clsMy & "Name Here" & Object

But as mentioned - such runtime code wouldn't get you very far in avoiding opening the reports.

If you want closed report properties you'll either have to settle for the very limited information exposed through the AllReports object properties collection.
However - there's nothing stopping you from adding to that minimal collection with a series of your own properties. So when your report is actually used - write the runtime properties of the report into the AccessObject report properties. Since they're available when the report is closed, you'll be able to access them any time without launching the report.

It's not an entirely built in solution - but it persists.
(You could of course, store those properties in any of a multitude of locations - the AccessObject just makes it particularly easy and compartmentalises the objects logically).

Cheers.

 

[George145]

Impressive. Thank you very much Leigh!

I will try the solution you propose, but I have to read first to get more into collections and objects in VBA.

But let me ask one more question.

What I would like the user to do when creating a new report is only entering the report's description as caption and not interfere with the code or anything else within the database. Will this be possible with the solution described? I mean, these "custom" properties may be filled in
a) automatically once, when a new report is created,
b) automatically each time the database is opened or
c) some other way?

Thank you very much again for your valuable help.

 

[LPurvis]

Hi again.

Sorry, you lost me when you said
>> "What I would like the user to do when creating a new report "
I passed out due to a loss of blood to my head and complete nervous system shut down.

Users creating reports?
In a developed, released application? :-O
If that is what you're doing then you sacrifice so much control of the application that can you actually be sure of anything?

In summary no - you'll be knackered. You'd need code to run when the report is opened which populates your "other" repository of properties. A user creating their own report won't include a call to that code in their own development. (Why would they?)
If you create your own then you're fine. You could include the code.
If you allow users to create their own reports, but maintain a central application point from which you offer the list of reports available to launch, then you'll be able to use that launching code to acquire the list of properties instead of the user having to do so.
But if they make their own and launch their own, no - you have nothing.

FWIW an application would normally be completely locked down upon release.
If users want to "play" with queries and reports then you could give them a separate blank MDB in which to do so which is liked to the same data as the live application (however there's plenty that could go wrong there - if they have access to the tables, you'd likely want to try to secure it somehow).


Incase you're battling with the property filling itself - it could be as simple as

Sub FillAccessReportProperties()
On Error Resume Next 'Because of non-compatible properties
 
    Dim prp As DAO.Property
    
    For Each prp In Me.Properties
        CurrentProject.AllReports(Me.Name).Properties.Add prp.Name, prp.Value
    Next
    
End Sub

You can then refer to even the closed report's property such as
CurrentProject.AllReports("Report Name").Properties("RecordSource")

Cheers.

 

[George145]

Hi again,

I am really sorry for almost giving you a heart attack

Basically this is a small part of an application that is intended for internal use. So, in short, the user groups will be the following:

1. Admin
2. One or two users with full permits - high ranked employees - who will be responsible for adding reports to the database, no more than once in a month. They have limited knowledge of Access and no programming skills at all.
3. 5-10 users who will just run the application.

So the users I was reffering to belong to group 2. They should be allowed to add reports, but they should also not need to mess with the code. According to your replies, I can see two solutions to my problem:

1. Once the application or the relevant form is loaded, all reports are loaded and then unloaded in order to get the information needed. This will take some time but works ok.

2. I add a table containing 2 fields, apart from the "ID", which would be "Report Name" and "Report Description", filled by the user each time a new report is created, and define this table as source of the listbox.

If you think I misunderstood what you said and want to correct me, or have anything else to suggest, please do.

Thank you very much and sorry if my English is not good enough (not being a native speaker)

George