Changing the Record Source of a subreport 1

[rbowes]

I have an unbound main report that has 6 subreports on it.&nbsp;&nbsp;My report criteria form allows the user to determine many different kinds of ways to view the data that they want.&nbsp;&nbsp;And because the queries for each are totals queries, filtering does me no good - so I have to change the record sources of subreports (through VBA) with a SQL statement.<br><br>Here's the problem, from within the report on the OnOpen event - or even in a standard module - I create the SQL statement to what I want then try to set the recordsource for the appropriate sub report and I get an invalid reference.&nbsp;&nbsp;The sub report is in a group header that I have defined <br><br>Dim strSQL as string<br><br>strSQL = &quot;SELECT somestatement&quot;<br>Reports![rpMainReportName]![rptAppropriateSUb].Report.RecordSource = strSQL<br><br>From within the report it looks like this:<br>Dim strSQL as string<br><br>strSQL = &quot;SELECT somestatement&quot;<br>Me![rpMainReportName]![rptAppropriateSUb].Report.RecordSource = strSQL<br><br><br>I know the SQL statement works - but it seems that ACCESS 2K will no allow me to get to the subreport.&nbsp;&nbsp;I have thought of changing the recordsource on the OnFormat event for the report header but haven't had the chance to try it. Does anyone have a possible solution for this.<br>

 

[DougP]

We need two things the exact Error message word for word, and your exact SQL statement.<br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[rbowes]

It'll take me a few days to get back to you about that, but I know that the SQL statement works - it worked in a query on its own.&nbsp;&nbsp;I also tried to set the record source to &quot;&quot; and it still wouldn't come up with another error message.&nbsp;&nbsp;I'll be back with the message later.

 

[rbowes]

the exact message that I am getting is &quot;You entered an expression that has an invalid reference to the property Form/Report&quot;&nbsp;&nbsp;the exact SQL statement is<br><br>strSQL = &quot;SELECT qryCaseLoadKidsByDate.ServiceCode, qryCaseLoadFamilyByDateTotal.[Number of Families], qryCaseLoadKidsByDate.[Children Served]&quot;<br>strSQL = strSQL & &quot; FROM qryCaseLoadKidsByDate INNER JOIN qryCaseLoadFamilyByDateTotal ON qryCaseLoadKidsByDate.ServiceCode = qryCaseLoadFamilyByDateTotal.ServiceCode;&quot;<br><br>What can be going on here?&nbsp;&nbsp;I have no idea why the report cannot be seen by Access and when I put the SQL statement on the subreport's OnOpen event - Access completely overlooks the code.<br><br>Thanks for your help.<br><br>Bob