Subreports VBA

[James]

How do i change the recordsource for a subreport in vba
when the report loads?

many thanks

J

 

[Tim Ferguson]

How do i change the recordsource for a subreport in vba
when the report loads?

With Me!SubFormControl
.Form.Recordsource = strNewSQL
.ChildLinkField = Whatever
.ParentLinkField =WhateverElse
.Form.Requery

End With

Not sure about the LinkField property names, but it should be something
like that.

Hope it helps


Tim F

 

[Guest]

Thanks for your reply but it comes up with the error
msg "you have entered an expresion that has an invalid
reference to the property Form/Report"

 

[Tim Ferguson]

Thanks for your reply but it comes up with the error
msg "you have entered an expresion that has an invalid
reference to the property Form/Report"

Okay, that's me speed reading: I was expecting a subform question rather
than a subreport. The reference should be to

Me!SubFormControl.Report.Recordsource

On the other hand, if you've already got that right, I am not quite sure
that the problem is. Can you post the code you are actually using, and
point out exactly which line causes the error?

B Wishes


Tim F

 

[James]

Yeah the code im using is

Me.mdate.Report.RecordSource = "SELECT
tblJobDetails.Partner, tblJobDetails.[M DATE ON Going
Research]FROM tblJobDetails WHERE (((tblJobDetails.[M
DATE ON Going Research]) Between #" & StartDate & "# And
#" & EndDate & "#)) ORDER BY tblJobDetails.Partner;"

mdate is the subreport. Startdate and enddate are two
varibles it asks for when starting up but I have tried
just a simple SQL code but it still brings up the same
error. The error highlights this line and says
"you have entered an expresion that has an invalid
reference to the property Form/Report"

Also i have tried putting the code into mdate itself but
it has an error saying you cant change sourcecode when
printing has started.

thankyou for helping me with this.

 

[Tim Ferguson]

Me.mdate.Report.RecordSource = "SELECT
tblJobDetails.Partner, tblJobDetails.[M DATE ON Going
Research]FROM tblJobDetails WHERE (((tblJobDetails.[M
DATE ON Going Research]) Between #" & StartDate & "# And
#" & EndDate & "#)) ORDER BY tblJobDetails.Partner;"

There are some problems with the SQL -- have you checked this out in the
query design window?

Also i have tried putting the code into mdate itself but
it has an error saying you cant change sourcecode when
printing has started.

Is this in the OnFormat event or something? I am not surprised that Access
is complaining if you are altering the recordsources on the fly like that.
My instinct would be to get the basic query right before opening the report
(that is what joins are for, after all) and not mess about.

If there is some reason why you need an exceptionally complex approach,
then you may be better off in the m.p.a.reports group.

B Wishes


Tim F