T
Tyger
Guest
I have generated a program on Access 97 that will generate reports and email them out. I use 2 tables with one with the data that goes into the report and the other that has their email addresses and is put in the email address in the SendObject Command.
My problem is that the table of emails list all the people I generally send out information. The data in the first table does not go out to everybody on that list. So I get blank page reports with the headings. I don't want a report generated if there is no data for that particular person. Is there a way to not generate the reports with no data. I have included my code if it will help. Thanks!
Private Sub Command11_Click()
Dim dbs As Database
Dim tdf As TableDef
Dim rstAgency As Recordset
Dim strWhere As String
Dim strReportName As String
Dim strFileName As String
Dim rstTestForRecords As Recordset
Dim strWhereTest As String
' Return reference to current database
Set dbs = CurrentDb
' Return reference to Agency table
Set tdf = dbs.TableDefs!POC
' Open the AgencyList table containing agencies
Set rstAgency = dbs.OpenRecordset("POC")
' Loop through AgencyList table to get the agency _
parameter for the report and path to send report
With rstAgency
.MoveFirst
Do Until .EOF
strReportName = "Certificate_Listing"
strWhere = "[AgencyName]=""" & rstAgency![AgencyName] & """"
strFileName = "F:\FDLEfiles\" & rstAgency![AgencyName] & ".rtf"
strWhereTest = "Select * From POC Where " & strWhere
Set rstTestForRecords = dbs.OpenRecordset(strWhereTest)
If rstTestForRecords.BOF And rstTestForRecords.EOF Then
Else
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
DoCmd.SendObject acSendReport, strReportName, acFormatRTF, _
rstAgency![POC Email].Value, , , "FDLE PKI Certificates Issued", " The attachecd report is a listing of all the certificates issued at the " & rstAgency![AgencyName] & " as of " & Date & ".", False
DoCmd.OutputTo acOutputReport, strReportName, "RichTextFormat(*.rtf)", _
strFileName
DoCmd.Close
End If
.MoveNext
Loop
.Close
End With
End Sub
My problem is that the table of emails list all the people I generally send out information. The data in the first table does not go out to everybody on that list. So I get blank page reports with the headings. I don't want a report generated if there is no data for that particular person. Is there a way to not generate the reports with no data. I have included my code if it will help. Thanks!
Private Sub Command11_Click()
Dim dbs As Database
Dim tdf As TableDef
Dim rstAgency As Recordset
Dim strWhere As String
Dim strReportName As String
Dim strFileName As String
Dim rstTestForRecords As Recordset
Dim strWhereTest As String
' Return reference to current database
Set dbs = CurrentDb
' Return reference to Agency table
Set tdf = dbs.TableDefs!POC
' Open the AgencyList table containing agencies
Set rstAgency = dbs.OpenRecordset("POC")
' Loop through AgencyList table to get the agency _
parameter for the report and path to send report
With rstAgency
.MoveFirst
Do Until .EOF
strReportName = "Certificate_Listing"
strWhere = "[AgencyName]=""" & rstAgency![AgencyName] & """"
strFileName = "F:\FDLEfiles\" & rstAgency![AgencyName] & ".rtf"
strWhereTest = "Select * From POC Where " & strWhere
Set rstTestForRecords = dbs.OpenRecordset(strWhereTest)
If rstTestForRecords.BOF And rstTestForRecords.EOF Then
Else
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
DoCmd.SendObject acSendReport, strReportName, acFormatRTF, _
rstAgency![POC Email].Value, , , "FDLE PKI Certificates Issued", " The attachecd report is a listing of all the certificates issued at the " & rstAgency![AgencyName] & " as of " & Date & ".", False
DoCmd.OutputTo acOutputReport, strReportName, "RichTextFormat(*.rtf)", _
strFileName
DoCmd.Close
End If
.MoveNext
Loop
.Close
End With
End Sub
