SQL rewrite

[Øystein Sund]

How do I rewrite the SQL property of a subreport's query before the
report opens?

I've tried putting the code me.rowsource=<query> in the report_load sub
of the subreport. But this didn't go too well.
Is it possible to change the rowsource from a form without opening the
report containing the sub-form? If so, how?


Any help would be most appreciated!


Sincerely
Oystein Sund

 

[Wayne Morgan]

Well, for starters, the new source would be sent to Me.RecordSource. You can't change the
RecordSource of a report unless it is open. What are you wanting to do with this change.
There are ways of doing this that might be simpler.

 

[Øystein Sund]

Well, for starters, the new source would be sent to Me.RecordSource. You can't change the
RecordSource of a report unless it is open. What are you wanting to do with this change.
There are ways of doing this that might be simpler.

I've got a form. The user is goint to type in start date, end date and a
textcode.

When the user clicks show report, the report containing the sub-report
will be opened with this code:

stDocName = "Service_pr_shop_en"
stLinkCriteria = "[ID2] >= #" & date1 & "# AND [ID2] <= #" & date2 & "#
and "
' Splitting text code
For I = LBound(StrArray) To UBound(StrArray)
stLinkCriteria = stLinkCriteria & "[textcode] Like '*" & StrArray(I) &
"*'"
If I < UBound(StrArray) Then
stLinkCriteria = stLinkCriteria & " or "
End If
Next
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria


The problem is that I don't know how to pass the criteria to the
sub-report. And it have to be passed by code because of the textcode.


Sincerely
Oystein Sund

 

[Wayne Morgan]

The easiest way to pass the criteria would probably be to set the criteria in the query
feeding the subreport and have it refer to the form for its information. This would make
the query a parameter query, but instead of it prompting you for the parameters, it would
simply get the answers from the form.

To refer to data on the form you would use syntax similar to

Forms!frmMyForm!txtMyTextbox (access may put brackets around these when you are done
typing)

So, for the date information, you could try criteria similar to

Between Forms!frmMyForm!txtFirstDate And Forms!frmMyForm!txtSecondDate

The form has to be open for this to work.

Another option would be to rewrite the SQL of the query feeding the subreport before you
get to the DoCmd.OpenReport call in the button's click event. To do that you will have to
concatenate the SQL of the query together in your code and then apply it to the query. To
apply it to the query you would use:

CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

Where strSQL is a string variable that has the query string that you have concatenated
together.

--
Wayne Morgan

Well, for starters, the new source would be sent to Me.RecordSource. You can't change the
RecordSource of a report unless it is open. What are you wanting to do with this change.
There are ways of doing this that might be simpler.

I've got a form. The user is goint to type in start date, end date and a
textcode.

When the user clicks show report, the report containing the sub-report
will be opened with this code:

stDocName = "Service_pr_shop_en"
stLinkCriteria = "[ID2] >= #" & date1 & "# AND [ID2] <= #" & date2 & "#
and "
' Splitting text code
For I = LBound(StrArray) To UBound(StrArray)
stLinkCriteria = stLinkCriteria & "[textcode] Like '*" & StrArray(I) &
"*'"
If I < UBound(StrArray) Then
stLinkCriteria = stLinkCriteria & " or "
End If
Next
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria


The problem is that I don't know how to pass the criteria to the
sub-report. And it have to be passed by code because of the textcode.


Sincerely
Oystein Sund