list box showing reports ?

[JonEx]

Hey,
i was wondering is it possible to have a list box that shows reports ? without the reports having the rpt or rpf infront of it? and if it is possible how would i go about that ?
-----------------------------------------------------------
list box name : 1stRep
command button to preveiw report : cmdOpen1

 

[MakeItSo]

Hi JonEx,
if all your reports start with rpt_ or rpf_, use this as data source for your listbox:

SELECT Mid(MSysObjects.Name, 4) FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

Credits:

http://www.mvps.org/access/queries/qry0002.htm

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[JonEx]

Hey,
thanks but i was hoping that i dont have to change the name of my reports... i dont want to put the rpt or rpf...is it possible to still get it work using Sysobjects?

 

[MakeItSo]

Hi Jon - no need to rename. )

You can adapt the code above to your needs:
".Type=5" instead of -32764 will give you a list of your queries and using something like e.g.

... And Name LIKE "Statistics*"

will result in a list of all your reports starting with "Statistics", and so on.

Above code without change will yield all reports, no matter what their name...

Good luck,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[JonEx]

Hey thanks again Andy but unfortunately its not working right for me .. ok orginally my reports started with qrpt but i renamed all of them to rpt after making them..
so when i click on a report .. say rptLongChap
when im actually in the report the title bar says qrptLongChap as i made them from queries...
now when i tried the code ..most reports show up but not the ones i renamed .. i renamed one called
"rptUpper" to "Upper Mid West Chapter".
and it doesnt show in the list box, i've tried the AND LIKE with "Mid*" "Chapter*" but still nothing..
this is my code for the form and the command button to open the form

-----------------------------------------------------------
Private Sub cmdOpen1_Click()
On Error GoTo Err_cmdOpen1_Click

Dim stDocName As String
Dim stLinkCriteria As String
If IsNull(Me!lstReports) Then
MsgBox "You must choose a report", vbExclamation
Else

stDocName = Me!lstReports.Column(0)
If Me!lstReports.Column(1) = "rpt" Then
DoCmd.OpenReport "rpt" & stDocName, acPreview
ElseIf Me!lstReports.Column(1) = "rpf" Then
DoCmd.OpenForm "frm" & stDocName, acNormal
End If

' Me.Visible = False
End If
Exit_cmdOpen1_Click:
Exit Sub

Err_cmdOpen1_Click:
MsgBox Err.Description
Resume Exit_cmdOpen1_Click

End Sub
-----------------------------------------------------------
and this is the orginal code with the list box

SELECT DISTINCTROW Mid([Name],4) AS Expr1, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Type)=-32764) AND ((MSysObjects.Name) Like "rpt*")) OR ((MSysObjects.Type)=-32764) AND ((MSysObjects.Name)Like "rpf*")) ORDER BY MSysObjects.Name;

now this worked fine but until i changed the name of the reports ... nothing shows up now...

-----------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String
Set objCP = Application.CurrentProject
For Each objAO In objCP.AllReports
If Left(objAO.Name, 3) = "rpt" Or Left(objAO.Name, 3) = "rpf" Then
strValues = strValues & Mid(objAO.Name, 4) & ";" & Left(objAO.Name, 3) & ";"
End If
Next objAO
lstReports.RowSourceType = "Value List"
lstReports.RowSource = strValues

End Sub
***********************************************************

 

[MakeItSo]

most reports show up but not the ones i renamed .. i renamed one called
"rptUpper" to "Upper Mid West Chapter".

That's the point.
1.) In your listbox's data source, you'll find

...AND [b]((MSysObjects.Name) Like "rpt*"))[/b] OR ((MSysObjects.Type)=-32764) AND [b]((MSysObjects.Name)Like "rpf*"))[/b]

This excludes the reports you renamed. If you leave out these criteria, you will receive all reports.

 

[joebloeonthego]

I doubt this is the best way, but the way I naturally did it was I made a table with two columns reportname reportdesc and called it reports.
ie:
reports
--------
report1 standard report
report2 columnar report
report3 verbose report

and made a listbox that showed the second column, and when you click the print button it calls the report that's selected (column 1).