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

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)

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

To this:

Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnings False
                If Forms![control center]![frareportmode] = 1 Then
                    DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
               ElseIf Forms![control center]![frareportmode] = 2 Then
                    DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
               End If
'mbmtimesgts_err:
 
'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.OpenReport "MBM ATTN Sheet", acPreview, "", ""
               ElseIf Forms![control center]![frareportmode] = 2 Then
                    DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
               End If
'mbmattns_err:
 
'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0
 
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

[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

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)

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

To this:

Function mbmtimeshts()
'On Error GoTo mbmtimesgts_err
DoCmd.SetWarnings False
                If Forms![control center]![frareportmode] = 1 Then
                    DoCmd.OpenReport "MBM Time Sheet", acPreview, "", ""
               ElseIf Forms![control center]![frareportmode] = 2 Then
                    DoCmd.OpenReport "MBM Time Sheet", acNormal, "", ""
               End If
'mbmtimesgts_err:
 
'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.OpenReport "MBM ATTN Sheet", acPreview, "", ""
               ElseIf Forms![control center]![frareportmode] = 2 Then
                    DoCmd.OpenReport "MBM ATTN Sheet", acNormal, "", ""
               End If
'mbmattns_err:
 
'MsgBox ("There are no Records for this Time Period.")
'Reponse = 0
 
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]

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

[cwby1966]

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]

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

[cwby1966]

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]

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

[cwby1966]

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]

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?

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

Regards,
Scott

[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

[cwby1966]

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

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
 

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]

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:

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)",

Try this:

DoCmd.OutputTo acOutputReport, "MBM Time Sheet", acFormatSNP
 

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"

[cwby1966]

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:

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)",

Try this:

DoCmd.OutputTo acOutputReport, "MBM Time Sheet", acFormatSNP
 

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]

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]

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

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.