Listing All Report Objects in List Box - Wish to hide some

[Frothingslosh]

I'm creating a form that will list all report objects in my database, and then the user will select one and print it.

The controls on the form are:

• List box named lstReports
• Check box named chkPreview
• Command button named cmdOpenReport

I did a search and found some code that does this and still works, but basically, I have two concerns. I have one report I want to hide - that one is called 'rptMarketBudgetSheet' while the rest of the reports have names in standard English (I'd planned on doing this from the start). I also want to ensure that only one report is selected, as no user will need to print multiple reports, and getting multiple different reports opening might just confuse some of them.

I'm really rusty (it's been years since I've done this), so I'm not sure how I could go in and make sure that reports beginning with "rpt*" don't get listed. If anyone can tell me what I need to do to do that, I'd be most appreciative. Same with ensuring only one report gets selected.

List box code:

Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    ' Purpose:  Supplies the name of all saved reports to a list box.
    ' Usage:    Set the list box's RowSourceType property to:? EnumReports
    '               leaving its RowSource property blank.
    ' Notes:    All arguments are provided to the function automatically.
    ' Author:   Allen Browne        (removed email due to post count)    Feb.'97.
    Dim db As dao.Database, dox As Documents, i As Integer
    Static sRptName(255) As String              ' Array to store report names.
    Static iRptCount As Integer                 ' Number of saved reports.
    ' Respond to the supplied value of "code".
    Select Case code
        Case acLBInitialize                     ' Called once when form opens.
            Set db = CurrentDb()
            Set dox = db.Containers!Reports.Documents
            iRptCount = dox.Count               ' Remember number of reports.
            For i = 0 To iRptCount - 1
                sRptName(i) = dox(i).Name       ' Load report names into array.
            Next
            EnumReports = True
        Case acLBOpen
            EnumReports = Timer                 ' Return a unique identifier.
        Case acLBGetRowCount                    ' Number of rows
            EnumReports = iRptCount
        Case acLBGetColumnCount                 ' 1 column
            EnumReports = 1
        Case acLBGetColumnWidth                 ' 2 inches
            EnumReports = 2 * 1440
        Case acLBGetValue                       ' The report name from the array.
            EnumReports = sRptName(row)
        Case acLBEnd
            Erase sRptName                      ' Deallocate array.
            iRptCount = 0
    End Select
End Function

 

[AccessMSSQL]

What I do with my reports menu is I load all of the report names into a table called tlkpReports. This table has fields (ReportID, ReportName, InActiveYN). Then my reports menu populates a list box with all reports in this table where InActiveYN is false). Then you can exclude the one report, or many others as needed. The only issue is you will have to maintain that table - which isn't very much maintenance. If you create a new report just make sure you add it to the table before releasing a new version.

 

[boblarson]

No code needed for populating the listbox. Just use a query:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;


Then you can open the report by using the listbox's on click event

Private Sub ListboxNameHere_Click()
   DoCmd.OpenReport Me.ListboxNameHere, acViewPreview
End Sub

I typically use this SQL:
SELECT Mid([Name],5) AS ReportName, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764) AND ((InStr([Name],"rpt_"))=True))
ORDER BY Mid([Name],5);



which hides the report until I am ready to deploy it. Then I will rename my report with the rpt_ part in front and then it will show up in the list. And I set the bound column of the listbox to 2 so it uses the full name instead of the display name.

 

[Frothingslosh]

I need it to be more or less automated, as this is a temporary job, and I want it to grab any reports people may add to the system down the road after I'm gone. Sooooo I'll switch to that SQL for the list box tomorrow.

Thanks a ton!

 

[steve21nj]

What I do with my reports menu is I load all of the report names into a table called tlkpReports. This table has fields (ReportID, ReportName, InActiveYN). Then my reports menu populates a list box with all reports in this table where InActiveYN is false). Then you can exclude the one report, or many others as needed. The only issue is you will have to maintain that table - which isn't very much maintenance. If you create a new report just make sure you add it to the table before releasing a new version.

I know this is a few months old, but how do you link the table [tlkpReports] with the actual reports? I remember Mr Larson mentioning this awhile back but I could never figure it out. I like the idea of the yes/no to hide reports. I also would like to display the report description in the list box.

I created my own table [tblReports], does the naming of the table matter in this situation? I have the names of the reports in the table, but can never get the actual reports to show. How do you establish the relationship to build a query with the creteria of yes "to show the report" with just names in a table.

 

[unclefink]

This is exactly what I am working on right now. I found the query mentioned by Bob in a forum but that is all there was. I like the table concept, is this a combination of both; if so I have the same question, how do you link the reports to the table?

 

[boblarson]

This is exactly what I am working on right now. I found the query mentioned by Bob in a forum but that is all there was. I like the table concept, is this a combination of both; if so I have the same question, how do you link the reports to the table?

You would need to manually enter the data into the table. The method I showed allows you to have it show up just by naming it with a certain prefix.