Subreport with different recordsource

[richardlegare]

I want to change the recordsource for every subreport before printing
it but I can't figure out How.
I have a report to which I set the recordsource by creating a
temporary table. The field in this table are DateStart and DateEnd and
Name. For each record, I want to show a sub report and modified it's
record source to show record where the date is between DateStart and
DateEnd.

I was looking to set the subreport recordsource with Report_Open() or
something like that but it only changes for the first record.

Here's mycode on the subreport:
Private Sub Report_Open(Cancel As Integer)
Report.RecordSource = "SELECT DISTINCT PersonnelHeure.NoProjet,
PersonnelHeure.Discipline, PersonnelHeure.Activite FROM PersonnelHeure
WHERE (((PersonnelHeure.Noms)='" & Parent.Noms & "') AND
((PersonnelHeure.Date) Between #" & Parent.DateStart & "# And #" &
Parent.DateEnd & "#)) ORDER BY PersonnelHeure.NoProjet;"
End Sub

Any ideas how to do this

 

[Marshall Barton]

I want to change the recordsource for every subreport before printing
it but I can't figure out How.
I have a report to which I set the recordsource by creating a
temporary table. The field in this table are DateStart and DateEnd and
Name. For each record, I want to show a sub report and modified it's
record source to show record where the date is between DateStart and
DateEnd.

I was looking to set the subreport recordsource with Report_Open() or
something like that but it only changes for the first record.

Here's mycode on the subreport:
Private Sub Report_Open(Cancel As Integer)
Report.RecordSource = "SELECT DISTINCT PersonnelHeure.NoProjet,
PersonnelHeure.Discipline, PersonnelHeure.Activite FROM PersonnelHeure
WHERE (((PersonnelHeure.Noms)='" & Parent.Noms & "') AND
((PersonnelHeure.Date) Between #" & Parent.DateStart & "# And #" &
Parent.DateEnd & "#)) ORDER BY PersonnelHeure.NoProjet;"
End Sub

Any ideas how to do this

Not that way. As you've already found, you can only modify
the record source property once.

If the problem were for a single value instead of a range of
values, then the normal approach is to use the Link
Master/Child properties (i.e. the Noms field)

Lacking any other way, you can filter the details in a
(sub)report by cancelling the detail section's Format event.
E.g.
Cancel = (Me.[Date] < Parent.DateStart Or Me.[Date] >
Parent.DateEnd)