How to post certain reports into a list box?

[elainenguyen]

I want to have a list box that show certain reports, anybody can help?
when I do my querry, it shows all the available reports, not certain reports that I want.
Thanks alot!

[nico5038]

In a case like this I normally "code" my report names like:
rptAutoOrders
rptAutoInvoices, etc.
Thus I can filter for "Like rptAuto*"

Idea ?

Nic;o)

[ADezii]

I want to have a list box that show certain reports, anybody can help?
when I do my querry, it shows all the available reports, not certain reports that I want.
Thanks alot!

Here is sample code (Access 2003) that will load only Reports beginning with rpt into a List Box called lstTest. Hope this helps.

Dim MyReport As Report, numOfReports As Integer, T As Integer
 
Me![lstTest].RowSourceType = "Value List"
 
numOfReports = CurrentProject.AllReports.Count
 
For T = 0 To numOfReports - 1
  If Left$(CurrentProject.AllReports(T).Name, 3) = "rpt" Then
    lstTest.AddItem CurrentProject.AllReports(T).Name
  End If
Next

[NeoPa]

ADezii,
There is another way to access items in a collection (Your way works fine, but this (For...Each) is an alternative which takes a little less setting up).

Private Sub Form_OnOpen()
    Dim strRowSource As String
    Dim rptThis As Report
 
    For Each rptThis in CurrentProject.AllReports
        If Left(rptThis.Name, 3) = "rpt" Then _
            strRowSource = strRowSource & ";" & rptThis.Name
    Next
    With Me!lstTest
        .RowSourceType = "Value List"
        .RowSource = Mid(strRowSource, 2)
    End With
End Sub

[ADezii]

ADezii,
There is another way to access items in a collection (Your way works fine, but this (For...Each) is an alternative which takes a little less setting up).

Private Sub Form_OnOpen()
    Dim strRowSource As String
    Dim rptThis As Report
 
    For Each rptThis in CurrentProject.AllReports
        If Left(rptThis.Name, 3) = "rpt" Then _
            strRowSource = strRowSource & ";" & rptThis.Name
    Next
    With Me!lstTest
        .RowSourceType = "Value List"
        .RowSource = Mid(strRowSource, 2)
    End With
End Sub

NeoPa:
Correct me if I'm wrong, but I'm pretty sure that your approach will fail. The AllReports Collection does not contain Report Objects but AccessObject Objects. rptThis should not be declared as Report but as an Object as in:

Dim strRowSource As String
 Dim rptThis As Object
 
    For Each rptThis In CurrentProject.AllReports
        If Left(rptThis.Name, 3) = "rpt" Then _
            strRowSource = strRowSource & ";" & rptThis.Name
    Next
 
    With Me!lstTest
        .RowSourceType = "Value List"
        .RowSource = Mid(strRowSource, 2)
    End With

[NeoPa]

You're absolutely right ADezii (and even if not - you would have been right to raise it for discussion).
I was working from your code and assumed (incorrectly it seems) that an M$ Office collection called AllReports, would contain Reports (How dumb am I?).
I only used this in earnest today (after my post here) when I was trying to delete all reports from a database and it refused to let me Dim the variable as a Report !?! So I used Object there (as you suggest). It didn't let me delete them anyway using DoCmd.Delete so it was a waste of time :( I always try to be specific with my Dims as clarity always helps with understanding I find. In this case though, apparently it is only defined as an Object.
Anyway, thanks for finding & fixing :)

[elainenguyen]

Thanks alot guys!

[NeoPa]

No problem Elaine - We're glad to help.