Count number of open reports using VBA

[SJ]

I need to enumerate through the reports collection and count the number of
reports that are open.

 

[Marshall Barton]

I need to enumerate through the reports collection and count the number of
reports that are open.

No enumeration needed, just use: Reports.Count

 

[Daniel Pineault]

The following Function should help you out:

'---------------------------------------------------------------------------------------
' Procedure : ListOpenRpts
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a list of all the loaded reports (preview or design)
' separated by ; (ie: Report1;Report2;Report3)
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Oct-30 Initial Release
'---------------------------------------------------------------------------------------
Function ListOpenRpts()
On Error GoTo Error_Handler

Dim DbO As AccessObject
Dim DbP As Object
Dim Rpts As Variant

Set DbP = Application.CurrentProject

For Each DbO In DbP.AllReports 'Loop all the reports
If DbO.IsLoaded = True Then 'Ensure the report if open
Rpts = Rpts & ";" & DbO.Name
End If
Next DbO

If Len(Rpts) > 0 Then
Rpts = Right(Rpts, Len(Rpts) - 1) 'Truncate initial ;
End If

ListOpenRpts = Rpts

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: ListOpenRpts" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function

'---------------------------------------------------------------------------------------
' Procedure : CountOpenRpts
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a count of the number of loaded reports (preview or
design)
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Oct-30 Initial Release
'---------------------------------------------------------------------------------------
Function CountOpenRpts()
On Error GoTo Error_Handler

Dim DbO As AccessObject
Dim DbP As Object
Dim Rpts As Variant

Set DbP = Application.CurrentProject
CountOpenRpts = 0

For Each DbO In DbP.AllReports 'Loop all the reports
If DbO.IsLoaded = True Then 'Ensure the report if open
CountOpenRpts = CountOpenRpts + 1
End If
Next DbO

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: CountOpenRpts" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

Marshall,

Reports.Count will give you the total number of reports, but how will it
only return the number of currently open reports? Do you not need to loop
through the collection and test IsLoaded to get a proper count of open
reports?
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Marshall Barton]

Reports.Count will give you the total number of reports, but how will it
only return the number of currently open reports? Do you not need to loop
through the collection and test IsLoaded to get a proper count of open
reports?

Not so. The Reports collection only contains open reports.

It is appropriate to spend a couple of minutes testing
before contradicting another's statement. Just enter
?Reports.Count
and see what you get.

 

[John W. Vinson]

Reports.Count will give you the total number of reports, but how will it
only return the number of currently open reports? Do you not need to loop
through the collection and test IsLoaded to get a proper count of open
reports?

No. Like the Forms collection, the Reports collection consists of only *open*
Reports.

See AllReports and AllForms if you want to see all objects, open or not.

 

[Daniel Pineault]

Thank you for the clarification.

I was by no mean contradicting you. I was simply asking a question so I
could learn something new.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

Based on Marshall and John's comments, below 2 slightly modified versions of
the previous functions that use the Reports Collection rather that then
AllReports Collection.

'---------------------------------------------------------------------------------------
' Procedure : ListOpenRpts
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a list of all the loaded reports (preview or design)
' separated by ; (ie: Report1;Report2;Report3)
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Oct-30 Initial Release
' 2 2009-Oct-31 Switched from AllReports to Reports
collectio
'---------------------------------------------------------------------------------------
Function ListOpenRpts()
On Error GoTo Error_Handler

Dim DbR As Report
Dim DbO As Object
Dim Rpts As Variant

Set DbO = Application.Reports

For Each DbR In DbO 'Loop all the reports
Rpts = Rpts & ";" & DbR.Name
Next DbR

If Len(Rpts) > 0 Then
Rpts = Right(Rpts, Len(Rpts) - 1) 'Truncate initial ;
End If

ListOpenRpts = Rpts

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: ListOpenRpts" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function

'---------------------------------------------------------------------------------------
' Procedure : CountOpenRpts
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Returns a count of the number of loaded reports (preview or
design)
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Oct-30 Initial Release
' 2 2009-Oct-31 Switched from AllReports to Reports
collectio
'---------------------------------------------------------------------------------------
Function CountOpenRpts()
On Error GoTo Error_Handler

CountOpenRpts = Application.Reports.Count

Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: CountOpenRpts" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.