Can pass subform Recordsource to a Report

[Federico]

I have a form where two listboxes filter data so a subform quickly displays
filtered records. Coding allows for multiselect, and a SQL string is
generated and applied to the subform recordsource:

Forms!ReviewSamplesResult!Details.Form.RecordSource = strSQL

Since the user needs to be able to print this subform data, I created an
OpenReport command button.

How can I pass the SQL that was built to generate the data for the report?
I tried things like
-----------
Dim strLinkCriteria As String

stDocName = "rptReviewSamplesResult"
strLinkCriteria = Forms!ReviewSamplesResult!Details.Form.RecordSource
DoCmd.OpenReport stDocName, acPreview
-----------

but did not fly. I am not very familiar with using OpenArgs and was
wondering if it could be a way to pass the data. also, don't you need to
provide the RecordSource BEFORE the Report opens?

Thanks for help.

Federico

 

[Marshall Barton]

I have a form where two listboxes filter data so a subform quickly displays
filtered records. Coding allows for multiselect, and a SQL string is
generated and applied to the subform recordsource:

Forms!ReviewSamplesResult!Details.Form.RecordSource = strSQL

Since the user needs to be able to print this subform data, I created an
OpenReport command button.

How can I pass the SQL that was built to generate the data for the report?
I tried things like
-----------
Dim strLinkCriteria As String

stDocName = "rptReviewSamplesResult"
strLinkCriteria = Forms!ReviewSamplesResult!Details.Form.RecordSource
DoCmd.OpenReport stDocName, acPreview
-----------

but did not fly. I am not very familiar with using OpenArgs and was
wondering if it could be a way to pass the data. also, don't you need to
provide the RecordSource BEFORE the Report opens?

The only place that can set a subreport's record source is
the subreport's Open event. The subreport's open even
procedure can retrieve the SQL statement from the main
report's OpenArgs (probably best), the form list box's row
source (report must know form and subform structure) or a
global variable (most potential problems).

In adidtion to that issue, there is a restriction that a
subreport's record source can only be set the first time the
subreport appears in the display/print of the main report.

Using Open Args, the code fo all that could look like:

Form button Click event:
. . .
DoCmd.OpenReport , . . ., _
OpenArgs:= strSQL

Subreport's Open event:
Static Initilaized As Boolean
If Not Initialized Then
If Not IsNull(Parent.OpenArgs) Then
Me.RecordSource = Parent.OpenArgs
Initialized = True
End If
End If

 

[Klatuu]

Marsh,
The OP did not state his version. OpenArgs for reports is not available
prior to 2003 and the app has to be in 2002-2003 format.
For a version prior to 2003, the SQL could be put in a hidden control on the
form from which the report is opened and the open event of the report can
reference that control:

Me.RecordSource = Forms!MyOpeningForm!SQLControl

 

[Federico]

Marsh,

thanks for the reply, I think I can play around and make it work.
But I think I didn't explain myself well on the subform issue:

Form "ReviewSamplesResult" has a subform "Details" whose RecordSource is
programmatically generated. THAT is the data I want to be able to use to
generate a Report. I don't have any subreports. Then I guess that the
"Parent.OpenArgs etc" sentences won't apply.

Thanks again,
Federico

 

[Marshall Barton]

Marsh,
The OP did not state his version. OpenArgs for reports is not available
prior to 2003 and the app has to be in 2002-2003 format.
For a version prior to 2003, the SQL could be put in a hidden control on the
form from which the report is opened and the open event of the report can
reference that control:

Me.RecordSource = Forms!MyOpeningForm!SQLControl

Dave, I just tested it and OpenArgs works for me in A2002.

I never used it so I am not sure, but I thought OpenArgs for
reports was introduced in A2K

 

[Marshall Barton]

thanks for the reply, I think I can play around and make it work.
But I think I didn't explain myself well on the subform issue:

Form "ReviewSamplesResult" has a subform "Details" whose RecordSource is
programmatically generated. THAT is the data I want to be able to use to
generate a Report. I don't have any subreports. Then I guess that the
"Parent.OpenArgs etc" sentences won't apply.

In that case try something more like:

Main form button Click event:

DoCmd.OpenReport . . ., _
OpenArgs:=Me.subformcontrol.Form.RecordSource

And in the report:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

 

[Federico]

Klatuu,

That worked great. Created hidden textbox in form, then OnOpen event in the
report to pull the recordsource from that control. Thanks! BTW, the project
is saved as Access 2000 (for no particular reason!).

Federico.