Loop though controls on a report

[neemi]

I am trying to loop through all of my reports on the db using the following code:

Dim obj As AccessObject
dbs As Object

Set dbs = Application.CurrentProject

For Each obj In dbs.AllReports
Debug.Print obj.Name
Next obj


What I want to do is that with each report found I need to be able to loop through the controls on the report.

I know you can use the controls collection to loop though them but not sure how to implement this.

Any help wid this appreciated.

Cheers,
N

 

[Eupher]

neemi,

You can nest For Each ... Next loops. Maybe something like this:

Dim obj As AccessObject
Dim dbs As Object
Dim ctrl As Control

Set dbs = Application.CurrentProject

For Each obj in dbs.AllReports
    For Each ctrl in obj.Controls
        'do something here
    Next ctrl
Next obj

HTH...

Ken S.

 

[Eupher]

After testing a little, something like this might work better:

Dim dbs As Object
Dim obj As AccessObject
Dim rpt As Report
Dim ctrl As Control

Set dbs = Application.CurrentProject

For Each obj In dbs.AllReports
    DoCmd.OpenReport obj.Name, acViewDesign, , , acHidden
    Set rpt = Application.Reports(obj.Name)
    Debug.Print rpt.Name
    For Each ctrl In rpt.Controls
        Debug.Print Space(5) & ctrl.Name
    Next ctrl
    DoCmd.Close acReport, obj.Name
Next obj

Set rpt = Nothing
Set dbs = Nothing

HTH,

Ken S.

 

[neemi]

Thanks for your help. I managed to get this done in the end. Do you know how I can get a count of the reports?

 

[Eupher]

neemi,

Declare an integer variable, then increment it each time through your For Each obj loop.

Ken S.

 

[mordja]

neemi,

For a count look at the properties of the allreports collection.

ie

CurrentProject.AllReports.count

Mordja

 

[neemi]

Can u use the allreports.count for report which have a particular word in the name? ie. all reports where the name has the word "Admin" in there?

 

[mordja]

Neemi,

No for that you would need to check the name of each report and count the result.

Dim rpt As Report
Dim intCount As Integer

intCount = 0

For Each rpt In CurrentProject.Reports
        If InStr(1, rpt.name, "Admin", vbTextCompare) > 0 Then
            intCount = intCount + 1
        End If
Next rpt

Mordja