Pausing/Interupting Loop to Edit

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

We are developing a database to import a number of different files into a
single source database table for tracking the number and durations of holds
on accounts.

We have developed the import engine and a form to edit duplication of
records. Each work well on their own, but the problem is that we need to
insert the form into the middle of the import loop and this creates the
problem...

1) How can we in the middle of this loop pop the form, pause the loop until
user interface is done, and then continue on with the loop from that point?

2) I also thought that rather than pop the form everytime through the
import loop, to do a conditional to check for the presence of these
duplicates and only if TRUE pop the form. Any suggestions?

Thank you in advance for your assistance.

MJ
 
You can cause the loop to suspend by making the form modal. Once the form is
open, the code will stop until the form is closed. As to determining if
there are duplicates, I can't answer that question based on the information
you provided.
 
Klatuu,

I tried making the form modal as you suggested, but no joy on stopping the
code.

I will include some of the code to see if this helps:

====================

Private Sub CmdImport_Click() 'Import Files -- using the dates input on the
form

On Error GoTo Err_CmdImport_Click

Dim dbMyDB As Database
Dim EndDate As Date, PrevDate As Date
Dim StartDate As Date
Dim d As Integer, DaystoImport As Integer
Dim DateComplete As String, Q As String
Dim strEnd As String, strStart As String
Dim strStart2 As String

Set dbMyDB = CurrentDb
DoCmd.SetWarnings False

PrevDate = DLookup("When", "LastDateDone")
StartDate = PrevDate + 1
EndDate = Me.EndDate

If (EndDate < StartDate) Then
MsgBox " Please re-enter a NEW 'End Date' that is EITHER same as
" & vbNewLine & " the 'Start Date' (listed above) OR AFTER.
", vbCritical, _
"DB: Please Re-Enter the End Date"
Else
DaystoImport = (EndDate - StartDate) + 1
For d = 1 To DaystoImport
DateComplete = Format(DateAdd("d", (d - 1), StartDate),
"mm/dd/yyyy")
strStart = Format(DateAdd("d", (d - 1), StartDate), "yymmdd")
strStart2 = Format(DateAdd("d", (d - 1), StartDate), "mmdd")

DoCmd.OpenQuery "Delete T1"
....
'MsgBox "Tables cleared. Importing files"

'importing file - File #1
DoCmd.TransferText acImportDelim, "File1 Import Specification", _
"File1", "\\share\dir\File1_" & strStart & ".txt", False, ""
....
***
DoCmd.OpenForm "Frm: DupChkr", acNormal
MsgBox "Duplicate Check complete, press OK to continue",
vbOKOnly, _
"DB: Duplicate Check"
***

DoCmd.OpenQuery "Q1"
...
DoCmd.OpenQuery "qry: Step5"
Next d

DoCmd.OpenQuery ("qryUpdate LastDateDone Data")

MsgBox "File Importing Completed", vbInformation, "DB: Import Files"
DoCmd.SetWarnings True

End If

Exit_CmdImport_Click:
Exit Sub

Err_CmdImport_Click:
MsgBox Err.Description
Resume Exit_CmdImport_Click

End Sub

===============

Form DupChkr (bracketed by *s above) is the DoCmd to pops the form, it does
open the form, but it sails onto the MsgBox following the open form line.

What are we missing?

Thanks again for your inputs.
MJ
 
Use acDialog as the window mode

DoCmd.OpenForm "Frm: DupChkr",acNormal,,,,acDialog

That should stop all processing in the calling code. It also makes menubars
unavailable.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks, John. It is been a while since I used this and remembered it
incorrectly. That's what happens when you suffer from CRS.
 
Yes, I tried that without any luck. The form popped, but the process went
right on past that point without stopping.

In researching in another of the Discussion groups: Access Database Forms
Coding, I found a discussion (Pausing Execution of Code In a Form,
12.15.2004) that got me close by using " DoCmd.OpenForm "Frm: DupChkr", , , ,
, acDialog ", but in the Dialog mode some elements of the popup form are not
available to the user. Since this form is an edit point, the User needs to
be able to use ALL facits of this form (including the search function) as
designed.

Dave, I know that it has got to be something extremely simple that we are
missing. Do you have any other ideas?

Dave, thanks again for you time and assistance with this one.

MJ
 
How about making it just a normal form and moving all the code below the
OpenForm to the search form? Once the work is done in the search form, put
the rest of the code in the search form's unload event.
 
You could always modify the code to check if the form was open and if the
form is open loop until the form is closed

***
DoCmd.OpenForm "Frm: DupChkr", acNormal

While CurrentProject.AllForms("Frm: DupChkr").IsLoaded = True
DoEvents
sSleep 1000 'API Code see below - pause for 1 second
Wend

MsgBox "Duplicate Check complete, press OK to continue", vbOKOnly, _
"DB: Duplicate Check"
***


'***************** Code Start *******************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub
'***************** Code End *********************
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top