Awhile back, the script below (I changed a few fields names only) was provided to prevent pdf files from being generated if a record did not exist. My problem is that the type of record do not exist for each Provider listed. However, the problem is that it created a file for each Provider even if that Provider does not a record listed of the specific type that the query attached to the report ask for. An example of database is attached.
Private Sub Command3_Click()
Dim rst As DAO.Recordset
Dim strReportName, strFileName As String
Dim strSQL As String
Dim rpt As Report
On Error GoTo ProcError
strReportName = "rptMasterReportCB"
DoCmd.OpenReport strReportName, acViewPreview, , , acHidden
Set rpt = Reports(strReportName)
strSQL = "SELECT tluFundsImport.ProvID, tblProviderAccount.Provide
rName, " _
& "tluFundsImport.DraftDate " _
& "FROM tblProviderAccount INNER JOIN tluFundsImport " _
& "ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
& "GROUP BY tluFundsImport.ProvID, tblProviderAccount.Provide
rName, " _
Set rst = CurrentDb.OpenRecordset(st
Do While Not rst.EOF
strFileName = "C:\Users\Eipc\Desktop\Com
bined " _
& rst!ProviderName & " " & Format(rst!DraftDate, "mm.dd.yyyy") & ".pdf"
rpt.Filter = "ProvID = " & rst!ProvID
rpt.FilterOn = True
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName
DoCmd.Close acReport, strReportName
If Not rst Is Nothing Then
Set rst = Nothing
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error exporting file"
Debug.Print "Error exporting file", Err.Number, Err.Description