UtterAccess Discussion Forums > Get list of reports and show in listbox

Full Version: Get list of reports and show in listbox

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

adaytay

Aug 13 2008, 07:48 AM

Hi all,

Following on from this thead (thanks again Alan it worked perfectly

), I've had an idea!

Is it possible to populate a listbox with the list of all reports in the current database, that start with a certain prefix? (eg rptATT_<reportname>)?

Reason being, is I have a "reports control panel" which I have some report names listed down the left-hand side of the screen, these are currently check boxes with a description, then in the tag I have the name of the report to open. The control panel also has a date range and output method, where the user can select either "print", "preview", can export to excel, or can export to PDF. This also works brilliantly for multiple reports, as the system just combines the reports (if excel or pdf is selected) into a single file before doing something with it. (will add this into the code archive if there's enough of a demand for it)

However, the biggest current flaw in the design is that if I add any new reports in I will need to change the form design, adding in a new checkbox for the report - so I thought, why not just pull the details through automatically from the system?

Any ideas? Has anyone done anything like this?

Cheers,

Ad

adaytay

Aug 13 2008, 08:01 AM

Ok - first bit is done:

Set this as the rowsource for the listbox:

CODE

SELECT msysobjects.Name FROM msysobjects WHERE (((msysobjects.Name) Like "rptATT*") AND ((msysobjects.Type)=-32764));

Now then, this is where it gets tricky. I want to display the report "description" (ie right-click on the report at the DB window, and get the description from there)..... any ideas?

Cheers,

Ad

RalphS

Aug 13 2008, 08:39 AM

You could do this in code quite easily from what i remember, you can easily list all Reports and their descriptions.

Would you be able to implement that if i knocked that up?

adaytay

Aug 13 2008, 08:47 AM

Hi Ralph,

As per my second post I've got the list, the difficulty was in getting the description out.

However, managed to sort it, as follows...

RowSource is now:

CODE

SELECT msysobjects.Name, GetDescr([Name]) AS Description FROM msysobjects WHERE (((msysobjects.Name) Like "rptATT*") AND ((msysobjects.Type)=-32764));

And a new function, called GetDescr, as follows:

CODE

Public Function GetDescr(ReportName) As Variant

GetDescr = CurrentDb.Containers!Reports.Documents(CStr(ReportName)).Properties!Description

End Function

I have a habit of answering my own questions - thanks for the look-in though!

Cheers,

Ad

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.