Select when to run report in code (2003 SP3)

Topic

New Reply

I have a series of 36 reports that are run by getting the names of the reports from a table and looped through in VBA code. It works fine, until or unless one of the reports’ queries returns no records.
So I added a couple of fields to the above mentioned report table (name of query as a text field and ‘Run’ as a Yes/No field). I attempted to use the following code to test the query for each report, and if there are records, it set the ‘Run’ to True, and if there are no records set it to False.

Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim rstReports As DAO.Recordset

Set db = CurrentDb
strSQL = “SELECT rptQueryName, Run ” & _
“FROM tlkpExportFields;”

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rst.EOF
With rst
.Edit
Set rstReports = db.OpenRecordset(!rptQueryname, dbOpenSnapshot)
If rstReports.EOF Then
rst!Run = False
Else
rst!Run = True
End If
.Update
.MoveNext
End With
Loop
rstReports.close
set rstReports = Nothing
rst.Close
Set rst = Nothing

Caveats.
The queries for the reports use a date from a control on the form as criteria. I think that is causing the error, but I don’t know a workaround. Is there an easier way to handle batching the reports with a way to address the ones with no records? If not, how would I get the above to run?

Reply | Quote

Replies

You haven’t told us what happens if a report has no data. I assume that you have code in the report’s On No Data event. This causes error 2501 if you use DoCmd.OpenReport. You can get around it like this (I have used strReportName as field containing the report name):

Sub Something()
Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim rstReports As DAO.Recordset

On Error GoTo ErrHandler

Set db = CurrentDb
strSQL = “SELECT strReportName FROM tlkpExportFields”
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rst.EOF
DoCmd.OpenReport rst!strReportName
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
If Err = 2501 Then
Resume Next
Else
Resume ExitHandler
End If
End Sub

If that doesn’t do what you want, you’ll have to provide more info.

Reply | Quote