Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Print If... (2003)

    I have some code that prints all 20 reports and sends all 20 vendors an email of the report(.snp). I am looking for a way to only email the reports that have something on them. Can someone help me? I think this could be done by counting the number of records in the subsequent query. Am I heading down the right path?

    thanks,
    jackal

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

    Re: Print If... (2003)

    Reports have an On No Data event that occurs if the record source has no records. You can cancel the report in this event:

    Private Sub Report_NoData(Cancel As Integer)
    Cancel = True
    End Sub

    Canceling a report will cause error 2501 in the code that opens the report, so you must add error handling to ignore this error:

    ...
    On Error GoTo ErrHandler

    DoCmd.OpenReport ...
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Canceled - ignore
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Email If... (2003)

    I think i subject should have been "Email If..."
    Here is a portion of my code:
    <pre>Private Sub Vendor()
    Dim rpt As Recordset
    'Ashgrove-Cement
    DoCmd.SendObject acReport, "rptVCmt-AshGrove", acFormatSNP, "jlowry@lhlacy.com",,, _
    "L.H. Lacy Material Schedule", "Here is this weeks Material Schedule.", False

    'Boral-Flyash
    DoCmd.SendObject acReport, "rpt-V-FlyAsh-Boral", acFormatSNP, "david.ditta@boral.com",,, _
    "L.H. Lacy Material Schedule", "Here is this weeks Material Schedule.", False
    ...
    End Sub</pre>


    I do this for 20 reports. I tried to add the On No Data event to some reports and it still sent them anyway. Should this way work?
    jackal

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

    Re: Email If... (2003)

    If the report contains no records, the SendObject action should also be canceled by the On No Data event procedure. If that doesn't work, either your report does contain records, or you made a mistake somewhere. What happens if you open one of the "problem" reports in preview mode?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email If... (2003)

    Ok, sorry i think i closed the vba window and did not save changes. Answer to your question: when i try to preview the report nothing happens.

    This works on the first DoCmd, how might I get it to return to the next DoCmd line?

    thanks,
    jackla

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

    Re: Email If... (2003)

    Change the error handling section in the procedure that sends the reports as follows:

    ErrHandler:
    If Err = 2501 Then
    ' Canceled - ignore and continue
    Resume Next
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email If... (2003)

    I got it. I added Resume Next to the error handler.

    thanks for the help.
    jackal.

Posting Permissions

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