Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Stop outputing Report if NO Data

Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#1: Aug 8 '07
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
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.SetWarnings False
  2.     DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
  3.     DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
  4.     DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
  5.     DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  6.     DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
  7.  
I am not sure what I amdoing worng, any help?
Thanks
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#2: Aug 8 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1.  DoCmd.SetWarnings False
  2.     DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
  3.     DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
  4.     DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
  5.     DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  6.     DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
  7.  
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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#3: Aug 9 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#4: Aug 9 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#5: Aug 9 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.  
  3. On Error GoTo Report_NoDataError
  4.  
  5.    MsgBox "No records meet the report criteria; cancelling report printing"
  6.    Cancel = True
  7.  
  8. Report_NoDataExit:
  9.    Exit Sub
  10.  
  11. Report_NoDataError:
  12.    MsgBox Err.Description
  13.    Resume Report_NoDataExit
  14.  
  15. End Sub
  16.  
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#6: Aug 9 '07

re: Stop outputing Report if NO Data

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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#7: Aug 9 '07

re: Stop outputing Report if NO Data

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
Expand|Select|Wrap|Line Numbers
  1.  If Nz(Forms![control center]![Report list]) <> "" Then
  2.         Select Case Forms![control center]![Report list]
  3.             Case "MBM Time Sheets"
  4.                 Call mbmtimeshts
  5.             Case "Time Summary"
  6.                 Call timesum
  7.             Case "Year To Date Managers Chart"
  8.                 Call ytdmgr
  9.             Case "Vacation Calendar"
  10.                 Call VacCal
  11.             Case "Seniority Report"
  12.                 Call senority
  13.             Case "Absenteeism Report"
  14.                 Call attendance
  15.             Case "Associate List"
  16.                 Call emplist
  17.             Case "Birthday List"
  18.                 Call bday
  19.             Case "Cost Center List"
  20.                 Call cclist
  21.             Case "Punch Card Labels"
  22.                 Call punchcard
  23.             Case "Associate Phone Book"
  24.                 Call phonebook
  25.             Case "Year To Date Managers Report"
  26.                 Call ytdrpt
  27.             Case "Online Vacation Calendar"
  28.                 Call vacchrt
  29.             Case "Staffing Service Time Sheets"
  30.                 Call Stafftimeshts
  31.             Case "Entry Verification"
  32.                 Call entryverify
  33.             Case "Set Up Text File"
  34.                 Call textfile
  35.             Case "Vacation Calender By Name"
  36.                 Call vaccalstaff
  37.         End Select
  38.  
  39.    Else
  40.       Forms![control center]![Report list].SetFocus
  41.       Forms![control center]![Report list].Dropdown
  42. End If
  43. End Function
  44. Function mbmtimeshts()
  45. 'On Error GoTo mbmtimesgts_err
  46. DoCmd.SetWarnings False
  47.                 If Forms![control center]![frareportmode] = 1 Then
  48.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  49.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  50.                     ElseIf Forms![control center]![frareportmode] = 2 Then
  51.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  52.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  53.                 End If
  54. 'mbmtimesgts_err:
  55.  
  56. 'MsgBox ("There are no Records for this Time Period.")
  57. 'Reponse = 0
  58.  
  59. End Function
  60.  
  61. Function timesum()
  62.  
  63.         If Forms![control center]![frareportmode] = 1 Then
  64.         DoCmd.OpenReport "Time Summary", acPreview, "", ""
  65.         ElseIf Forms![control center]![frareportmode] = 2 Then
  66.         DoCmd.OpenReport "Time Summary", acNormal, "", ""
  67.         End If
  68.  
  69. End Function
  70.  
  71. Function ytdmgr()
  72.  DoCmd.OpenForm "pvt_ytdchart", acFormPivotTable, "", "", , acNormal
  73. End Function
  74.  
  75. Function VacCal()
  76.  
  77.          If Forms![control center]![frareportmode] = 1 Then
  78.         DoCmd.OpenReport "rpt_calander", acPreview, "", ""
  79.         ElseIf Forms![control center]![frareportmode] = 2 Then
  80.         DoCmd.OpenReport "rpt_calander", acNormal, "", ""
  81.         End If
  82.  
  83. End Function
  84. Function senority()
  85.  
  86.          If Forms![control center]![frareportmode] = 1 Then
  87.         DoCmd.OpenReport "rpt_SENORITY", acPreview, "", ""
  88.         ElseIf Forms![control center]![frareportmode] = 2 Then
  89.         DoCmd.OpenReport "rpt_SENORITY", acNormal, "", ""
  90.         End If
  91.  
  92. End Function
  93. Function attendance()
  94.  
  95.          If Forms![control center]![frareportmode] = 1 Then
  96.         DoCmd.OpenReport "RPT_Staff Attendance", acPreview, "", ""
  97.         ElseIf Forms![control center]![frareportmode] = 2 Then
  98.         DoCmd.OpenReport "RPT_Staff Attendance", acNormal, "", ""
  99.         End If
  100.  
  101. End Function
  102.  
  103. Function emplist()
  104.          If Forms![control center]![frareportmode] = 1 Then
  105.         DoCmd.OpenReport "Employee List", acPreview, "", ""
  106.         ElseIf Forms![control center]![frareportmode] = 2 Then
  107.         DoCmd.OpenReport "Employee List", acNormal, "", ""
  108.         End If
  109. End Function
  110.  
  111. Function bday()
  112.          If Forms![control center]![frareportmode] = 1 Then
  113.         DoCmd.OpenReport "rtp_bday", acPreview, "", ""
  114.         ElseIf Forms![control center]![frareportmode] = 2 Then
  115.         DoCmd.OpenReport "rtp_bday", acNormal, "", ""
  116.         End If
  117. End Function
  118.  
  119. Function cclist()
  120.          If Forms![control center]![frareportmode] = 1 Then
  121.         DoCmd.OpenReport "Cost Center List", acPreview, "", ""
  122.         ElseIf Forms![control center]![frareportmode] = 2 Then
  123.         DoCmd.OpenReport "Cost Center List", acNormal, "", ""
  124.         End If
  125. End Function
  126.  
  127. Function punchcard()
  128.          If Forms![control center]![frareportmode] = 1 Then
  129.         DoCmd.OpenReport "Labels punch card print", acPreview, "", ""
  130.         ElseIf Forms![control center]![frareportmode] = 2 Then
  131.         DoCmd.OpenReport "Labels punch card print", acNormal, "", ""
  132.         End If
  133. End Function
  134. Function phonebook()
  135.          If Forms![control center]![frareportmode] = 1 Then
  136.         DoCmd.OpenReport "pHONE bOOK", acPreview, "", ""
  137.         ElseIf Forms![control center]![frareportmode] = 2 Then
  138.         DoCmd.OpenReport "pHONE bOOK", acNormal, "", ""
  139.         End If
  140. End Function
  141. Function ytdrpt()
  142.          If Forms![control center]![frareportmode] = 1 Then
  143.         DoCmd.OpenReport "rpt_YTD MGR SUMMARY", acPreview, "", ""
  144.         ElseIf Forms![control center]![frareportmode] = 2 Then
  145.         DoCmd.OpenReport "rpt_YTD MGR SUMMARY", acNormal, "", ""
  146.         End If
  147. End Function
  148. Function vacchrt()
  149.  DoCmd.OpenForm "NEW VAC", acFormPivotTable, "", "", , acNormal
  150. End Function
  151. Function Stafftimeshts()
  152. On Error GoTo STAFFTIMESHTS_ERR
  153.         If Forms![control center]![frareportmode] = 1 Then
  154.         Beep
  155.         MsgBox "Are You sure You Have Selected the Correct Week of The Pay Period", vbInformation, ""
  156.         DoCmd.OpenReport "Staff Service Time Sheet", acViewPreview, "", ""
  157.         DoCmd.OpenReport "Staff Service Time Sheet SB", acViewPreview, "", ""
  158.         ElseIf Forms![control center]![frareportmode] = 2 Then
  159.         MsgBox "Are You sure You Have Selected the Correct Week of The Pay Period", vbInformation, ""
  160.         DoCmd.OpenReport "Staff Service Time Sheet", acNormal, "", ""
  161.         DoCmd.OpenReport "Staff Service Time Sheet SB", acNormal, "", ""
  162.                 End If
  163. STAFFTIMESHTS_ERR:
  164. MsgBox ("There are no Records for this Time Period.")
  165. Reponse = 0
  166. End Function
  167.  
  168. Function entryverify()
  169. DoCmd.OpenQuery "EMP List Without Matching time summary", acNormal, acEdit
  170. End Function
  171. Function textfile()
  172.     Dim strinput As String
  173.     strinput = "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.txt"
  174.     DoCmd.SetWarnings False
  175.     DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
  176.     DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
  177.     DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
  178.     DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  179.     DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
  180.     Application.FollowHyperlink strinput, , True
  181.  
  182.     End Function
  183.  
  184. Function vaccalstaff()
  185.   If Forms![control center]![frareportmode] = 1 Then
  186.         DoCmd.OpenReport "rpt_calbystaff", acPreview, "", ""
  187.         ElseIf Forms![control center]![frareportmode] = 2 Then
  188.         DoCmd.OpenReport "qry_listreport", acNormal, "", ""
  189.         End If
  190. End Function
  191.  
  192.  
