Suggestion on pausing loop

[vbc22]

Hi,

What would you suggest for pausing a loop that's in process?

I've got a loop constructed that imports records into a table. When it's a new record I want it to pause to let me create a new entry into a separate table thereby ascertaining a new ID, which upon resuming the loop, will be used to import the new record.

Here's my pseudo code...

Public Sub ImportExample()
    For x = 1 To files
    
        If IsNull(files.ID) Then
            
            ' Here's where I need something something
            ' to stop this loop from continuing while
            ' I do something else to make available an
            ' ID for the loop to carry on.
            
        End If

        ' a call to routine that processes import.
        importRecord (x)
    
    Next
End Sub

What I'll want to do is stop the loop, open up a form, enter information, and click save. That then will create the ID needed. Then come back, update that import record with the ID and have the loop resume.

What I've done already, as an alternative, is set it aside by importing it to another table which I then process manually. I'm looking for a solution that will expedite this process by having it semi-automated, after all it's only missing the ID. As well, most records should import automatically, thus only the new ones require this attention.

I'm not sure if DoEvents() is the solution here. If so, I don't know how the syntax would work.

I was thinking of the Assert method? That plausible?

I'd like to know what is available to make this work. Any help would be appreciated.

Regards.

 

[PHV]

Why not open the form as modal (acDialog) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbc22]

The loop continues execution after the form (Dialog) is opened. I tested that.

I just did a search after my first posting (sorry, I know should be doing that first) and found the following posting:

http://www.tek-tips.com/viewthread.cfm?qid=716088

The author was looking for the same solution. I'm working with the suggestion there provided by mstrmage1768 and vbajock

 

[PHV]

How do you open your Dialog form ?
The form shouldn't be in datasheet view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbc22]

Hi PHV,

Thank you for your continued responses here and on other threads I've posted.

The dialog form I used above had its Modal property set to Yes in the Properties window.

So when I invoked it during loop execution, it opened but still continued processing. So it didn't halt the loop.

I did however come up with a solution based on the above noted thread link I posted.

As a test example, I created 2 forms, each with a button.

Code on form1 that performs the loop.

' Boolean variable for continuation switch.
Public resumeFlag As Boolean

Private Sub btnBeginLoop_Click()
        
    ' Initialize loop continuation
    resumeFlag = False
    
    ' Loop example.
    For x = 1 To 10
        
        ' Pause execution
        If x = 5 Then
        
            ' Open form for offline activity.
            DoCmd.OpenForm "form2"
            
            ' Additional Do...Loop that waits
            ' until resume flag is true.
            Do
                If resumeFlag = False Then
                    ' Needed this in order to gain
                    ' access to the second form.
                    DoEvents
                End If
                
            Loop Until resumeFlag = True
            
        End If
        
        ' Time check when loop iterations occurred.
        Debug.Print x & " time " & Now()
    Next
End Sub

Code on form2 that would be equivalent to my required form for ascertaining a new record ID.

Private Sub btnClose_Click()
    
    '-----------------------
    '
    ' Do stuff here...
    '
    '-----------------------
    
    ' When done, set resume status to
    ' true to end waiting Do...Loop
    Forms!form1.resumeFlag = True
    
    DoCmd.Close acForm, "form2"
    
End Sub

The only problem I see with this is that CPU usage jumps up to 100% while the second form is open because that Do...Loop is cycling. That's the price you pay for I guess because I'm using that loop to wait.

Is this code ok? Or is there a better approach?

Any comments would be appreciated.

 

[vbc22]

Sorry, forgot to answer your question...the dialog form was not in datasheet view.