Reports Listbox

[D Willett]

Hi

I use a list box to show reports for selection. The rowsource is:

SELECT msysobjects.Name FROM msysobjects WHERE (((msysobjects.Type)=-32764)) ORDER BY msysobjects.Name;

Which shows all reports, this is great other than the list also contains subreports which to the user is confusing.
Is there another way to show the reports which only I ( as an administrator) want the user to see for selection.?

[HansV]

Hi Dave,

There are several options.

The first that comes to mind is to use a strict naming convention. You could have all "main" reports start with "rpt", and subreports with "srpt" or similar. The row source for your list box would then become

SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Type=-32764 AND MSysObjects.Name Like "rpt*" ORDER BY MSysObjects.Name;

Another one would be to create a table tblReports with a ReportName field (Text). Add a record for each report that you want to appear in the list box, and change the row source to

SELECT ReportName FROM tblReports ORDER BY ReportName

A disadvantage is that you have to maintain the table - new reports will not automatically be added to the table, and neither will deleted reports be removed from the table automatically. But on the other hand, you get to control exactly which reports will be listed.

[D Willett]

Cheers Hans

I opted for the first option which I can decide at design stage whether it's shown or not.

[JohnH]

I find that I want to decided on a case by case basis which reports to list, and how they should appear, so I just have another table with two fields: Display Name , and Report Name. The listbox uses this table, and displays the Display Name, but uses the ReportName column.
I manually maintain the table.

[D Willett]

Thanks for your views John.
I decided on the first option because the lack of maintenance, but it just goes to show there are many options.

Thanks again.