Thanks
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#8: Aug 9 '07

re: Stop outputing Report if NO Data

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

Expand|Select|Wrap|Line Numbers
  1. Call mbmattnsheet
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)

Expand|Select|Wrap|Line Numbers
  1. Function mbmtimeshts()
  2. 'On Error GoTo mbmtimesgts_err
  3. DoCmd.SetWarnings False
  4.                 If Forms![control center]![frareportmode] = 1 Then
  5.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  6.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  7.                     ElseIf Forms![control center]![frareportmode] = 2 Then
  8.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  9.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  10.                 End If
  11. 'mbmtimesgts_err:
  12.  
  13. 'MsgBox ("There are no Records for this Time Period.")
  14. 'Reponse = 0
  15.  
  16. End Function
To this:

Expand|Select|Wrap|Line Numbers
  1. Function mbmtimeshts()
  2. 'On Error GoTo mbmtimesgts_err
  3. DoCmd.SetWarnings False
  4.                 If Forms![control center]![frareportmode] = 1 Then
  5.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  6.                ElseIf Forms![control center]![frareportmode] = 2 Then
  7.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  8.                End If
  9. 'mbmtimesgts_err:
  10.  
  11. 'MsgBox ("There are no Records for this Time Period.")
  12. 'Reponse = 0
  13.  
  14. End Function
  15.  
  16. Function mbmattnsheet()
  17. 'On Error GoTO mbmattns_err
  18.              If Forms![control center]![frareportmode] = 1 Then
  19.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  20.                ElseIf Forms![control center]![frareportmode] = 2 Then
  21.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  22.                End If
  23. 'mbmattns_err:
  24.  
  25. 'MsgBox ("There are no Records for this Time Period.")
  26. 'Reponse = 0
  27.  
  28. End Function
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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#9: Aug 13 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1. Call mbmattnsheet
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)

