Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Select when to run report in code (2003 SP3)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select when to run report in code (2003 SP3)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •