For Marshall Barton...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A followup question to that OnOpen event procedure:

Can you explain the code you gave me and how it dealt with the subreport
open event? What is the If Then statement doing?

Thanks.
Ngan

Sub Report_Open()
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = " . . . "
Initialized = True
End If
End Sub
 
Oh, this is different than what I (mis?)read as your
original question. Forget the stuff we've been discussing,
it won't work for multiple main reports.

The only thing I can think of here is to use the subreport
**control** Link Master/Child properties. I think you want
to set Link Master to PersonID and Link Child to ClientID.

Be sure to remove the ClientID condition from the query.
The other parts of the query's Where clause that are
independent of the main report should remain the same.

In the future, please reply to the original thread so we can
follow the question throughout its discussion.
 
Thanks for your reply. Actually, you read my question correctly...I may have
forgotten to expand the question to include the issue about multiple main
reports.

In the past, I have used the child/master properties (mostly in the
form/subform). however, this requires the subreport's query to not have much
in the WHERE condition. Therefore it would bring back all the data and then
the child/master properties would filter. When I did that, it took alot
longer time to pull up the info.

That's when I started to look at the OnOpen event procedure to fix this
problem. Then I noticed that the subreport was used by two main reports. So
I wanted that to work too.

Is there another option to the child/master properties? The problem I have
with that is the subreport's SQL statement. It has a subquery ("SELECT
Max(EffectiveTo) AS MaxEffTo ..." where it's WHERE condition is based on a
field in the main report.

Here is what I'm trying to do. A client can have more than one eligibility
record. I want to grab the most current elig record (therefore the max
EffectiveTo date) for that client. So I would do a query to get the max date
(if I add any more fields, i would get more than one record for that client).
Then I create another query linked to that first query to add more fields
(eligstatus, clientID, equipment...)...

Doing the child/master properties wouldn't work because the subquery's WHERE
condition requires a specific report name.

The workaround I did was to have two subreports with your initial suggestion
of: Sub Report_Open()
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = " . . . "
Initialized = True
End If
End Sub

Of course, if I need another main report to share that subreport, I would
have to create another copy. Do you know if there is another way to do this?
Below is my original record source.

Thanks.
Ngan

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])))));"


Marshall Barton said:
Oh, this is different than what I (mis?)read as your
original question. Forget the stuff we've been discussing,
it won't work for multiple main reports.

The only thing I can think of here is to use the subreport
**control** Link Master/Child properties. I think you want
to set Link Master to PersonID and Link Child to ClientID.

Be sure to remove the ClientID condition from the query.
The other parts of the query's Where clause that are
independent of the main report should remain the same.

In the future, please reply to the original thread so we can
follow the question throughout its discussion.
--
Marsh
MVP [MS Access]

Oops...one more thing about the code. I want the subreport to open with the
WHERE clientID = WhateverReportIsOpen!ClientID. I tried to use Parent and Me
and it didn't display anything. The two reports that sure the subreport is
rptRider and rptRiderHistory.

If I do Where ClientID = " & Parent!PersonID & "...", the subreport displays
info for the first Client only. The other clients don't see anything.
Click to expand...
Click to expand...
 
ngan said:
Thanks for your reply. Actually, you read my question correctly...I may have
forgotten to expand the question to include the issue about multiple main
reports.

In the past, I have used the child/master properties (mostly in the
form/subform). however, this requires the subreport's query to not have much
in the WHERE condition. Therefore it would bring back all the data and then
the child/master properties would filter. When I did that, it took alot
longer time to pull up the info.

That's when I started to look at the OnOpen event procedure to fix this
problem. Then I noticed that the subreport was used by two main reports. So
I wanted that to work too.

Is there another option to the child/master properties? The problem I have
with that is the subreport's SQL statement. It has a subquery ("SELECT
Max(EffectiveTo) AS MaxEffTo ..." where it's WHERE condition is based on a
field in the main report.

Here is what I'm trying to do. A client can have more than one eligibility
record. I want to grab the most current elig record (therefore the max
EffectiveTo date) for that client. So I would do a query to get the max date
(if I add any more fields, i would get more than one record for that client).
Then I create another query linked to that first query to add more fields
(eligstatus, clientID, equipment...)...

Doing the child/master properties wouldn't work because the subquery's WHERE
condition requires a specific report name.

The workaround I did was to have two subreports with your initial suggestion
of: Sub Report_Open()
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = " . . . "
Initialized = True
End If
End Sub

Of course, if I need another main report to share that subreport, I would
have to create another copy. Do you know if there is another way to do this?
Below is my original record source.

Thanks.
Ngan

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])))));"
Click to expand...


Speed is a separate issue. Let's get the subreport working
for both the main reports.

To do that, you must remove the reference to the main report
in the subreport's record source query. If you haven't
already done this, here's a way to write the query:

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 As T
WHERE EffectiveTo =
(SELECT Max(EffectiveTo)
FROM tblEligibility As X
WHERE X.ClientID = T.ClientID)

Now that the query is independent of whatever main report
contains the subreport, you can use the Link Master/Child
properties.

If that gets everything working the way it's supposed to,
but it's still too slow, try posting the query to the
Queries newsgroup and ask if anyone knows of a way to make
it run faster.
 
That worked, thanks! There wasn't a speed issue (not that I noticed). Maybe
the speed issue is with the form where I had a few more subforms.

Marshall Barton said:
ngan said:
Thanks for your reply. Actually, you read my question correctly...I may have
forgotten to expand the question to include the issue about multiple main
reports.

In the past, I have used the child/master properties (mostly in the
form/subform). however, this requires the subreport's query to not have much
in the WHERE condition. Therefore it would bring back all the data and then
the child/master properties would filter. When I did that, it took alot
longer time to pull up the info.

That's when I started to look at the OnOpen event procedure to fix this
problem. Then I noticed that the subreport was used by two main reports. So
I wanted that to work too.

Is there another option to the child/master properties? The problem I have
with that is the subreport's SQL statement. It has a subquery ("SELECT
Max(EffectiveTo) AS MaxEffTo ..." where it's WHERE condition is based on a
field in the main report.

Here is what I'm trying to do. A client can have more than one eligibility
record. I want to grab the most current elig record (therefore the max
EffectiveTo date) for that client. So I would do a query to get the max date
(if I add any more fields, i would get more than one record for that client).
Then I create another query linked to that first query to add more fields
(eligstatus, clientID, equipment...)...

Doing the child/master properties wouldn't work because the subquery's WHERE
condition requires a specific report name.

The workaround I did was to have two subreports with your initial suggestion
of: Sub Report_Open()
Static Initialized As Boolean
If Not Initialized Then
Me.RecordSource = " . . . "
Initialized = True
End If
End Sub

Of course, if I need another main report to share that subreport, I would
have to create another copy. Do you know if there is another way to do this?
Below is my original record source.

Thanks.
Ngan

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])))));"
Click to expand...


Speed is a separate issue. Let's get the subreport working
for both the main reports.

To do that, you must remove the reference to the main report
in the subreport's record source query. If you haven't
already done this, here's a way to write the query:

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 As T
WHERE EffectiveTo =
(SELECT Max(EffectiveTo)
FROM tblEligibility As X
WHERE X.ClientID = T.ClientID)

Now that the query is independent of whatever main report
contains the subreport, you can use the Link Master/Child
properties.

If that gets everything working the way it's supposed to,
but it's still too slow, try posting the query to the
Queries newsgroup and ask if anyone knows of a way to make
it run faster.
Click to expand...
 
Back
Top