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,

 

[jasmith4]

Is it a question of months that don't have 31 days?

And yes, you should use each sub-report's Load event.

 

[seenfresh]

Is it a question of months that don't have 31 days?

And yes, you should use each sub-report's Load event.

I don't think the error message pertain to the Date values, but rather my attempt to set the focus of an object that apparently does not exist... Also the sub report code in the load event does not start until I actually click on one of the objects in the sub-report in report view, which is a bit odd...

 

[Norie]

Why are you using SetFocus?

 

[seenfresh]

I figured if I tried to set the focus on the object I wouldn't receive the same error message when I was trying to change the objects control source...

In the code below, if I exclude the Me.S2.SetFocus line the line below it returns the same error message...?

 

[Norie]

I was kind of wondering why you are using SetFocus at all.


Usually it's not needed to set the focus on an object like a control to change it's properties.

Along as you correctly reference it of course, which might be the problem.

Is the code you've posted in the load event of the subreport?

When you open the subreport on it's own does it work?

Perhaps what you need to do is move all the code into load event of the main report.

Then create a reference to the subreport control on the main report and use that to set the properties of the controls on the subreport.

That's all I can think of right now, I'm actually wondering why you are using a subreport.

 

[seenfresh]

I was using Set Focus more as a test because I was receiving the error...

Yes, the code is in the Load event of the sub-report

Yes, the code works when I open the sub-report on it's own

I'll try your suggestion...the use of the sub-report is a bit complicated to explain, but if I have to I can change the sub-report and make it a stand-alone report...

Thanks again Norie for you help!

 

[seenfresh]

Norie,

How do you set the control source and properly reference an object that is not open?

For example:

Dim rpt2 As Report
Set rpt2 = Reports![Net Booked Sales_Summary Analysis Sub Report]
rpt2.SY1.Caption = Format(Cyr, "mmm-yy")

OR

Reports![Net Booked Sales_Summary Analysis Sub Report].Controls!SY1.Caption = Format(Cyr, "mmm-yy")

Both produce run-time error 2451

 

[Norie]

To be honest I don't know.

I take it the error meesage you are getting is some sort of 'object not found' kind of thing?

I'll take a look into it later.

I'm still wondering what you are trying to do and if there is some way other than the main report's Load event.

Couldn't you use aggregrate functions like DSum, DCount... with references to the main report in.

In fact you already appear to be using similar references in the code:

Set mst = Forms("Master Form")

Cyr = mst.CPeriod

PreDate = mst.PrvWk

CurDate = mst.CurWk

 

[seenfresh]

Yeah... it seems I cannot change the control source or adjust properties of a report unless it is open...Probably some sort of limitation when using sub-reports.

I could always delete the sub-report and create a stand alone report. This would work, but for presentation and formatting purposes would be nice to have it in the main report. I also wanted to see if we could make it work, because it seems I like to do things the hard way! lol Like the challenge!

I'm going to research more on this and post back if I find anything...

Thanks again!