Hi there,

I have a report. This report contains a number of sub report controls. I need to modify the query that is run to populate the sub report control.

When I select the sub report control on the report it looks like I have the SourceObject filed I can modify so I tried the following. Where

[Hospital - Main Report] is the name of the report
[Hospital - Top10lens] is the sub report within the report.

When I load the report I get the following error

Run-time error '2465'
Application-defined or object-defined error.

NOTE: There is no other VBA code in this report.

I'm guessing it is the way I'm trying to manipulate the SourceObject or Record Source field.

Private Sub Report_Load()

Reports![Hospital - Main Report]![Hospital - Top10lens].Report.SourceObject = "SELECT TOP 10 lens_type, RECORDCOUNT" & _
" FROM (SELECT * from (select count(*) AS RECORDCOUNT, patient_appointment_details.lens_type" & _
" FROM patient_appointment_details, patient_appointments where lens_type <> """ & _
" AND patient_appointments.appointment_id = patient_appointment_details.appointment_id" & _
" AND patient_appointments.appointment_date >= #04/1/2010#" & _
" AND patient_appointments.appointmentdate <= #06/30/2010#" & _
" GROUP BY patient_appointment_details.lens_type)" & _
" ORDER BY RECORDCOUNT DESC)"
End Sub

SourceObject is looking for a name of a SUB REPORT, not a SQL string. You want RECORDSOURCE instead to assign the SQL to it.

Hi there,

Thanks for the info and certainly that fixes that issue. However the report does not do what I want and am wondering if the way I have structured the report is wrong.

I have the following

A Query called Hospital - Top10lens

I have created a report called Hospital - Top10lens where the RecordSource is set to Hospital - Top10lens (I.E. the query I defined earlier)

I then created a report called Hospital - Main Report.

This contains a subreport where the SourceObject is set to Report.Hospital - Top10lens

In the Form Load I have changed the code to read

Reports![Hospital - Main Report]![Hospital - Top10lens].Report.RecordSource = "SELECT TOP 5 lens_type........

However, when the report is run I See the top 10 results from the original query being populated rather than being overriden by the query in the Form Load.

I suspect it is the combination of
Reports![Hospital - Main Report]![Hospital - Top10lens].Report.RecordSource but not quite sure what.

Any help would be appreciated

Thanks