Changing recordsource on open

[Guest]

I have a subreport that is used by two reports. I need to change the
recordsource to have the WHERE be set to whatever the report is using the
subreport.

In the OnOpen of the rptRider report, I have the following code:

Me!rptEligSub.Report.RecordSource = "SELECT ClientID, EffectiveTo,
Eligibility, Equipment, PCA, " & _
"IIf([Cognitive]=0,Null,'C') & IIf([Hearing]=0,Null,'H') &
IIf([MentalHealth]=0,Null,'M') & IIf([Physical]=0,Null,'P') &
IIf([Visual]=0,Null,'V') AS Disability, " & _
"NoID FROM tblEligibility WHERE
((ClientID=[reports]![rptRider]![personid]) AND " & _
"(EffectiveTo=(SELECT Max(EffectiveTo) AS MaxEffTo FROM tblEligibility "
& _
"WHERE ((ClientID=[Reports]![rptrider]![personid])))));"

When I do this, I get the following error:

You have entered an expression that has an invalid reference to the property
Form/Report

What can I do to change the recordsource so the WHERE ClientID = whatever
report opens ?

 

[Marshall Barton]

I have a subreport that is used by two reports. I need to change the
recordsource to have the WHERE be set to whatever the report is using the
subreport.

In the OnOpen of the rptRider report, I have the following code:

Me!rptEligSub.Report.RecordSource = "SELECT ClientID, EffectiveTo,
Eligibility, Equipment, PCA, " & _
"IIf([Cognitive]=0,Null,'C') & IIf([Hearing]=0,Null,'H') &
IIf([MentalHealth]=0,Null,'M') & IIf([Physical]=0,Null,'P') &
IIf([Visual]=0,Null,'V') AS Disability, " & _
"NoID FROM tblEligibility WHERE
((ClientID=[reports]![rptRider]![personid]) AND " & _
"(EffectiveTo=(SELECT Max(EffectiveTo) AS MaxEffTo FROM tblEligibility "
& _
"WHERE ((ClientID=[Reports]![rptrider]![personid])))));"

When I do this, I get the following error:

You have entered an expression that has an invalid reference to the property
Form/Report

What can I do to change the recordsource so the WHERE ClientID = whatever
report opens ?

You got that error because the subreport is not opened when
the main report's Open event runs.

You can only set a report's (or subreport's) RecordSource
property in its own Open event procedure. For subreports,
this is complicated by the fact that you can only set it the
first time the subreport is opened. Here's some logic to
demonstrate how to deal with the issues on the subreport's
Open event:

Sub Report_Open()
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = " . . . "
Initialized = True
End If
End Sub