Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Successive Reports (Access 97)

    I have a form that calls a series of three reports (actually, a series of letters) with this code:
    DoCmd.OpenReport "rptPassLetter", acViewPreview
    DoCmd.OpenReport "rptPassLetForStudents", acViewPreview
    DoCmd.OpenReport "rptFailLetter", acViewPreview
    If any one of these reports has no data, I have suppressed it with this code:
    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no letters to print from this date."
    Cancel = -1
    End Sub
    Unfortunately,it is possible to have no pass letters and if any but the last report have no data, the whole process stops at the first one without data. Is there any way to stop just the report with no data from previewing, and continue on with the rest? I just don't know how to get back to the calling sub and continue. Any help is greatly appreciated.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calling Successive Reports (Access 97)

    One solution would be to test for the number of letters in each category before opening the report, and only open if there are any.
    I would have queries that selected people due for each type of letter (which you may well have already.)
    Otherwise one general query, and add a criteria to each dcount

    if dcount("[Personid]","qryPassLetter") >0 then
    DoCmd.OpenReport "rptPassLetter", acViewPreview
    end if
    if dcount("[Personid]","qryPassLetforstudents") >0 then
    DoCmd.OpenReport "rptPassLetforstudents", acViewPreview
    end if
    etc
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calling Successive Reports (Access 97)

    John's suggestion is a way to go, and here is another.

    The NoData event of the report is the way to go to bypass the report if there is no data, however, you will stop the report with the MsgBox. I would delete the MsgBox.
    I would also set Cancel = True rather than -1.
    I would put a test in the form in the Error trap to exclude any Err = 2501 (this is where you cancel a report)

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling Successive Reports (Access 97)

    If you wanted to get exotic, you could also declare a module level report object variable using the WithEvents keyword and trap the report events like NoData for each report successively in the calling class module.
    Charlotte

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

    Re: Calling Successive Reports (Access 97)

    To elaborate on patt's suggestion: your code could look like this:

    Sub PreviewReports()

    ' Trap errors
    On Error Goto Err_Handler

    ' Preview the reports
    DoCmd.OpenReport "rptPassLetter", acViewPreview
    DoCmd.OpenReport "rptPassLetForStudents", acViewPreview
    DoCmd.OpenReport "rptFailLetter", acViewPreview

    ' Get out now
    Exit Sub

    Err_Handler:

    If Err = 2501 Then
    ' Report was canceled; ignore error and continue with next report.
    Resume Next
    Else
    ' Inform user of all other errors
    MsgBox Err.Description, vbExclamation
    End If

    End Sub

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Successive Reports (Access 97)

    I'd like to thank all of you for your help. My original code looked like this:
    Private Sub cmdPrLets_Click()
    On Error GoTo Err_cmdPrLets_Click
    'open each report in preview
    DoCmd.OpenReport "rptPassLetter", acViewPreview
    DoCmd.OpenReport "rptPassLetForStudents", acViewPreview
    DoCmd.OpenReport "rptFailLetter", acViewPreview
    'error handlers
    Exit_cmdPrLets_Click:
    Exit Sub

    Err_cmdPrLets_Click:
    ' if there is no data for letters
    If Err = 2501 Then
    Resume Exit_cmdPrLets_Click
    Else
    'handle other errors
    MsgBox Err.Description
    Resume Exit_cmdPrLets_Click
    End If
    End Sub

    So just adding "Resume Next" and taking out my Resume statement made the code work just like I needed it to. Thanks so much. I was unfamiliar with the "Resume Next" statement.

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Successive Reports (Access 97)

    Could I ask why you suggested changing the cancel = true instead of -1? Why do you prefer it? I have no idea what the difference is. Thanks in advance for the info.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calling Successive Reports (Access 97)

    I guess I could ask why you prefer -1 instead of True. There is no difference.
    This is a habit I have gotten into over the years, the main reason being, if they ever change the value of True from -1 then a lot of code out there will start to fail. Readibilty is another reason to use True instead of -1.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    New Lounger
    Join Date
    Jul 2002
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Successive Reports (Access 97)

    Good point! Come to think of it, I am always a bit confused by "-1" and I always have to stop and think. I'm just a patzer when it comes to code so I'm always curious when something like this comes up and the reasoning behind it. I guess this comes from being mostly self-taught. Thanks for the insight, it is much appreciated. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calling Successive Reports (Access 97)

    Pat (patt for short) makes an excellent point - in fact SQL Server and Access ADP project use a +1 as True, and if you use -1 as a criteria in a query, you don't get the expected result.
    Wendell

Posting Permissions

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