Stop outputing Report if NO Data

[cwby1966]

I have VBA code that is to output 2 reports to snapshot format for emailing. When I add a cancel on the report if no data, the second report will not output even when there is data

 DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
    DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
    DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
    DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
    DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0

I am not sure what I amdoing worng, any help?
Thanks

[Scott Price]

I have VBA code that is to output 2 reports to snapshot format for emailing. When I add a cancel on the report if no data, the second report will not output even when there is data

 DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
    DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
    DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
    DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
    DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0

I am not sure what I amdoing worng, any help?
Thanks

How are you cancelling the report if no data?

Where does this code reside? Please post the entire sub-routine... (I notice you have set your warnings to false... hopefully later in this section of code you return them to true!)

Regards,
Scott

[cwby1966]

How are you cancelling the report if no data?

Where does this code reside? Please post the entire sub-routine... (I notice you have set your warnings to false... hopefully later in this section of code you return them to true!)

Regards,
Scott

Scott
The code to execte the out put is a commad buton. The Cancel event is in the "no Data" on hte report. Is this wrong? Should I be handling the Do data thig somewhere else?

Yes the warnings get set back to on at the end of the Select statement.
Thanks
Troy

[Scott Price]

Scott
The code to execte the out put is a commad buton. The Cancel event is in the "no Data" on hte report. Is this wrong? Should I be handling the Do data thig somewhere else?

Yes the warnings get set back to on at the end of the Select statement.
Thanks
Troy

To get this a little clearer in my mind, how exactly is the Cancel called? I understand that you are doing this is under the On No Data event of the report... My question: is it cancelled through an expression, code, macro?? If code, please post the code used to cancel.

Regards,
Scott

[cwby1966]

To get this a little clearer in my mind, how exactly is the Cancel called? I understand that you are doing this is under the On No Data event of the report... My question: is it cancelled through an expression, code, macro?? If code, please post the code used to cancel.

Regards,
Scott

Scott
Sorry Scott using the code below

Private Sub Report_NoData(Cancel As Integer)

On Error GoTo Report_NoDataError

   MsgBox "No records meet the report criteria; cancelling report printing"
   Cancel = True
   
Report_NoDataExit:
   Exit Sub

Report_NoDataError:
   MsgBox Err.Description
   Resume Report_NoDataExit
   
End Sub

[Scott Price]

Thanks for posting that section of code!

Could you now please post the whole procedure for your first section of code?

What I think is happening (not sure yet without seeing all the code...) is that you are calling 2 separate reports/subreports (from your first section of code they would be called MBM Time Sheet, and MBM ATTN Sheet) from within another report/function (from your second section of code this is called Report?)?

Let me know if these assumptions are correct, thanks!

Regards,
Scott

[cwby1966]

Scott Here is the full code that calls the out put. What I did find interesting is that when I had the cancel event on the "on no data" for the repots, there is code to call both of them to open either in preview or send tothe printer it worksexactly likeI want it to.
Heres the code

 If Nz(Forms![control center]![Report list]) <> "" Then
        Select Case Forms![control center]![Report list]
            Case "MBM Time Sheets"
                Call mbmtimeshts
            Case "Time Summary"
                Call timesum
            Case "Year To Date Managers Chart"
                Call ytdmgr
            Case "Vacation Calendar"
                Call VacCal
            Case "Seniority Report"
                Call senority
            Case "Absenteeism Report"
                Call attendance
            Case "Associate List"
                Call emplist
            Case "Birthday List"
                Call bday
            Case "Cost Center List"
                Call cclist
            Case "Punch Card Labels"
                Call punchcard
            Case "Associate Phone Book"
                Call phonebook
            Case "Year To Date Managers Report"
                Call ytdrpt
            Case "Online Vacation Calendar"
                Call vacchrt
            Case "Staffing Service Time Sheets"
                Call Stafftimeshts
            Case "Entry Verification"
                Call entryverify
            Case "Set Up Text File"
                Call textfile
            Case "Vacation Calender By Name"
                Call vaccalstaff
        End Select
    
   Else
      Forms![control center]![Report list].SetFocus
      Forms![control center]![Report list].Dropdown
End If
End Function
Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnings False
                If Forms![control center]![frareportmode] = 1 Then
                    DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
                    DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
                    ElseIf Forms![control center]![frareportmode] = 2 Then
                    DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
                    DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
                End If
'mbmtimesgts_err:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function

Function timesum()

        If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "Time Summary", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "Time Summary", acNormal, "", ""
        End If

End Function

Function ytdmgr()
 DoCmd.OpenForm "pvt_ytdchart", acFormPivotTable, "", "", , acNormal
End Function

Function VacCal()

         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "rpt_calander", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "rpt_calander", acNormal, "", ""
        End If

