populating listbox with all available reports

[boblarson]

I am at a bit of a standstill -

I am trying to figure out how to populate a listbox with the names of all of the available reports to run so that the user can select one and then run it.

I have come up with this code to find the names of all of the reports and the msgbox output looks fine. I am just at a loss to figure out how to plug that into the listbox.

Maybe there's an easier way also. Let me know if you have something that will help.

The code I have currently:
Dim obj As AccessObject, dbs As Object, strRpts As String
Set dbs = Application.CurrentProject
' Search for AccessObject objects in AllReports collection.
For Each obj In dbs.AllReports
strRpts = strRpts & vbCrLf & obj.Name
Next obj
MsgBox strRpts



Bob Larson

 

[Alexandre]

Populating a listbox through code involves a creating a particular kind of function that is not at all intuitive. I would either try to create a value list instead (make a string of values from the obj.Names you gather and assign it as the source of the combo) or preferably, I would go for a basing the combo on a query using on Access system tables:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=-32764));

 

[donbettis]

Here is a previous post dealing with this...

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=19577

Thanks to PeterD and raindrop3 I was able to get it to work... This post also includes info on preventing a particular report or reports from being in the list box.

***Attached is an example... I pulled it from one of my databases; it will open and list the reports; however I did not include some of the forms that it will look for... so it will give you an error if you try and print the report... You can edit the code to fix the errors...


Hope this helps...

Don

 

[boblarson]

I got the suggestion of donbettis to work. After populating the text box, I had it just take the value and assign it to a string and then opened that particular report using the string.

My code:
Private Sub cmdRunReport_Click()
' runs the selected report
Dim strRptName As String
strRptName = lstReports.Value
If InStr(1, strRptName, "STATE", vbTextCompare) And IsNull(cmbStateSelect) Then
MsgBox "You MUST select a state to view when selecting this particular report!", vbExclamation, "SELECTION ERROR!"
Else
DoCmd.OpenReport strRptName, acViewPreview
End If
End Sub

 

[Fizzio]

Here is the code I use to populate a listbox (Can be mirrored to produce a list of queries, forms etc just by changing the containers)

Dim dbs As DAO.Database, ctr As Container, lstList As String, doc As Document
Set dbs = CurrentDb
lstList = ""
Set ctr = dbs.Containers("Reports")

'Create Value list for listbox
For Each doc In ctr.Documents
If Left(doc.Name, 3) = "rpt" Then 'will only populate with reports beginning with "rpt"
lstList = lstList & Mid(doc.Name, 4, Len(doc.Name)) & ";" 'trims off the "rpt" and creates a list
End If
Next

'Populate List Box
lstList = Left(lstList, Len(lstList) - 1)
Me.lisSelRep.RowSourceType = "Value List"
Me.lisSelRep.RowSource = lstList

I open the report I want simply with an On-Click of a label with the event procedure set to
DoCmd.OpenReport "rpt" & Me.NameofListbox, acViewPreview 'the "rpt" & bit just replaces the "rpt" on the fromt of the report name.