By Passing Reports with no data

  • Thread starter Thread starter Del
  • Start date Start date
D

Del

I have a series of query driven reports that are run from the on click
command of a button on a form. These reports are not opened or printed
but saved in a folder on a network share. Once the reports have been
created an email is send to specific people letting them know that new
reports have been created. Below is the code I am using so far.

This code works fine other than it creates a blank report if there's
no data to be displayed.

I am looking for a way to run the reports but ONLY save reports if
they actually have data.

==CODE =============================


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

If IsNull(Me.Qty) Or IsNull(Me.StatusEnteredBy) Then
MsgBox "Nothing To Print. Please Complete The Form", vbOKOnly +
vbExclamation
Qty.SetFocus
Cancel = -1
Else

'Create and Save reports to the Repair Center, WIP Notice folder
on \\dbdm2\mogpublic
DoCmd.OutputTo acOutputReport,
"rptWIPNotice100Series_LowerMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice100Series_LowerMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice200Series_UpperMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice200Series_UpperMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice300Series_AkronGoodwill", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice300Series_AkronGoodwill" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice400Series_WoosterGoodwill", acFormatSNP, "\
\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice400Series_WoosterGoodwill" & Format([ReqDate],
"mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport, "rptWIPNotice500Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice500Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice600Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice600Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice700Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice700Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice800Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice800Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice900Series_ReturnToWIP", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice900Series_ReturnToWIP" &
Format([ReqDate], "mmddyyyy") & ".snp", False

'Run Macro to create and email WIP Notice reports
'DoCmd.RunMacro "mcrCreateWIPNoticeReports"

'set the mail variables
strReciepient = "email address"
strSubject = "WIP Notice"
strMessageBody = "New GSL WIP Notice Reports have been created
and are currently availabel on the MOGPUBLIC drive (\\dbdm2\mogpublic
\RepairCenter\WIPNotice). These reports show Part Numbers that were
picked from WIP and sent to Material Handling."
'send the report as a snapshot
DoCmd.SendObject , , , strReciepient, , , strSubject,
strMessageBody, False

End If
Dim stLinkCriteria As String

DoCmd.OpenForm "frmMainMenu", , , stLinkCriteria
DoCmd.Close acForm, "frmUpdateWIPRequest"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
 
Reports have a NoData event associated with them. In that event, just set
Cancel = True. Unfortunately, this will generate an error in your code, so
you need to use something like:

On Error Resume Next
Docmd.Outputto acOutputReport, "ReportName1", ...
If Err.Number = 0 or Err.number = 2501 then
'do nothing
else
'Goto ErrorHandler
endif
Docmd.Outputto acOutputReport, "ReportName2", ...
If Err.Number ...

This will handle errors other than the 2501 (The OutputTo action was
cancelled) error. Don't forget to turn your error handling back on after you
have completed the list of OutputTo statements.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Del said:
I have a series of query driven reports that are run from the on click
command of a button on a form. These reports are not opened or printed
but saved in a folder on a network share. Once the reports have been
created an email is send to specific people letting them know that new
reports have been created. Below is the code I am using so far.

This code works fine other than it creates a blank report if there's
no data to be displayed.

I am looking for a way to run the reports but ONLY save reports if
they actually have data.

==CODE =============================


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

If IsNull(Me.Qty) Or IsNull(Me.StatusEnteredBy) Then
MsgBox "Nothing To Print. Please Complete The Form", vbOKOnly +
vbExclamation
Qty.SetFocus
Cancel = -1
Else

'Create and Save reports to the Repair Center, WIP Notice folder
on \\dbdm2\mogpublic
DoCmd.OutputTo acOutputReport,
"rptWIPNotice100Series_LowerMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice100Series_LowerMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice200Series_UpperMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice200Series_UpperMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice300Series_AkronGoodwill", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice300Series_AkronGoodwill" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice400Series_WoosterGoodwill", acFormatSNP, "\
\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice400Series_WoosterGoodwill" & Format([ReqDate],
"mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport, "rptWIPNotice500Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice500Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice600Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice600Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice700Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice700Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice800Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice800Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice900Series_ReturnToWIP", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice900Series_ReturnToWIP" &
Format([ReqDate], "mmddyyyy") & ".snp", False

'Run Macro to create and email WIP Notice reports
'DoCmd.RunMacro "mcrCreateWIPNoticeReports"

'set the mail variables
strReciepient = "email address"
strSubject = "WIP Notice"
strMessageBody = "New GSL WIP Notice Reports have been created
and are currently availabel on the MOGPUBLIC drive (\\dbdm2\mogpublic
\RepairCenter\WIPNotice). These reports show Part Numbers that were
picked from WIP and sent to Material Handling."
'send the report as a snapshot
DoCmd.SendObject , , , strReciepient, , , strSubject,
strMessageBody, False

End If
Dim stLinkCriteria As String

DoCmd.OpenForm "frmMainMenu", , , stLinkCriteria
DoCmd.Close acForm, "frmUpdateWIPRequest"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
Click to expand...
 
Hi,
When writing VBA code I've used the following approach to avoid producing
reports with zero records:
Dim IntX as integer
IntX = DCount ("FieldName","SourceQueryName")
If IntX = 0 then
msgbox "No records therefore no report"
Exit sub 'or go to your exit routine
Else
'produce the report....
End if
' rest of code
Rick



Dale Fye said:
Reports have a NoData event associated with them. In that event, just set
Cancel = True. Unfortunately, this will generate an error in your code, so
you need to use something like:

On Error Resume Next
Docmd.Outputto acOutputReport, "ReportName1", ...
If Err.Number = 0 or Err.number = 2501 then
'do nothing
else
'Goto ErrorHandler
endif
Docmd.Outputto acOutputReport, "ReportName2", ...
If Err.Number ...

This will handle errors other than the 2501 (The OutputTo action was
cancelled) error. Don't forget to turn your error handling back on after you
have completed the list of OutputTo statements.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Del said:
I have a series of query driven reports that are run from the on click
command of a button on a form. These reports are not opened or printed
but saved in a folder on a network share. Once the reports have been
created an email is send to specific people letting them know that new
reports have been created. Below is the code I am using so far.

This code works fine other than it creates a blank report if there's
no data to be displayed.

I am looking for a way to run the reports but ONLY save reports if
they actually have data.

==CODE =============================


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

If IsNull(Me.Qty) Or IsNull(Me.StatusEnteredBy) Then
MsgBox "Nothing To Print. Please Complete The Form", vbOKOnly +
vbExclamation
Qty.SetFocus
Cancel = -1
Else

'Create and Save reports to the Repair Center, WIP Notice folder
on \\dbdm2\mogpublic
DoCmd.OutputTo acOutputReport,
"rptWIPNotice100Series_LowerMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice100Series_LowerMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice200Series_UpperMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice200Series_UpperMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice300Series_AkronGoodwill", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice300Series_AkronGoodwill" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice400Series_WoosterGoodwill", acFormatSNP, "\
\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice400Series_WoosterGoodwill" & Format([ReqDate],
"mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport, "rptWIPNotice500Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice500Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice600Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice600Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice700Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice700Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice800Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice800Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice900Series_ReturnToWIP", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice900Series_ReturnToWIP" &
Format([ReqDate], "mmddyyyy") & ".snp", False

'Run Macro to create and email WIP Notice reports
'DoCmd.RunMacro "mcrCreateWIPNoticeReports"

'set the mail variables
strReciepient = "email address"
strSubject = "WIP Notice"
strMessageBody = "New GSL WIP Notice Reports have been created
and are currently availabel on the MOGPUBLIC drive (\\dbdm2\mogpublic
\RepairCenter\WIPNotice). These reports show Part Numbers that were
picked from WIP and sent to Material Handling."
'send the report as a snapshot
DoCmd.SendObject , , , strReciepient, , , strSubject,
strMessageBody, False

End If
Dim stLinkCriteria As String

DoCmd.OpenForm "frmMainMenu", , , stLinkCriteria
DoCmd.Close acForm, "frmUpdateWIPRequest"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
Click to expand...
Click to expand...
 
Rick,

The down side of this is that Jet has to count the number of records in your
query first, so if there are records, it takes longer to generate the
report. You could speed this up using DLookup instead of DCount, since
DLookup will return the first record, if there is one, and NULL if there
isn't. Then you could just test:

IF ISNULL(DLookup("FieldName", "SourceQueryName")) Then
msgbox "No records"
Exit sub
else
'produce report
End if

Actually, I still like my method better, because then, all you need to do is
put this in a function or subroutine, pass it the name of the report, and
let it do the processing. When you have multiple reports like this, it
would get old repeating the previous code for each report. Additionally,
you have to actually know the name of a field for each query.

Dale

SoCal Rick said:
Hi,
When writing VBA code I've used the following approach to avoid producing
reports with zero records:
Dim IntX as integer
IntX = DCount ("FieldName","SourceQueryName")
If IntX = 0 then
msgbox "No records therefore no report"
Exit sub 'or go to your exit routine
Else
'produce the report....
End if
' rest of code
Rick



Dale Fye said:
Reports have a NoData event associated with them. In that event, just
set
Cancel = True. Unfortunately, this will generate an error in your code,
so
you need to use something like:

On Error Resume Next
Docmd.Outputto acOutputReport, "ReportName1", ...
If Err.Number = 0 or Err.number = 2501 then
'do nothing
else
'Goto ErrorHandler
endif
Docmd.Outputto acOutputReport, "ReportName2", ...
If Err.Number ...

This will handle errors other than the 2501 (The OutputTo action was
cancelled) error. Don't forget to turn your error handling back on after
you
have completed the list of OutputTo statements.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Del said:
I have a series of query driven reports that are run from the on click
command of a button on a form. These reports are not opened or printed
but saved in a folder on a network share. Once the reports have been
created an email is send to specific people letting them know that new
reports have been created. Below is the code I am using so far.

This code works fine other than it creates a blank report if there's
no data to be displayed.

I am looking for a way to run the reports but ONLY save reports if
they actually have data.

==CODE =============================


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

If IsNull(Me.Qty) Or IsNull(Me.StatusEnteredBy) Then
MsgBox "Nothing To Print. Please Complete The Form", vbOKOnly +
vbExclamation
Qty.SetFocus
Cancel = -1
Else

'Create and Save reports to the Repair Center, WIP Notice folder
on \\dbdm2\mogpublic
DoCmd.OutputTo acOutputReport,
"rptWIPNotice100Series_LowerMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice100Series_LowerMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice200Series_UpperMezz", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice200Series_UpperMezz" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice300Series_AkronGoodwill", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice300Series_AkronGoodwill" &
Format([ReqDate], "mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice400Series_WoosterGoodwill", acFormatSNP, "\
\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice400Series_WoosterGoodwill" & Format([ReqDate],
"mmddyyyy") & ".snp", False
DoCmd.OutputTo acOutputReport, "rptWIPNotice500Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice500Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice600Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice600Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice700Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice700Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport, "rptWIPNotice800Series_",
acFormatSNP, "\\dbdm2\mogpublic\RepairCenter\WIPNotice
\rptWIPNotice800Series_" & Format([ReqDate], "mmddyyyy") & ".snp",
False
DoCmd.OutputTo acOutputReport,
"rptWIPNotice900Series_ReturnToWIP", acFormatSNP, "\\dbdm2\mogpublic
\RepairCenter\WIPNotice\rptWIPNotice900Series_ReturnToWIP" &
Format([ReqDate], "mmddyyyy") & ".snp", False

'Run Macro to create and email WIP Notice reports
'DoCmd.RunMacro "mcrCreateWIPNoticeReports"

'set the mail variables
strReciepient = "email address"
strSubject = "WIP Notice"
strMessageBody = "New GSL WIP Notice Reports have been created
and are currently availabel on the MOGPUBLIC drive (\\dbdm2\mogpublic
\RepairCenter\WIPNotice). These reports show Part Numbers that were
picked from WIP and sent to Material Handling."
'send the report as a snapshot
DoCmd.SendObject , , , strReciepient, , , strSubject,
strMessageBody, False

End If
Dim stLinkCriteria As String

DoCmd.OpenForm "frmMainMenu", , , stLinkCriteria
DoCmd.Close acForm, "frmUpdateWIPRequest"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top