How-to count reports beginning with a certain name?

[Guest]

Hi,

I have a command button on a form which prints 16 reports without a problem
-- here's the code snippet:

Dim stDocName As String
Dim i As Integer
i = 1

Do While i <= 16
stDocName = "rptVA" & i
DoCmd.OpenReport stDocName, acNormal
i = i + 1
Loop
Exit Sub

However, if reports are added (or deleted) I have to change the integer on
the do while line. Easy enough, but is there a way to use the Reports.Count
property to count the number of reports that begin with "rptVA"?

TIA
Allan

 

[Douglas J. Steele]

No, but assuming you're using Access 2000 or newer, you could do this:

Dim objReport As AccessObject
Dim objDBS As Object

Set objDBS = Application.CurrentProject
For Each objReport In objDBS.AllReports
If Left(objReport.Name, 5) = "rptVA" Then
DoCmd.OpenReport objReport.Name, acNormal
End If
Next obj
Set objDBS = Nothing

For Access 97 or earlier, use:

Dim dbCurr As DAO.Database
Dim docCurr As DAO.Document

Set dbCurr = CurrentDb
For Each docCurr In dbCurr.Containers("Reports").Documents
If Left(docCurr.Name, 5) = "rptVA" Then
DoCmd.OpenReport docCurr.Name, acNormal
End If
Next docCurr
Set dbCurr = Nothing

 

[Guest]

Sweet! Works like a charm!

For other users who may find this useful a small correction -- change "Next
obj" to "Next objReport"

Thanks a lot Doug!

Allan

 

[Douglas J. Steele]

Sorry about that! I started by copying a snippet from somewhere, and decided
to rename the variables. Obviously I forgot one...