Report names in control

[rbb101]

I am using Allen Browne's code below in a control that lists all the reports
in my database. This works great.

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 (e-mail address removed) Feb.'97.

Dim db As 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


My question is whether there is a way to limit the reports that are listed.
For instance, can I limit it some way to the first 5 reports, or all reports
begining with a certain number or letter. I have about 45 reports, but would
like only certain reports to show in the control.

 

[Douglas J. Steele]

Actually, an easier way to extract all of the reports would be to use the
following SQL as the RowSource:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
ORDER BY [Name]

With that, you could easily use

SELECT TOP 5 [Name]
FROM MSysObjects
WHERE [Type] = -32764
ORDER BY [Name]

or

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
AND [Name] LIKE "A*"
ORDER BY [Name]

 

[Pieter Wijnen]

'for 5 reports only

iRptCount = 5 ' Remember number of reports.
For i = 0 To iRptCount - 1
sRptName(i) = dox(i).Name

Next

'for criteria
iRptCount =0
For i = 0 To dox.Count -1
If Instr(dox(i).Name, "IncludeMe") > 0 Then
sRptName(i) = dox(i).Name
iRptCount = iRptCount +1
End If
Next

HtH

Pieter

 

[rbb101]

Okay, thanks. That should accomplish what I need.

Actually, an easier way to extract all of the reports would be to use the
following SQL as the RowSource:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
ORDER BY [Name]

With that, you could easily use

SELECT TOP 5 [Name]
FROM MSysObjects
WHERE [Type] = -32764
ORDER BY [Name]

or

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764
AND [Name] LIKE "A*"
ORDER BY [Name]



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using Allen Browne's code below in a control that lists all the
reports
in my database. This works great.

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 (e-mail address removed) Feb.'97.

Dim db As 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


My question is whether there is a way to limit the reports that are
listed.
For instance, can I limit it some way to the first 5 reports, or all
reports
begining with a certain number or letter. I have about 45 reports, but
would
like only certain reports to show in the control.