How do you set a variables for controls in a subreport?

[Marc Brown]

I am trying to run a report which displays the data horizontally across the page to make the report more readable. I managed to do this by having a list of empty fields and assign the control source based on a filter which is prompt by a open event. The code is as follows where jobs(1,i) is a list of the jobs:

    Set Cnt = Reports![Job Subtrade Analysis].Controls
 
    For i = 0 To 6
        If i <= UBound(jobs, 2) Then
            Cnt("R" & i + 1).ControlSource = "CCSubCost_GFA.[" & jobs(1, i) & "]"
            Cnt("R1" & i + 1).ControlSource = "CCSubCost_FU.[" & jobs(1, i) & "]"
            Cnt("R2" & i + 1).ControlSource = "CCSubCost.[" & jobs(1, i) & "]"
            Cnt("R" & i + 1).Visible = True
            Cnt("R1" & i + 1).Visible = True
            Cnt("R2" & i + 1).Visible = True
            Cnt("P" & 1 + i).Caption = jobs(1, i)
        Else
            Cnt("R" & i + 1).ControlSource = ""
            Cnt("R1" & i + 1).ControlSource = ""
            Cnt("R2" & i + 1).ControlSource = ""
            Cnt("R" & i + 1).Visible = False
            Cnt("R1" & i + 1).Visible = False
            Cnt("R2" & i + 1).Visible = False
        End If
    Next
 

This code works great but the problem that I am running into is in this report there is a sub report and I cannot set a variable to loop through the controls of the sub report. I tried using the following but it returned an error stating that the subreport could not me found.

    Set Cnt = Reports![Job Subtrade Analysis]![CCSubCost_Subreport].Controls
 

Any suggestions would be greatly appreciated.

[NeoPa]

What's the name of your Subform/Subreport control on your main [Job Subtrade Analysis] report?

[Marc Brown]

The control has the same name as the report so CCSubCost_Subreport.

[NeoPa]

In that case you need either :

Set Cnt = Reports![Job Subtrade Analysis]![CCSubCost_Subreport]!Controls

or :

Set Cnt = Reports![Job Subtrade Analysis]![CCSubCost_Subreport].Report.Controls

See Referring to Items on a Sub-Form for the explanation why.