Need a method to slow execution down

[GraeagleBill]

Not at all surprising to get the error depicted below. I need a method inserted in my code below after each of the DoCmd.OutputTo commands to wait for the action to complete. What method might one suggest?

Private Sub cmdPDFs_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Give UI ability to bulk refresh the floor layouts
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim I As Integer


On Error GoTo Err_Handler


For I = 1 To 4
    strCurFloor = 100 + I
    strfloor = I
    DoCmd.OutputTo acOutputForm, "frmNorthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\North" & strfloor & ".pdf"
    DoCmd.OutputTo acOutputForm, "frmSouthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\South" & strfloor & ".pdf"
Next I


DoCmd.Close


Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " in attempting to create pdf floor layouts : " & Err.description
   Resume Exit_Handler


End Sub

[isladogs]

DoEvents ...too short ...DoEvents

[orange]

Here's an older routine that may apply.

         '---------------------------------------------------------------------------------------
' Procedure : Pause
' Author    : From ghudson Access World Forums
' Date      : 28/02/2011
' Purpose   : To cause Access to Pause for a specific amount of time(in seconds)
'---------------------------------------------------------------------------------------
'
Public Function Pause(NumberOfSeconds As Variant)
10    On Error GoTo Err_Pause

          Dim PauseTime As Variant, start As Variant

20        PauseTime = NumberOfSeconds
30        start = Timer
40        Do While Timer < start + PauseTime
50        DoEvents
60        Loop

Exit_Pause:
70        Exit Function

Err_Pause:
80        MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
90        Resume Exit_Pause

End Function

Test routine:

 Sub TESTTIMER()

Debug.Print Now
Pause (3)
Debug.Print Now
End Sub

Result:

15/05/2015 8:10:15 AM 
15/05/2015 8:10:18 AM

[GraeagleBill]

Hi Colin,
I don't understand your post? First, I always thought the DoEvents was to force completion of DB update activity? Second, your post suggests that I insert multiple DoEvents in succession after each of the OutputTo statements?
Bill

[Micron]

Just me, but I don't see the point of passing a parameter to a procedure then creating a variable that is equal to that parameter as it's already declared. One could argue that an error handler is usually a good idea, but could also argue that the pause value will be provided by code in virtually all cases, so it should never be null or of the wrong data type. I modified a similar version of the above, but eventually went with Single so that I could pass a fractional portion of seconds. Even half a second can add up if you've got many routines to run through in some sort of update. FWIW, it looks like

Public Function Pause(sngSecs As Single)
Dim Start As Variant
Start = Timer
Do While Timer < Start + sngSecs
    DoEvents
Loop
End Function

[isladogs]

Hi Colin,
I don't understand your post? First, I always thought the DoEvents was to force completion of DB update activity? Second, your post suggests that I insert multiple DoEvents in succession after each of the OutputTo statements?
Bill

The forum has a minimum reply length.
Writing DoEvents was too short so I had to repeat it in order to post

DoEvents is one method of allowing time for code to complete before the next line of code is run.
There are other alternatives but IMO that is usually the best method.
Specifying a Pause time can add a degree of certainty to that but will often just add an unnecessary delay.

Or you can use dbIdle.RefreshCache - shorter delay but may not allow code completion as a result.
See my article Speed Comparison Tests 3 - Mendip Data Systems (isladogs.co.uk)

[GraeagleBill]

Same error even with the DoEvents following the OutputTo commands. Could it be that the form itself might still be open?

Private Sub cmdPDFs_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Give UI ability to bulk refresh the floor layouts
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim I As Integer


On Error GoTo Err_Handler


For I = 1 To 4
    strCurFloor = 100 + I
    strfloor = I
    DoCmd.OutputTo acOutputForm, "frmNorthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\North" & strfloor & ".pdf"
    DoEvents
    DoCmd.OutputTo acOutputForm, "frmSouthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\South" & strfloor & ".pdf"
    DoEvents
Next I


DoCmd.Close


Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " in attempting to create pdf floor layouts : " & Err.description
   Resume Exit_Handler


End Sub

[isladogs]

Same error even with the DoEvents following the OutputTo commands. Could it be that the form itself might still be open?

Not sure its relevant but you can easily check that: Google for code to check if a form is loaded

Or adapt code already suggested about to pause code. Start with say a 10s pause. If it works, reduce to 9s & repeat reducing by one second each time until it fails

[GraeagleBill]

Will do. I have some code somewhere that scans the forms collection, so I can use that to check the state of the form.
Also, I have a version of the Pause function laying around somewhere in an old app. Never been a big fan of such indeterminate methods, but they do work. I always have to test them on one of my old "slower" laptops so I don't release a piece of code that works on my high-end desktop but not on a user's machine. (BTW, not applicable here, but there's a ShellWait function out in the wild that works perfectly when one needs Shell processes to complete before proceeding.)
Thanks,
Bill

[Micron]

We're focused on what GB says is required. However, perhaps that is not the case. If this code is in a runtime version, the report has to be opened before it can be output? In other words, there might be other reasons why the error is being raised, one of which could be that the form has a timer event that's interfering. I guess a pause will prove whether or not that is what the issue is.

[GraeagleBill]

If this code is in a runtime version

It is not.

form has a timer event that's interfering

None.

I'll post back later after I get a chance to insert a "Pause". (current time preempted by other forces )

[GraeagleBill]

Micron's version of "Pause" works just fine. However, after a 10 second pause the error occurs as before. Verified first form closed and 2nd of the two OutputTo commands failed with the 2046 error.

I'm stumped!

[Micron]

As always, the current and relevant posted code will provide so much more insight.

[GraeagleBill]

Below is the modified code that reveals the point of failure. I'm concluding, right or wrong, that had the DoCmd.Close acform found the form NOT open I would have received the appropriate error message. As one can see from the screenshot at the point of failure, the 2nd of the two OutputTo commands raised the error.

Private Sub cmdPDFs_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Give UI ability to bulk refresh the floor layouts
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim I As Integer
Dim strErrMsg As String


On Error GoTo Err_Handler


For I = 1 To 4
    strCurFloor = 100 + I
    strfloor = I
    
    strErrMsg = "create pdf North floor layouts: "
    DoCmd.OutputTo acOutputForm, "frmNorthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\North" & strfloor & ".pdf"
    Pause (5)
    strErrMsg = "close frmNorthTower: "
    DoCmd.Close acForm, "frmNorthTower"
    
    strErrMsg = "create pdf South floor layouts: "
    DoCmd.OutputTo acOutputForm, "frmSouthTower", acFormatPDF, "c:\Revel\RDS\FloorPDFs\South" & strfloor & ".pdf"
    Pause (5)
    strErrMsg = "close frmSouthTower: "
    DoCmd.Close acForm, "frmSouthTower"
Next I


DoCmd.Close


Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " in attempting to " & strErrMsg & Err.description
   Resume Exit_Handler


End Sub

[isladogs]

Just out of interest, try escaping that error in the error handler...

If err=2046 Then Resume Next

Sometimes that is enough to 'fix' recalcitrant code