How to modify sub report record source

[EddieN1]

I have a main report with up to three sub reports depending on what check boxes the user selects on the form that calls the report. There is always a small amount of data on the main report. If the operator checks a check box for sub report 1, 2, or 3, then they appear as well. I have put the test for the check box in the sub report's underlying record source. However, this technique causes the sub report to open, read all the data from the record source query, and finally reject them all since the check box isn't checked. This works but makes the report run very slow. I would like to modify the record sources for the sub reports based on the check boxes before the sub reports open, so that if the check box for a sub report isn't checked, then the record source for the sub report is blank. What event should I use to modify the sub report's record source and what syntax should I use?

Thanks, Eddie

[pbaldy]

I'd start with the open event of the subreport. The code would look like:

Me.RecordSource = "Whatever"

Where Whatever is either an SQL string or the name of a saved query.

[EddieN1]

pbaldy, Thanks for the response, however, that's not quite what I meant. I need to change the record source for the sub report dynamically. If the operator doesn't check the check box for that sub report, I want its record source to be changed to null. I think that when the main report opens, it should be able to do that, but under which event?

[pbaldy]

I think I understood and would use the event mentioned. If the checkbox isn't checked, set it to a zls: ""

[EddieN1]

Okay, guys, I finally found out how to do this. First, the record source may be modified in the sub report's Open event. However, the trick is to do it only the first time through. Here's how...

Static CallCount as integer
If CallCount = zero then
Me.RecordSource = "... whatever you want "
Else CallCount = 1
End If

Thanks for eveyone's comments and suggestions.

Eddie