End Function
Function senority()

         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "rpt_SENORITY", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "rpt_SENORITY", acNormal, "", ""
        End If

End Function
Function attendance()

         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "RPT_Staff Attendance", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "RPT_Staff Attendance", acNormal, "", ""
        End If

End Function

Function emplist()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "Employee List", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "Employee List", acNormal, "", ""
        End If
End Function

Function bday()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "rtp_bday", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "rtp_bday", acNormal, "", ""
        End If
End Function

Function cclist()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "Cost Center List", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "Cost Center List", acNormal, "", ""
        End If
End Function

Function punchcard()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "Labels punch card print", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "Labels punch card print", acNormal, "", ""
        End If
End Function
Function phonebook()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "pHONE bOOK", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "pHONE bOOK", acNormal, "", ""
        End If
End Function
Function ytdrpt()
         If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "rpt_YTD MGR SUMMARY", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "rpt_YTD MGR SUMMARY", acNormal, "", ""
        End If
End Function
Function vacchrt()
 DoCmd.OpenForm "NEW VAC", acFormPivotTable, "", "", , acNormal
End Function
Function Stafftimeshts()
On Error GoTo STAFFTIMESHTS_ERR
        If Forms![control center]![frareportmode] = 1 Then
        Beep
        MsgBox "Are You sure You Have Selected the Correct Week of The Pay Period", vbInformation, ""
        DoCmd.OpenReport "Staff Service Time Sheet", acViewPreview, "", ""
        DoCmd.OpenReport "Staff Service Time Sheet SB", acViewPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        MsgBox "Are You sure You Have Selected the Correct Week of The Pay Period", vbInformation, ""
        DoCmd.OpenReport "Staff Service Time Sheet", acNormal, "", ""
        DoCmd.OpenReport "Staff Service Time Sheet SB", acNormal, "", ""
                End If
STAFFTIMESHTS_ERR:
MsgBox ("There are no Records for this Time Period.")
Reponse = 0
End Function

Function entryverify()
DoCmd.OpenQuery "EMP List Without Matching time summary", acNormal, acEdit
End Function
Function textfile()
    Dim strinput As String
    strinput = "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.txt"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
    DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
    DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
    DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
    DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
    Application.FollowHyperlink strinput, , True

    End Function

Function vaccalstaff()
  If Forms![control center]![frareportmode] = 1 Then
        DoCmd.OpenReport "rpt_calbystaff", acPreview, "", ""
        ElseIf Forms![control center]![frareportmode] = 2 Then
        DoCmd.OpenReport "qry_listreport", acNormal, "", ""
        End If
End Function

Thanks
Troy

[Scott Price]

Thanks again for posting the code!

Try changing your first select case statement to add another call function:

I.e after line 4 of your posted code, you would add another line that says

[CODE=vb]Call mbmattnsheet[/CODE]

Next, create the function called mbmattnsheet. It will be almost exactly the same as your other functions... This will require changing these lines of code (44 to 59 in your post)

[CODE=vb]
Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnin gs False
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM Time Sheet", acPreview, "", ""
DoCmd.OpenRepor t "MBM ATTN Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM Time Sheet", acNormal, "", ""
DoCmd.OpenRepor t "MBM ATTN Sheet", acNormal, "", ""
End If
'mbmtimesgts_er r:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function[/CODE]

To this:

[CODE=vb]Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnin gs False
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM Time Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM Time Sheet", acNormal, "", ""
End If
'mbmtimesgts_er r:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function

Function mbmattnsheet()
'On Error GoTO mbmattns_err
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM ATTN Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM ATTN Sheet", acNormal, "", ""
End If
'mbmattns_err:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function[/CODE]

I think what's happening is that when one of your conditions in the existing function is false, then it exits the whole function... Thus splitting it into two functions that are called from the same select case, will still call one even if the other is false...

Let me know if this works!

Regards,
Scott

[cwby1966]

Thanks again for posting the code!

Try changing your first select case statement to add another call function:

I.e after line 4 of your posted code, you would add another line that says

[CODE=vb]Call mbmattnsheet[/CODE]

Next, create the function called mbmattnsheet. It will be almost exactly the same as your other functions... This will require changing these lines of code (44 to 59 in your post)

[CODE=vb]
Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnin gs False
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM Time Sheet", acPreview, "", ""
DoCmd.OpenRepor t "MBM ATTN Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM Time Sheet", acNormal, "", ""
DoCmd.OpenRepor t "MBM ATTN Sheet", acNormal, "", ""
End If
'mbmtimesgts_er r:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function[/CODE]

To this:

[CODE=vb]Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnin gs False
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM Time Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM Time Sheet", acNormal, "", ""
End If
'mbmtimesgts_er r:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function

