VBA Code failing when DB opened to a Form.

[puforee]

I have a DB that is set to open to a specific form. This part works. One of the Command buttons on the Form outputs 4 reports to my C: drive into a Folder. Three of these "EOC", "1097A", and "PTEAR B rpt" always work.

The fourth, "CLEAR", is failing with "Run-Time Error 2046. The command or action 'OutputTo' isn't available now.

The code outputs the reports in this order: EOC, 1087A, CLEAR, PTEAR. I have moved the order around but the CLEAR is the only one that fails.

When it fails I check the report and it looks good and has the correct information.

Here is the code:

Private Sub PrepForms_Click()
     
    DoCmd.Hourglass (HourglasOn)
    'DoCmd.Echo (EchoOff)
    'DoCmd.SetWarnings (Off)
         
    fileName = "EOC" 'filename for PDF file*
    fldrPath = "C:\TempEmail"
    filePath = fldrPath & "\" & fileName & ".pdf"
    DoCmd.OutputTo acReport, "EOC Sheet rpt", acFormatPDF, filePath, False
    
    fileName = "1087A"
    filePath = fldrPath & "\" & fileName & ".pdf"
    DoCmd.OutputTo acReport, "1087A rpt", acFormatPDF, filePath, False
    
    fileName = "CLEAR"
    filePath = fldrPath & "\" & fileName & ".pdf"
    'DoCmd.OutputTo acReport, "CLEAR rpt", acFormatPDF, filePath, False
    [highlight #FCE94F]DoCmd.OutputTo acReport, "CLEAR rpt", acFormatPDF, filePath, False[/highlight]
    
    fileName = "PTEAR Form"
    filePath = fldrPath & "\" & fileName & ".pdf"
    DoCmd.OutputTo acReport, "PTEAR B rpt", acFormatPDF, filePath, False
    
    'DoCmd.Echo (EchoOn)
    DoCmd.Hourglass (HourglassOff)
    'DoCmd.SetWarnings (WarningsOn)
    MsgBox "All Forms have been prepared. Please select the first instructor."


    Me.EmailDD.Enabled = True
End Sub

Now for the trick. If I open the DB using the shift key and then open the form and click the same command button, everything works fine. Only when I open the DB with a double click does the error occur.


I don't under stand why opening the DB normally shows this problem and opening it with the SHIFT allows it to run correctly.

Help please,

John

 

[puforee]

Update. It seems the file selected to fail is more random than I thought. When I move the order of outputs the failing output will change. I have ganged all these outputs commands together as shown in the previous post.

Set file path

Do Output 1, then 2, then 3, then 4

They all use the same file path C:\TempEmail. This should be OK to do just once - Yes? Then each set of output codes can use it.

When using the DoCmd.OutputTo code for a file do I have to wait for the first to get through before doing the second, etc.?

Again, the confusion is that it runs fine when I use the shit key opening the DB and open the form manually. But fails when I open the DB with just a double click.

What am I missing?

I hate to put 4 command buttons on the form, one for each output.

Thanks,

John

 

[Andrzejek]

Does it misbehave when you step thru your code line by line? If you do it line-by-line, slow, you will get your answer to:
"When using the DoCmd.OutputTo code for a file do I have to wait for the first to get through before doing the second, etc.?"

BTW, your code may be easier to read if you do this:

Private Sub PrepForms_Click()
     
    DoCmd.Hourglass (HourglasOn)
    fldrPath = "C:\TempEmail\"
    
    For X = 1 To 4
        fileName = Choose(X, "EOC", "1087A", "CLEAR", "PTEAR Form")
        repName = Choose(X, "EOC Sheet rpt", "1087A rpt", "CLEAR rpt", "PTEAR B rpt")
        filePath = fldrPath & fileName & ".pdf"
        DoCmd.OutputTo acReport, repName, acFormatPDF, filePath, False
    Next X

    DoCmd.Hourglass (HourglassOff)
    MsgBox "All Forms have been prepared. Please select the first instructor."
    Me.EmailDD.Enabled = True

End Sub

Just a suggestion....


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Thanks Andy - much appreciated but the same fault occurs. I believe this may be because Access does not know when the outputs are complete. It may be the old story of not being able to do two things at once.

So #1 is there code that can detect when each output is done? Can we use that code to control when the next output starts?

#2 why does this only happen when I open the DB with a double click and not when I open it with a SHIFT double click?

Or would putting a time delay in the For Next loop help?

Thanks,
John

 

[sxschech]

It may need something more, but you could try doevents.

...
...
      DoCmd.OutputTo acReport, repName, acFormatPDF, filePath, False
      DoEvents
    Next X

 

[puforee]

Sxscheh - Same error. Thanks for the suggestion

 

[puforee]

Andy and sxschech (Got the spelling right this time sxschech, sorry)

OK. Here is what works. It took a lot of searching on Google to find this suggestion.

Note the [highlight #FCE94F]M.SetFocus[/highlight]

    DoCmd.Hourglass (HourglasOn)
    
    fldrPath = "C:\TempEmail\"
    
    For X = 1 To 4
        fileName = Choose(X, "EOC", "1087A", "CLEAR", "PTEAR Form")
        repName = Choose(X, "EOC Sheet rpt", "1087A rpt", "CLEAR rpt", "PTEAR B rpt")
        filePath = fldrPath & fileName & ".pdf"
        [highlight #FCE94F]Me.SetFocus[/highlight]
        DoCmd.OutputTo acReport, repName, acFormatPDF, filePath, False
        
        
    Next X

    DoCmd.Hourglass (HourglassOff)
    MsgBox "All Forms have been prepared. Please select the first instructor."
    Me.EmailDD.Enabled = True

I an not sure why this works but it does.

Does this ring any bells with you guys? Can you explain why it is working?

Thanks

John