Newbie: How can I get Access97 to query for report names? 2

[edrodgers]

I have a huge number of reports in my database, and I'm trying to make them accessable through a form. I have too many reports, and more are created daily, to program the form with specific report names. How can I generate a list of reports for use in a combo-box, or set of buttons?<br>

Thanks,<br>

Ed

 

[LWoods]

Sure, you can find the report names... <br>
<br>
In the 'CurrentDB' object there is list of the the categories of objects in the DB. This is the Collection object. After selecting the Collection, we have ANOTHER collection: the Documents. To see your list of Reports:<br>
<br>
For i=0 to CurrentDB.Collection("Reports").Documents.Count-1<br>
print CurrentDB.Collection("Reports").Documents(i).Name<br>
Next i<br>
<br>
This will list your report names. Obviously, the above code could be cleaned up with 'With' statement(s), etc.<br>
<br>
Hope this helps!<br>

 

[JimHorton]

An easier way:<br>
In the RowSource of a combobox:<br>
Select Name from MsysObjects Where Type = -32764<br>
<br>
--Jim

 

[edrodgers]

Thanks! Works Great!<br>
<A HREF="mailto:ed@edrodgers.com">ed@edrodgers.com</A>

 

[kmkland]

Is it possible to limit the list of reports offered instead of showing all reports available??

Thanks in advance for any assistance!!

Rgds,
Kmkland

 

[jsteph]

kmkland,
Yes, you can, but what criteria? With MSysObjects, you can use DateCreated, DAteUpdated, etc. for criteria, or parse the Name and use that.

My personal method of naming reports (since Access db window doesn't support folders for categorizing) is to not only prefix objects with, in this case 'rpt', but to have the next 3 letters further categorize, ie. 'rptFIN' for Finance reports, rptPRD for production reports, etc.
--Jim

 

[lameid]

Personally, I use a table with the report object names in it, and a user frienldy name. I also have about a half dozen fields that are enabled and disabled whether or not I have values in other fields populated. In those fields I put the field name to be used as criteria and build a criteria expression conditionally. I.e. I might have a field DateCriteria. If it has the value orderdate, I make the controls for date criteria visible. Next I construct the criteria based on the value in datecriteria and the fields.

It's a completely different way to go. It may give you the control your looking for even if it is just a table.