Need help setting Recordsource of subreport

[BJS]

I am having trouble setting the recordsource of my subreport using the On_Open event of the Main report. Here is the code I am using to set the recordsource:

[Reports]![rptMainReport]![srptSubReport].RecordSource = "SELECT * FROM tblEmployee WHERE Index = " & intIndex & _
" ORDER BY tbl_LTDReturnToWorkRecord.WeekEndingDate ASC;"

When I open the report I get error: "Object doesn't support this property or method." It doesn't seem to like how I am referencing the reports.

Please Help! Thanks!

 

[KenHigg]

[Reports]![rptMainReport]![srptSubReport].report.RecordSource = "SELECT * FROM tblEmployee WHERE Index = " & intIndex & _
" ORDER BY tbl_LTDReturnToWorkRecord.WeekEndingDate ASC;"

???
Ken

 

[BJS]

Hi Ken, I just tried your code and now I get the error:

"You entered an expression that has an invalid reference to the property Form/Report. The property may not exist or may not apply to the object you specified."

BTW....I am using Access 97.

???

 

[KenHigg]

I'm not sure I have ever changed the recordsource for a report on the fly. Are you sure you can't just set it in the properties? What would be different from one run to the next?

As far as the error, You need to make sure you are using the name of the sub report as it appears in the main report. This could be different from what you may have actually saved the sub report as...

Ken

 

[BJS]

Thank you for your input Ken. I just checked, and I am using the name of the subreport as it appears on the main report.

I need to change the recordsource on the fly, because the records for the subreport will be different depending on the Employee record that the user is running the report for.

I'm still trying various other ways; with no success yet!

 

[KenHigg]

Hum. To start with, could you build the query and save it, the just use the query name?

So it would be something like:

me!mySubreportName.report.recordsource = "MyQueryName"

This would do two things; 1. Your report should run a little faster, and 2. It should eliminate the posibility of an error in your sql string.

???
Ken

 

[BJS]

Hi Ken...I just did it the exact same way you explained, and I still get the same error. I don't know what's going on here! :-(

 

[KenHigg]

Hum...

Let's start from scratch. Where/how are you going to decide which recordset will be used in the sub report?

Ken

 

[BJS]

Here are the steps the user of the db needs to take to get the report to display the correct employee information:

1. Select Employee from the Switchboard to open the form that displays employee information.

2. On that form the user clicks a button that opens another form for data entry of employee payments through the subform on this form.

3. On this Employee Payment form, the user clicks a button to open the report that contains the subreport. The main report displays general info about the employee and the subreport shows the payment details for that employee.

The record source changes on the fly, depending on which employee was selected in the first place. I am storing the index value in a global variable.

What I am trying to do with my report is basically the same as I have done with my form that contains the subform. I was able to change the recordsource of the subform on the fly as follows:

Form_sfrmMain.RecordSource = "SELECT * FROM tbl_LTDReturnToWorkRecord WHERE Index = " & intLTDIndex & _
" ORDER BY tbl_LTDReturnToWorkRecord.WeekEndingDate ASC;"

The subform on the mainform displays the correct records. I tried the same for the report and subreport, but as you see, it doesn't work.

WHILE WRITING THIS...I HAD ONE OTHER THOUGHT, I TRIED IT AND IT WORKS:

I set the recordsource for the On_Open Event of the Subreport instead of the Main Report. Not sure why it doesn't work the same way for the report as it does for the form.

Here is the code that worked on the On_Open Event of the subreport:

Report_srptSubReport.RecordSource = "SELECT * FROM tbl_LTDReturnToWorkRecord WHERE Index = " & intLTDIndex & _
" ORDER BY tbl_LTDReturnToWorkRecord.WeekEndingDate ASC;"

Thanks so much for your help, Ken!!

 

[KenHigg]

...
I set the recordsource for the On_Open Event of the Subreport instead of the Main Report....

Thanks so much for your help, Ken!!

I thought of that as well - Glad you got it working!

Ken

 

[Rich]

A subReport opens before the main Report, that's why you can't use the OpenEvent of the main Report