The command or action 'OutputTo' isn't available now

Hi,

I'm having problems with this error appearing in this code......

Sub month_report()
On Error GoTo Err_month_report_complete

Dim stDocName As String, stDocName2 As String, db As Database
Dim addr As Recordset, emadd As String, attach As String, attach2 As String
Dim objOutlook As Outlook.Application, objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient, objOutlookAttach As Outlook.Attachment

   Set db = CurrentDb()
   Set addr = db.OpenRecordset("tblEmail")
   
    addr.MoveFirst
    emadd = addr.Fields("Address").Value
    Set db = Nothing
    stDocName = "rptMonth_Complete"
    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS,  _
                             "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"
    stDocName2 = "rptMonth_Active"
    DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS,  _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"
    attach = "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"
    attach2 = "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsofile = fso.CreateTextFile("G:\LabResults\SGS\ByPass.vbs")
    fsofile.writeline "set fso = createobject(""wscript.shell"")"
    fsofile.writeline "while fso.appactivate(""microsoft outlook"") =false"
    fsofile.writeline "wscript.sleep 1000"
    fsofile.writeline "wend"
    fsofile.writeline "fso.sendkeys ""a"", true"
    fsofile.writeline "fso.sendkeys ""y"", true"
    fsofile.writeline "wscript.sleep 7000"
    fsofile.writeline "while fso.appactivate(""microsoft outlook"") =false"
    fsofile.writeline "wscript.sleep 3000"
    fsofile.writeline "wend"
    fsofile.writeline "fso.sendkeys ""y"", true"
    fsofile.Close

    Set wshShell = CreateObject("Wscript.Shell")
    wshShell.Run ("G:\LabResults\SGS\ByPass.vbs")
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

      With objOutlookMsg
          .To = emadd
          .Subject = "PGM Housing Complaint Reports for the month of " & Format(Now() - 1, "mmmm yyyy") & ""
          .Body = "PGM Housing Complaint for the month of " & Format(Now() - 1, "mmm, yyyy") & Chr(13) & ""
          .Attachments.Add (attach)
          .Attachments.Add (attach2)
          .Importance = olImportanceHigh
          .Send
      End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
   
Exit_month_report_complete:
    Exit Sub

Err_month_report_complete:
    MsgBox "#" & Err.Number & ": " & Err.Description
End Sub

This is run depending on this code......

Private Sub Form_Load()
  If Time >= TimeValue("23:00") Then  'If it is after 11:00 P.M.
    week_report
  If DatePart("d", Date) = 1 Then
    month_report
  End If
    Application.Quit
  End If
End Sub

The weird thing I find in this is it works fine if I run it from VBE. If I let it run as normal on form load, it'll output the first file OK, but gives the above error on the second.

Any ideas why this is happening?
LVL 48
Wayne Taylor (webtubbs)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kirenievsCommented:
>>The weird thing I find in this is it works fine if I run it from VBE. If I let it run as normal on form load, it'll output the first file OK, but gives the above error on the second.

Does this mean it works stepping through code or have you also tried to run continuously?

If it works while taking one step at a time it could be access runs one command before the previous ar totally finished. If so try to add DoEvents between the lines. It might help..At least DoEvents have solved some strange problems to me

    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS,  _
                             "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"
    stDocName2 = "rptMonth_Active"
    DoEvents
    DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS,  _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"
Wayne Taylor (webtubbs)Author Commented:
Yeah, I mean running continuously (Pressing F5 when in the VBE).

I just tried adding DoEvents but no luck. It still gets stuck creating the second report.
thenelsonCommented:
If I understand your question correctly, you are getting the 'OutputTo' isn't available now error on this line:
DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS,  _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"

1:  Date - 1 sometimes causes problems.  Change it to:
     DateAdd("d", -1, Date)
     Probably not your problem

2:  I think kirenievs was heading the right direction.  It looks like a timing problem.  Try:

Dim strOutputFile As String

strOutputFile = "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"

    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, strOutputFile

    Do Until FileSystemObject.FileExists(strOutputFile)
    Loop

    stDocName2 = "rptMonth_Active"
    DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS,  _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"