Expand|Select|Wrap|Line Numbers
  1. Function mbmtimeshts()
  2. 'On Error GoTo mbmtimesgts_err
  3. DoCmd.SetWarnings False
  4.                 If Forms![control center]![frareportmode] = 1 Then
  5.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  6.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  7.                     ElseIf Forms![control center]![frareportmode] = 2 Then
  8.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  9.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  10.                 End If
  11. 'mbmtimesgts_err:
  12.  
  13. 'MsgBox ("There are no Records for this Time Period.")
  14. 'Reponse = 0
  15.  
  16. End Function
To this:

Expand|Select|Wrap|Line Numbers
  1. Function mbmtimeshts()
  2. 'On Error GoTo mbmtimesgts_err
  3. DoCmd.SetWarnings False
  4.                 If Forms![control center]![frareportmode] = 1 Then
  5.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  6.                ElseIf Forms![control center]![frareportmode] = 2 Then
  7.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  8.                End If
  9. 'mbmtimesgts_err:
  10.  
  11. 'MsgBox ("There are no Records for this Time Period.")
  12. 'Reponse = 0
  13.  
  14. End Function
  15.  
  16. Function mbmattnsheet()
  17. 'On Error GoTO mbmattns_err
  18.              If Forms![control center]![frareportmode] = 1 Then
  19.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  20.                ElseIf Forms![control center]![frareportmode] = 2 Then
  21.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  22.                End If
  23. 'mbmattns_err:
  24.  
  25. 'MsgBox ("There are no Records for this Time Period.")
  26. 'Reponse = 0
  27.  
  28. End Function
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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#10: Aug 13 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#11: Aug 14 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#12: Aug 14 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#13: Aug 14 '07

