Drop-down list to select relevant reports

[BrookieOU]

I have a drop down list on a form that currently uses the MySys code to list
all of my reports. Since the form is specific to projects I would like to
limit the drop down list to show only the project reports. Is there a way to
do this? I tried to set-up a table of reports, but got lost in the process
somewhere and figured out I don't know what I'm doing! Anyone have any ideas?

 

[Roger Carlson]

When I want to limit the type of reports to show in a listbox, I will give
them a unique prefix, which I can then show in the listbox by querying the
MSysObjects table.

For instance, if I start all of a certain kind of report with "rpt", I can
query the MSysObjects table for the listbox with:

SELECT DISTINCTROW Mid([Name],4) AS Expr1, msysobjects.Type, *
FROM msysobjects
WHERE (((msysobjects.Type)=-32764) AND ((Left([name],3))="rpt"));

Then in the button to preview the report, I have to add the prefix back on:

stDocName = "rpt" & Me!lstReports
DoCmd.OpenReport stDocName, acPreview

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ChooseReportFromList.mdb" which illustrates how to do this.
You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=383.

There are two other samples that do the same thing, but are more advanced.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Damon Heron]

Gee, I hadn't heard of using the MySysObjects code for report listing, but
anyway,

set the rowsource property of your combobox to:

SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*Project*") AND
((MSysObjects.Type)= -32764))
ORDER BY MSysObjects.[Name];

This assumes that your project reports all have the name "project" in them -
if not, rename relevant reports with something unique, and substitute the
word 'project'.
To expand this idea, you could do this with all reports that are grouped
together around a common purpose.

Damon