The above may not work because OutputTo may create the file then load it.  If so try:

Dim StartTime As Long

   DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS,  _
                             "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"
    stDocName2 = "rptMonth_Active"
   StartTime = Timer
   On Error Resume Next
    Do
           DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS,  _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"
           If Timer - StartTime > 5 Then          
                  'just to make sure we don't have an endless loop, we give the first OutputTo 5 seconds
                  MsgBox Err & " " & Err.Description
                  Exit Sub
           End If
    Loop While Err = <the error number of 'OutputTo' isn't available now>
    If Err <> 0 Then
           MsgBox Err & " " & Err.Description
           Exit Sub
    End If
    On Error Goto 0    'or what ever your on error statement was
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Wayne Taylor (webtubbs)Author Commented:
Hi thenelson,

I tried all of you suggestions but they still come back with the same error (#2046, The command or action 'OutputTo' isn't available now).

Would it be better to split it up into two seperate emails? I'm hoping I can keep the together, but if the only way for this to work is to split them up, then I'll have to do it.

Cheers,

Wayne
thenelsonCommented:
The second segestion didn't work?  Did you try increasing the time?  Try changing :
If Timer - StartTime > 5 Then
to
If Timer - StartTime > 120 Then
and add a debug.print to see how much time it took:
    Loop While Err = 2046
    debug.print Timer - StartTime
Wayne Taylor (webtubbs)Author Commented:
No, it is still coming up with the same error.

I added an extra bit to the error messages to see where it stopped, ie.

------------------------------------------------------------------------------------------------------------
Dim StartTime As Long

   DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, _
                             "G:\LabResults\SGS\Complete_" & Format(Date - 1, "mmmyy") & ".xls"
    stDocName2 = "rptMonth_Active"
   StartTime = Timer
   On Error Resume Next
    Do
           DoCmd.OutputTo acOutputReport, stDocName2, acFormatXLS, _
                             "G:\LabResults\SGS\Active_" & Format(Date - 1, "mmmyy") & ".xls"
           If Timer - StartTime > 120 Then
                  MsgBox Err & " " & Err.Description & "ERROR #1"
                  Exit Sub
           End If
    Loop While Err = 2046
    Debug.Print Timer - StartTime
    If Err <> 0 Then
           MsgBox Err & " " & Err.Description & "ERROR #2"
           Exit Sub
    End If
    On Error GoTo Err_month_report_complete
------------------------------------------------------------------------------------------------------------

And it stopped on the first error message (ERROR #1)

Then I changed the error messages to......

MsgBox Err & " " & Err.Description & " " & Timer

and it said the timer was 29445.

I tried your last code again and it stopped on....

Do Until FileSystemObject.FileExists(strOutputFile)

I think I may have had a On Error Resume Next in there somewhere that caused it to continuously loop.
Wayne Taylor (webtubbs)Author Commented:
Got It :)

I ended up using the Form_Timer code. Eg,

Sub Form_Load()
Me.TimerInterval = 1000
End Sub

Private Sub Form_Timer()
  If Time >= TimeValue("06:00") Then  'If it is after 11:00 P.M.
    week_report
  If DatePart("d", Date) = 1 Then
    month_report
  End If
    Application.Quit
  End If
End Sub


....and it worked. Not sure why though.

Cheers,

Wayne
thenelsonCommented:
One important thing whenever you are using a timer for a one time event -- immediately turn the timer off or it will reoccur:

Private Sub Form_Timer()
Me.TimerInterval = 0
 If Time >= TimeValue("06:00") Then
...

Are you running the Output To when the form opens -- on the form load event?  I didn't think to ask that -- one of the problems of trying to debug remotely.  I find I often have to delay asynchronous routines (routines that run in the background while the code continues  -- many of the input/output routines are like that) need a half a second or more for the form to settle down before running them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne Taylor (webtubbs)Author Commented:
I was running the OutputTo from the form load event. I tried running it on Form_Open but it wouldn't do anything.

But at least now it's working. That's the most important thing I guess.

Thanks for the timer tip as well. Because of that I'll give you the points.

Cheers,

Wayne.
thenelsonCommented:
Glad you found the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.