Property of closed report

[LARiot]

Hi,

How can the Record Source property of a report (which is closed) be retrieved?

This is to do a count and run the report only if it has records.
Thanks.

 

[Remou]

You would need to open the report in design view, however, why not use DCount to check the number of records in the table, query or modified SQL statement?

 

[LARiot]

That's exactly what I'm doing. But I don't want to hard code the report's record source in the VBA because the user has to first choose a report from the combo box.

There has to be a way to get the report's Record Source from VBA without opening the report first.

 

[Remou]

You can keep the SQL in a table.

 

[LARiot]

Thanks Remou,

That would make it too complicated and require too much maintenance.

If there isn't a simple way to check a closed report property I might just have VBA open it in Design View then close it. Though it doesn't make for well written code.

Is there a way to kill the report right from the start if the record count is zero?

 

[Remou]

You can use the On No Data event of the report.

 

[LARiot]

Thanks Remou,

I used

Private Sub Report_NoData(Cancel As Integer)

    DoCmd.Close acReport, "Canada"
    
End Sub

but it returns

Run-time error '2585':

This action cannot be carried out while processing a form or report event.

Is there another way to close the report? Also, is there way to close it without specifing a name?

Thanks, I hope I'm not asking for too much.

 

[Remou]

Don't close, Cancel:

Private Sub Report_NoData(Cancel As Integer)

    cancel=true
    
End Sub

You will get a trappable error, 2501, in your calling code.

 

[LARiot]

Thanks Remou, that did it.