On Load event setting control properties

[seenfresh]

Greetings,

I have a bound report with a sub report included and am having some trouble setting the control source of some of the controls in the sub-report on load event.

The main report "Report1" on load event include some code that dynamically changes some of the controls properties and this report works fine. Within this report I have a sub-report where I have the following code in the sub-reports on load event:

Private Sub Report_Load()
Dim mst As Form
Dim Cyr As Date

Set mst = Forms("Master Form")

Cyr = mst.CPeriod
PreDate = mst.PrvWk
CurDate = mst.CurWk

Me.SY1.Caption = Format(Cyr, "mmm-yy")
Me.SY2.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 1, Day(Cyr)), "mmm-yy")
Me.SY3.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 2, Day(Cyr)), "mmm-yy")
Me.SY4.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 3, Day(Cyr)), "mmm-yy")
Me.SY5.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 4, Day(Cyr)), "mmm-yy")
Me.SY6.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 5, Day(Cyr)), "mmm-yy")
Me.SY7.Caption = Format(DateSerial(Year(Cyr), Month(Cyr) + 6, Day(Cyr)), "mmm-yy")


i1 = DateSerial(Year(Cyr), Month(Cyr), Day(Cyr))
Me.S1.SetFocus
Me.S1.ControlSource = "=[" & Month(i1) & "-" & Day(i1) & "-" & Year(i1) & "_V]"


i2 = DateSerial(Year(Cyr), Month(Cyr) + 1, Day(Cyr))
Me.S2.SetFocus
Me.S2.ControlSource = "=[" & Month(i2) & "-" & Day(i2) & "-" & Year(i2) & "_V]"


i3 = DateSerial(Year(Cyr), Month(Cyr) + 2, Day(Cyr))
Me.S3.SetFocus
Me.S3.ControlSource = "=[" & Month(i3) & "-" & Day(i3) & "-" & Year(i3) & "_V]"



i4 = DateSerial(Year(Cyr), Month(Cyr) + 3, Day(Cyr))
Me.S4.SetFocus
Me.S4.ControlSource = "=[" & Month(i4) & "-" & Day(i4) & "-" & Year(i4) & "_V]"


i5 = DateSerial(Year(Cyr), Month(Cyr) + 4, Day(Cyr))
Me.S5.SetFocus
Me.S5.ControlSource = "=[" & Month(i5) & "-" & Day(i5) & "-" & Year(i5) & "_V]"


i6 = DateSerial(Year(Cyr), Month(Cyr) + 5, Day(Cyr))
Me.S6.SetFocus
Me.S6.ControlSource = "=[" & Month(i6) & "-" & Day(i6) & "-" & Year(i6) & "_V]"


i7 = DateSerial(Year(Cyr), Month(Cyr) + 6, Day(Cyr))
Me.S7.SetFocus
Me.S7.ControlSource = "=[" & Month(i7) & "-" & Day(i7) & "-" & Year(i7) & "_V]"


Me.STot1.SetFocus
Me.STot1.ControlSource = "=Sum([" & Month(i1) & "-" & Day(i1) & "-" & Year(i1) & "_V])"
Me.STot2.SetFocus
Me.STot2.ControlSource = "=Sum([" & Month(i2) & "-" & Day(i2) & "-" & Year(i2) & "_V])"
Me.STot3.SetFocus
Me.STot3.ControlSource = "=Sum([" & Month(i3) & "-" & Day(i3) & "-" & Year(i3) & "_V])"
Me.STot4.SetFocus
Me.STot4.ControlSource = "=Sum([" & Month(i4) & "-" & Day(i4) & "-" & Year(i4) & "_V])"
Me.STot5.SetFocus
Me.STot5.ControlSource = "=Sum([" & Month(i5) & "-" & Day(i5) & "-" & Year(i5) & "_V])"
Me.STot6.SetFocus
Me.STot6.ControlSource = "=Sum([" & Month(i6) & "-" & Day(i6) & "-" & Year(i6) & "_V])"
Me.STot7.SetFocus
Me.STot7.ControlSource = "=Sum([" & Month(i7) & "-" & Day(i7) & "-" & Year(i7) & "_V])"

End Sub

With the line of code "Me.S2.SetFocus" I receive the following error # 2467: "The expression you entered refers to an object that is closed or doesn't exist"

I have verified that the object and name does exists and actually the sub report runs fine if I open it on it's own without opening it through the main report.

Overall I'm trying to figure out the best method of changing control properties (captions and control source) in a report as well as a sub report. Should I be using the On Load event of each report?

Any suggestions would be greatly appreciated.

Thanks in advance,

 

[Norie]

Actually you've reminded me of something.

I used to create forms with code but not really 'on the fly' at runtime.

What I was doing was using a sort of template form, asking the user what buttons they wanted on it, even offering them 'standard' buttons for things like closing
the form and opening another form.

Basically it was a quick and dirty way to create a form, sort of like a wizard.

Anyway, I rember I had to open the form in design view (using code - lots of flickering) to add the controls programmatically.

It was the only way I knew how to do it actually and it worked.

Probably not relevant to yout situation but just thought I'd 'share'.

By the way have you considered my idea of using aggregrate functions?