re: Stop outputing Report if NO Data

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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#14: Aug 14 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by 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

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's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#15: Aug 14 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

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
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#16: Aug 16 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

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

Scott
I have them both outputing but even when there is data in both the second report will not have anything in it even when there is data there. I can't find anyhting that would tie teh 2 together except that tehy both come from the same data table. Coul dthat be something?
Thanks
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#17: Aug 16 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

Scott
I have them both outputing but even when there is data in both the second report will not have anything in it even when there is data there. I can't find anyhting that would tie teh 2 together except that tehy both come from the same data table. Coul dthat be something?
Thanks
Troy

I guess we've focused so much on the vb code, I haven't even thought to ask this question :-(

Are they based on one query, or two?

If two, have you run them separately to make sure they return the data they should?

If one, you should split them, eh?

Regards,
Scott
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#18: Aug 16 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

I guess we've focused so much on the vb code, I haven't even thought to ask this question :-(

Are they based on one query, or two?

If two, have you run them separately to make sure they return the data they should?

If one, you should split them, eh?

Regards,
Scott

Scot
The data for both reports is in the same table, there is a set up query and then sthere are 2 different queries that generate each report. Coul dit be the set up query ?

Thanks
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#19: Aug 16 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

Scot
The data for both reports is in the same table, there is a set up query and then sthere are 2 different queries that generate each report. Coul dit be the set up query ?

Thanks
Troy

I'd like to say yes!

Try running the setup query while replicating the situation that produces no data for the one report. If it returns no data, then you've got your answer about where the two are tied together, intentionally or un- .

On another line, do you really NEED the setup query? Can you simply base both off the same table?

Regards,
Scott
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#20: Aug 17 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

I'd like to say yes!

Try running the setup query while replicating the situation that produces no data for the one report. If it returns no data, then you've got your answer about where the two are tied together, intentionally or un- .

On another line, do you really NEED the setup query? Can you simply base both off the same table?

Regards,
Scott

Scott
I tried this and the queries run fine. everything seems to run fine except when i try to output in code.
I dont know anymore.
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#21: Aug 17 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

Scott
I tried this and the queries run fine. everything seems to run fine except when i try to output in code.
I dont know anymore.
Troy


Just looking back over this thread to see what I've missed!! I noticed this time looking at these lines of code that your function msmtimeshts is dependent on something called frareportmode = 1. Have we established what the criteria might be for making this statement true, and therefore calling the function and outputting the reports?

Expand|Select|Wrap|Line Numbers
  1. Function mbmtimeshts()
  2. 'On Error GoTo mbmtimesgts_err
  3. DoCmd.SetWarnings False
  4.                 If Forms![control center]![frareportmode] = 1 Then
  5.                     DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
  6.                     DoCmd.OpenReport "MBM ATTN Sheet", acPreview, "", ""
  7.                     ElseIf Forms![control center]![frareportmode] = 2 Then
  8.                     DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
  9.                     DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
  10.                 End If
  11. 'mbmtimesgts_err:
  12.  
  13. 'MsgBox ("There are no Records for this Time Period.")
  14. 'Reponse = 0
Regards,
Scott
MGrowneyARSI's Avatar
Member
  
Join Date: Aug 2007
Posts: 92
#22: Aug 17 '07

re: Stop outputing Report if NO Data

Have you tried to just use and if/then statment around the code for the reports

If <> Null then
Run Report1
end if
if <> Null then
Run Report2
end if
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#23: Aug 21 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by MGrowneyARSI

Have you tried to just use and if/then statment around the code for the reports

If <> Null then
Run Report1
end if
if <> Null then
Run Report2
end if

Scott
The function that references "frareportmode = 1" is a difference funcrion. THat is how they can either preview or print reports.
The error is occurring in
Expand|Select|Wrap|Line Numbers
  1. Function textfile()
  2.     Dim strinput As String
  3.     strinput = "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.txt"
  4.     DoCmd.SetWarnings False
  5.     DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
  6.     DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
  7.     DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
  8.     DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  9.     DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
  10.     Application.FollowHyperlink strinput, , True
  11.  
  12.     End Function
  13.  
Thats why i can seem to understand why his is not working. If you Preview or print the reports they work fine. if you select tooutput them to snapshot they dont.

Thanks
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#24: Aug 21 '07

re: Stop outputing Report if NO Data

Troy, bear with me here... I'm having senior moments I think.

The syntax of your output commands might be what's holding you up:

Instead of this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  2. DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)",
Try this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "MBM Time Sheet", acFormatSNP
  2.  
The rest of your code there should be all right... (I just replaced the part of the code line that looked like it needed a bit of different syntax)

Regards,
Scott

P.S. If this still doesn't work, try changing the acFormatSNP to "Snapshot Format"
Member
  
Join Date: Feb 2007
Location: Minneapolis MN
Posts: 49
#25: Aug 21 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by Scott Price

Troy, bear with me here... I'm having senior moments I think.

The syntax of your output commands might be what's holding you up:

Instead of this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  2. DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)",
Try this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "MBM Time Sheet", acFormatSNP
  2.  
