Combo Box to Return Report Names 2

[TimTDP]

in Access 2000, how can I create a combo box that returns the names of reports in the database?

 

[straybullet]

SELECT DISTINCTROW MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Type)=-32764) AND ((MSysObjects.Flags)<>8)
ORDER BY MSysObjects.Name;

will give you an alphabetical listing of the reports as named.

Let them hate - so long as they fear... Lucius Accius

 

[fneily]

For the rowsource of your combobox copy/paste;

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

 

[rasticle]

How do you open the report using that combo box?

 

[Remou]

With DoCmd.OpenReport

 

[rasticle]

*pounds head on desk* Thank you! It has been a long day!

 

[rasticle]

How would select specific reports to put into the combo box? So that not all reports are pulled?

 

[MajP]

I guess it depends what you want to filter on. Maybe build a table with several fields so you can group and filter the reports as needed.

tblReports
strReportName
strReportCategory
someOtherFields

In a listbox or combo rowsource
select strReportName from tblReports where strReportCategory = 'someCategory'

 

[Remou]

You may wish to read this thread702-1397583
which has some suggestions for comboboxes and reports.

 

[MajP]

Remou seems to suggest what I was saying. However, that brings up a good point. The table should have a field for the report namem, probably an alias, and maybe even a description. So you could then have a combo box that shows something like

Report Description
2007 Accounts | Shows 2007 outstanding accounts

vice something like

rpt2007accounts

 

[missinglinq]

Another approach I've used is to name reports according to their type, i.e. financial, operations, etc. with a three digit prefix (finMonthlySales, oprPersonell, etc) then use something like this as the source for the combobox for financials, for example:

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],3)= "fin") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!