subreport

[Guest]

i was able to successfullly specify the recordsource for a report at runtime.
However, when I try to specify the same report's recordsource within a
subreport, I have no luck. Here is my code:

DoCmd.OpenReport "tbl_archive_subreport_daily_with_graph", acViewDesign,
acHidden
Reports!tbl_archive_subreport_daily_with_graph.Report.Filter = strSQL3
Reports!tbl_archive_subreport_daily_with_graph.Report.FilterOn = True
DoCmd.Close acReport, "tbl_archive_subreport_daily_with_graph", acSaveYes
DoCmd.OpenReport "report1_summary2", acViewDesign, acHidden
tbl_masterpop.status, Count(tbl_masterpop.status) AS CountOfstatus FROM
tbl_masterpop WHERE " & strSQL4 & " GROUP BY tbl_masterpop.status ORDER BY
tbl_masterpop.status;"
DoCmd.Close acReport, "report1_summary2", acSaveYes
DoCmd.OpenReport "report1_summary2", acPreview, , strSQL3


Here is tried to specify the recordsource for the subreport
"tbl_archive_subreport_daily_with_graph" by opening it up and specifying the
strSQL3 before i open the main report. But this is not working.

Any suggestions?

Thanks in advance,
geebee

 

[Marshall Barton]

i was able to successfullly specify the recordsource for a report at runtime.
However, when I try to specify the same report's recordsource within a
subreport, I have no luck. Here is my code:

DoCmd.OpenReport "tbl_archive_subreport_daily_with_graph", acViewDesign,
acHidden
Reports!tbl_archive_subreport_daily_with_graph.Report.Filter = strSQL3
Reports!tbl_archive_subreport_daily_with_graph.Report.FilterOn = True
DoCmd.Close acReport, "tbl_archive_subreport_daily_with_graph", acSaveYes
DoCmd.OpenReport "report1_summary2", acViewDesign, acHidden
tbl_masterpop.status, Count(tbl_masterpop.status) AS CountOfstatus FROM
tbl_masterpop WHERE " & strSQL4 & " GROUP BY tbl_masterpop.status ORDER BY
tbl_masterpop.status;"
DoCmd.Close acReport, "report1_summary2", acSaveYes
DoCmd.OpenReport "report1_summary2", acPreview, , strSQL3


Here is tried to specify the recordsource for the subreport
"tbl_archive_subreport_daily_with_graph" by opening it up and specifying the
strSQL3 before i open the main report. But this is not working.

You are going down the wrong road. For many reasons, you
should never modify a report's (or form's) design in a
running application.

The safe place to modify a record source is in the report's
Open event procedure.

Park the entire SQL statement in a hidden text box on a
form, then in the report's Open event:

Me.RecordSource = Forms!theform.textboxa

The situation is a little trickier for subreports because
they may appear many times in a main report, but the record
source can only be set the first time using code like this:

Static Initialized as Boolean
If Not Initialized Then
Me.RecordSource = Forms!theform.rextboxb
Initialized = True
End if

 

[Guest]

Marsh,

I put the following in the onActivate for the report used as a subreport:

Static Initialized as Boolean
If Not Initialized Then
Me.Filter= Forms!theform.rextboxb
Initialized = True
End if

No luck. Am I doing something wrong?

Thanks in advance,
geebee

 

[Marshall Barton]

You did several things wrong.

First, "no luck" doesn't describe what actually happend.
You probably got an error message and it would help us if
you copied the error number and description so we could see
what you see.

Second, the code was supposed to be in the Open event
**procedure**, not the OnActivate event property.

Third, you did not change the dummy names I used to
demonstrate the syntax with the names you are actually
using.

Finally, and this may not be "wrong", but I was talking
about setting the RecordSource to a complete SQL statement.
Because of some issues I've had with it, I don't use the
Filter property so I can not say if just setting the
criteria is sufficient. It's supposed to be, but I can't be
sure.