Selecting a report to print from a List Box... 1

[WallT]

Is there a way in Access to have a List Box on a from with report names in it that I can have a command button next to that will print or display the slected Report in the List Box?

 

[JeremyNYC]

Yes.

Make the name of all of the reports you want displayed in the listbox end with "Lbx".

Then use this as the rowsource of your listbox:
SELECT msysobjects.Name, msysobjects.Type, Right([Name],3) AS TestIt
FROM msysobjects
WHERE (((msysobjects.Type)=-32764) AND ((Right([Name],3))="Lbx&quot);

Then make the Click event of your button do this:
Private Sub btnPrintReport_Click()
If IsNull(Me!lbxReport) Then
Call MsgBox("Please choose a report from the listbox.&quot
Else
Call DoCmd.OpenReport(Me!lbxReport, acViewPreview)
End If
End Sub

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[WallT]

Thank you, that works great, now is there a way to display the names of the reports differently so they don't display the "LBX" etc... in the list box?

 

[JeremyNYC]

I can't think of one, aside from using Left to chop off the last three characters of the name. There may be a way to grab the "Description" of the report object, but I've not done much poking about, and I don't know one off the top of my head.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[WallT]

Thanks for all your help.

 

[DanJR]

Thanks Jeremy for spotting the double post. Since I had already looked up the code in one of my db's, i thought i would post the 'accessing the description property' part of the code, if its of any worth.

DDad.....Jeremy has already answered your question nicely, so this is just F.Y.I.

Here's one way to enumerate through the reports (which are documents within a container) and grab the 'description' property. The code checks if a the description property exists, and create then appends it if it doesn't.

[tt]
Private Sub Form_Load()
Dim strRowSource As String
Dim db As Database
Dim doc As Document

On Error GoTo errLoad

'set reference to reports container
Set db = CurrentDb
Set ctr = db.Containers("Reports&quot

strRowSource = vbNullString

'loop through documents (reports) in container
For Each doc In ctr.Documents
If strRowSource = vbNullString Then
strRowSource = doc.Properties("Description&quot.Value
Else
strRowSource = strRowSource & ";" & doc.Properties("Description&quot.Value
End If
Next doc

Me.lstReports.RowSource = strRowSource

CleanUp:
set doc = nothing
set db = nothing
Exit Sub

errLoad:
If Err.Number = 3270 Then 'property not found
Call CreateProperty("Description", doc)
Resume
End If


End Sub

Public Function CreateProperty(strName, doc As Document)
Dim prpNew As DAO.Property

Set prpNew = doc.CreateProperty(strName, dbText)

prpNew.Value = " "
doc.Properties.Append prpNew

MsgBox doc.Properties("Description&quot.Value


End Function

[/tt]