Function mbmattnsheet()
'On Error GoTO mbmattns_err
If Forms![control center]![frareportmode] = 1 Then
DoCmd.OpenRepor t "MBM ATTN Sheet", acPreview, "", ""
ElseIf Forms![control center]![frareportmode] = 2 Then
DoCmd.OpenRepor t "MBM ATTN Sheet", acNormal, "", ""
End If
'mbmattns_err:

'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0

End Function[/CODE]

I think what's happening is that when one of your conditions in the existing function is false, then it exits the whole function... Thus splitting it into two functions that are called from the same select case, will still call one even if the other is false...

Let me know if this works!

Regards,
Scott

Scott
Thanks for change but it is in the function called "Function textfile()
" that it stops, The user can choose to print the reports or output them to snap shot. When they try to output them is when it will not export the second report. In the function "Function textfile()", a text files is created and copies of the reports are output to snapshot for emiling. This DB is a processing tool for Timesheets. The text file and snapshots are emiled into our Corp office, the text fiel is autmatically loaded and the snapshots are for verification.

Thanks
Troy[/Qoute]

[Scott Price]

Scott
Thanks for change but it is in the function called "Function textfile()
" that it stops, The user can choose to print the reports or output them to snap shot. When they try to output them is when it will not export the second report. In the function "Function textfile()", a text files is created and copies of the reports are output to snapshot for emiling. This DB is a processing tool for Timesheets. The text file and snapshots are emiled into our Corp office, the text fiel is autmatically loaded and the snapshots are for verification.

Thanks
Troy[/Qoute]

Try splitting the textfile() function into two parts, similar to the function in my previous suggestion. If this doesn't work for you, please post back.

Regards,
Scott

[cwby1966]

Try splitting the textfile() function into two parts, similar to the function in my previous suggestion. If this doesn't work for you, please post back.

Regards,
Scott

Thanks Scot for the solution, unfortunity it is still not working, when it runs the first output it gives a error message that there are no records, which it is supposed to do, but even when there are records.
Thanks
Troy

[Scott Price]

Thanks Scot for the solution, unfortunity it is still not working, when it runs the first output it gives a error message that there are no records, which it is supposed to do, but even when there are records.
Thanks
Troy

I'm thinking, then, that the problem doesn't lie with the functions you posted... Take a good look at the conditions that occur before these functions are called.

If you can track through what happens in what order, you're half way to a solution :-) Especially look at the queries that populate these reports, and any code that runs under command buttons, onopen procedures etc...

Since both refuse to print even though one SHOULD have data in it, logically they have to be tied together somewhere back down the chain of functions, commands, queries, validation code, etc. Track through to where they are tied together, separate them out, and then test again.

Regards,
Scott

[Scott Price]

Another quick thought, if you are not the original developer of this database... Did the original developer leave a design blueprint laying around somewhere? This could help you track down the problem, especially if there existed/possibly still exists a so-called 'business' rule where someone decided that these two reports should never be printed separately from each other and so required a validation rule to be built in at some point tying them together never to be separated?

Regards,
Scott

[cwby1966]

Another quick thought, if you are not the original developer of this database... Did the original developer leave a design blueprint laying around somewhere? This could help you track down the problem, especially if there existed/possibly still exists a so-called 'business' rule where someone decided that these two reports should never be printed separately from each other and so required a validation rule to be built in at some point tying them together never to be separated?

Regards,
Scott

Scott
I am the orginal developer of this. The reason for them outputing together is simplfing process for end user. Most of the user are not very eperienced and we are trying t make this as simple as we can. I am wondering if i should be using a an IIF statement in the code to call the out put, ie checking for data before it even calls the report to output? Ideas?

Thanks
Troy

[Scott Price]

Scott
I am the orginal developer of this. The reason for them outputing together is simplfing process for end user. Most of the user are not very eperienced and we are trying t make this as simple as we can. I am wondering if i should be using a an IIF statement in the code to call the out put, ie checking for data before it even calls the report to output? Ideas?

Thanks
Troy

Well, you certainly could use an IIF statement, just keep in mind when using the iif structure in vba code that it can at times negatively affect your performance, because vba evaluates BOTH the value if true and the value if false statements, even though it only returns one of them. Perhaps better to use an If Not Isnull()...Then , or an If IsNull()...Then structure when programming this in vba. That said, I have used it myself, and will continue to use it in vba code when performance is not an issue.

(Note the iif statement doesn't result in the same performance problems when used in queries, can't just tell you exactly why not at the moment, but if you are interested in the intricacies of it, I think there's some good info on this site in the articles section that addresses the issue.)

It's still intriguing that your two reports won't fire independently of each other. As I said in a previous post, logically they HAVE to be tied together somewhere down the chain, either intentionally or unintentionally .

Anyway, let me know how it goes!

Regards,
Scott