Confusing result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get the following to run:
Dim Directors As ADODB.Recordset

Set Directors = New ADODB.Recordset

Directors.Open "tbl_Directors", CurrentProject.Connection, adOpenStatic

Do Until Directors.EOF

[Forms]![frm_SendReport].[Director].Value = Directors![Employee Full Name]
[Forms]![frm_SendReport].[Jeff].Value = Directors![Jeff]

DoCmd.SendObject acSendReport, "rpt_ApplicationAccess", acFormatTXT,
[Forms]![frm_SendReport].[Jeff], , , "SOX 404 Appropriate Application Access
Review", "Please review the attached report, electronically sign and return
to sender.", True

Directors.MoveNext

Loop

The report is based on a query with criteria in the field [employee full
name]"

[Forms]![frm_sendreport].[Director], which is the combo box that selects the
Director from tbl_Directors.

Two things happen: Some directors get empty reports so in the noData event
of the report I entered cancel = true, this cancels the sequence entirely
after the first report is sent.

I then look in the Directors table and a new empty record has been added.

Can anyone help with this? What am I missing? Thank you.
 
Evidently because my form had as it's record source the table I was using as
the record set, this is what was creating the new records everytime the code
ran. I converted the for to unbound and am using unbound text boxes to hold
the email address and name of director. Now the mailing works.

I am still stuck with the "Cancel = true" on the report's on no data event.
With this in place the loop ends with an error after the first email.

How can I get the loop to skip empty reports if this is not the correct way?

Thanks.
 
Back
Top