RecordSource OnOpen

[Patrik]

I am changing the recorsource in the onopen event of my report simply
using this line :

RecordSource = "SELECT * FROM tbl_Temp_Impres sionQuestionnai re WHERE
Num_Questionnai re = 19"

However how could I pass a parameter defined in another sub to this
onopen code to replace 19 with the number chosen by my user.

Or WHEN can I define the recordsource other than in the onopen event.

I would like to define the recordsource based on a variable in my sub
then open (??) the report.

Thank you

[pietlinden]

you could set the recordsource to the result of a function that
receives the Num_Questionnai re value...

Function MyRowsource (byval lngQuestionID As Long) As String
MyRowsource = "SELECT * FROM tbl_Temp_Impres sionQuestionnai re WHERE
Num_Questionnai re =" & lngQuestionID
End Function

[pietlinden]

Duh... it's a *report*... forgetting how to read.

Pass the filter in the Open event of the report.

Just Num_Questionnai re = <something>

.... could be Forms!MyOpenFor m!MyTextControl

or some such...

See OpenReport in the help - it gives examples

[Jana]

Patrik:

Is the '19' the only part of your report's recordsource that changes?
If that's the case, and you're opening the report from within another
sub, use the built in feature of the DoCmd.OpenRepor t method to limit
the records it returns in the report. Here's what you can do:

In Design View of the report, set the report's recordsource to "SELECT
* FROM tbl_Temp_Impres sionQuestionnai re" which will be a completely
unfiltered report, and save it. Be sure to comment out the code that
affects the recordsource in your OnOpen event of the report.

In the sub where you're assigning the parameter and opening the report,
do this:

Dim strCriteria as String
strCriteria = "Num_Questionna ire = " & Whatever#YouWan tOnReport

Find the line that says DoCmd.OpenRepor t "YourReportName " and replace
it with:
DoCmd.OpenRepor t "YourReportName ", , , strCriteria

Note that the strCriteria is basically your "WHERE" clause of the SQL,
without the word "WHERE", so you can build this string into whatever
criteria you want. Remember that date criteria will need to be wrapped
with # signs and text with single quotes. Numerical parameters won't
need any wrapping.

HTH,

Jana