The rest of your code there should be all right... (I just replaced the part of the code line that looked like it needed a bit of different syntax)

Regards,
Scott

P.S. If this still doesn't work, try changing the acFormatSNP to "Snapshot Format"

Scott
Still no go! I tried both
The code will still output the report but it will be blank, it wont even have the dates anything just the lay out with out data. it like it cant find the data for the second report.

Thanks
Troy
Scott Price's Avatar
Moderator
  
Join Date: Jul 2007
Location: Seattle, WA
Posts: 1,314
#26: Aug 21 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by cwby1966

Scott
Still no go! I tried both
The code will still output the report but it will be blank, it wont even have the dates anything just the lay out with out data. it like it cant find the data for the second report.

Thanks
Troy

The only other thing I can think of is to delete the 0 from the end of your DoCmd.Output code. That just specifies an encoding format, which I don't think you really need nor want.

Regards,
Scott

p.s. I also PM'ed an expert to take a look at this to see what I'm missing!
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,214
#27: Aug 24 '07

re: Stop outputing Report if NO Data

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1.  DoCmd.SetWarnings False
  2.     DoCmd.OpenQuery "QRY_EXPCLNUP", acViewNormal, acEdit
  3.     DoCmd.OpenQuery "Data File Pull", acViewNormal, acEdit
  4.     DoCmd.TransferText acExportDelim, "", "TBL_EXPORT", "C:\Account Tools\Payroll\EXPORT\TIMESHEETINFO.TXT", True, ""
  5.     DoCmd.OutputTo acReport, "MBM Time Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMTIMESHEET.SNP", False, "", 0
  6.     DoCmd.OutputTo acReport, "MBM ATTN Sheet", "SnapshotFormat(*.snp)", "C:\Account Tools\Payroll\EXPORT\MBMATTN.SNP", False, "", 0
  7.  
I am not sure what I amdoing worng, any help?
Thanks

If both Reports open successfully independently of each other, rather than setting the Cancel Parameter to True in the NoData() Event, when no data exists, check and see if Records are present by examing the RecordSource of the Report, then proceed from there.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,383 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved