How to insert a "delay" or "pause" in access 97 code?

you can also use doevents, something like:

do while Sometest = True
    doevents
loop

For doevents:
DoCmd.OpenForm "yourform", acNormal
Do While Application.CurrentProject.AllForms("yourform").IsLoaded
DoEvents
Loop

if you want code to pause while a form is loaded it's best to open that form like this:

DoCmd.OpenForm "frmwhatever", acNormal, , , , acDialog

opening it using the dialog parameter will pause the code until it's closed

I tried the sleep function as suggested by yhwhlivesinme in the first response to my question.

test results:

1.  if the form is opened because the if statement prior to the sleep decided it needed to open the form, then the screen is not redrawn until the first sleep command is over.

2.  rest of app not usable while sleeping.  other forms still need to work while the system is silently waiting for one section of code to resume.

Other questions:

According to the documentation the "DoEvents" allows the system to go ahead and process whatever it needs to process and when the queue is empty then the job returns to active processing.

I just dont see how to fixate that on a time, I think it would be random based on the size of the os queue.

which might not be much on a particular machine.

would you mind sharing some of your code with us so we can see exactly what's going on?

sure here is the code in its entirety

I added a note as to my problem area.

<begin code>
Public Sub getcslots(fulfillmentid As Integer, orderno As String, product As String, warehouse As String, casesneeded As Integer, cslotcancel As Boolean)

Dim strsql As String
Dim cslotno As Integer
Dim cslot As String
Dim found As Boolean
Dim frm As Form
Dim db As Database
Dim RS As Recordset
Dim item As Integer
Dim CASES As Integer
Dim casessofar As Integer
Dim listcount As Integer
Dim varI As Integer
Dim casesleft As Integer
Dim confirmcancel As Integer
Dim waitsec As String

found = False
Set db = CurrentDb
casesleft = casesneeded

For Each frm In Forms
If frm.Name = "cslotdetail" Then
found = True
End If
Next frm

If found = True Then
DoCmd.SelectObject acForm, "cslotdetail", False
Else
Call buildcslotlist(product, warehouse)
End If

<this is the problem area>

getlot: cslot = InputBox("Please enter the CS Lot # for the release of product " & product & " on order " & orderno & " from warehouse " & warehouse & Chr(10) & Chr(13) & "You need " & casesleft & " cases.", "CS Lot")

If IsNull(cslot) Or cslot = "" Then
confirmcancel = MsgBox("Are You sure you want to cancel the cold storage lot selection?", vbApplicationModal + vbYesNo, "Cancel CS Lot Selection")
If confirmcancel = vbNo Then
GoTo getlot
Else

cslotcancel = True
Exit Sub
End If
End If

'validate cslot against form list
found = False

For varI = 1 To Forms!cslotdetail!cslotlist.listcount
If Forms!cslotdetail!cslotlist.Column(0, varI) = cslot Then
item = varI
End If
Next varI

If item = 0 Then
MsgBox "The cold storage release lot you entered can not be used for this product, please try again.", vbApplicationModal + vbOKOnly, "Invalid CS Lot number"
GoTo getlot
End If


'pick min of cslot available or product cases
If CInt(Forms!cslotdetail!cslotlist.Column(1, item)) < casesleft Then
CASES = Forms!cslotdetail!cslotlist.Column(2, item)
Else
CASES = casesleft
End If

getcases: CASES = InputBox("Please enter the case count for the release of product " & product & " from lot number " & cslot, "Cases Released", CASES)

If CASES > casesleft Then
MsgBox "The case count you entered will release more of this product than is needed.", vbApplicationModal + vbOKOnly, "Quantity Exceeded"
GoTo getcases
End If

'insert new record, but get record id first
strsql = "select max(CS_RELEASES.CSRELEASE_ID) as maxrelease"
strsql = strsql & " FROM CS_RELEASES"

Set RS = db.OpenRecordset(strsql)

If IsNull(RS![maxrelease]) Or RS![maxrelease] = 0 Then
cslotno = 1
Else
cslotno = RS![maxrelease] + 1
End If

strsql = "INSERT INTO CS_RELEASES (CSRELEASE_ID,REFERENCE_NO,FULFILLMENT_ITEM,CS_LOT,CASES,WAREHOUSE) VALUES ("
strsql = strsql & cslotno & ",'" & orderno & "'," & fulfillmentid & ",'" & cslot & "'," & CASES & ",'" & warehouse & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True



casessofar = casessofar + CASES

If casessofar < casesneeded Then
casesleft = casesleft - CASES
'update the cslotdetail display
Call buildcslotlist(product, warehouse)
GoTo getlot
End If

If casessofar = casesneeded Then
'shut it down
DoCmd.close acForm, "cslotdetail", acSaveNo

Exit Sub
End If

End Sub

<end code>

the "buildcslots" function collects the needed data and opens a form.

Notice how this code continues to operate after the form is opened.

the window on the form that is opened by the code is large enough to accomodate our data for all but the busiest time of the year.  We are a small turkey processing plant, in the US and you can bet that this is the busiest time of the year for us.

anyway, the code works as long as the window on the opened form can hold all the needed data, but if you would have to use a scroll bar to see all of the list of data, then is when the problem occurs.

with the input box open, the form is not usable and none of the naviagation buttons on that form work.

Now if this was a msgbox instead of an inputbox then the msgbox could be open with flags that would allow or disallow other forms to continue to function, inputbox has no such extra control.

I'd be happy to help further, but there's a reason why we have a points system at Experts Exchange.  I really don't think that analysing that amount of code is worth 50 points.  If you'd rather not up the points I'd suggest you look at:

1.  Using a DoEvents loop
2.  Opening the form with the dialog parameter (see post above)

I have no problem upping points if that is warranted, I always start new q's at 50 and raise accordingly.

I'm going to continue testing some of the other ideas and see if one of them works the way I want it to.

I apologize for the abandoned question, I totally forgot about it.

Basically none of the suggestions worked the way they needed to, so I've been forced to do a complete rewrite of that section of the program in order to get around that one issue.

I have no objections to splitting the points between jimhorn and jeffwilley, but they need a chance to voice any objections themselves

Recommend adding yhwhlivesinme's first comment to the CV recommendation.

There is more than one way to pull this off, and all are equally good.

sweet, thanks jim :)

This is a 50 pt Q...  :-(

>sweet, thanks jim :)
np

>This is a 50 pt Q...  :-(
I know, but EE discourages experts making comments to askers regarding the point value of their question, unless it relates to a specific MA violation such as max 500 points.

I don't make the rules here, I only enjoy them.

I bumped the point value to 150, and split 50 each to three who had an answer, yes all were equally good, all had pros and cons, but in the end, they formed the inspiration for a different direction.

Thanks all

would you mind sharing with us your final solution? for the benefit of the community?

In the original design I had "print report" button with a check that made sure that the supporting data was present before the report printed, if it wasn't, then it had to go through a loop getting values and adding them to a database and then rechecking the condtion before actually printing the report.

The problem for me was that whenever the checkloop asked for data, it needed to wait till there was data before continuing I couldn't make the loop stop processing without breaking the usability of the other forms in the app.

so the final solution was to design a new form that could get the needed extra information, now when the "print report" button is pressed, the check is run ONCE, and either the form is opened, or the report is printed based on the outcome of the test.

code stops processing and no loops are required.

the form close function on the new form has the same test in it and so if the test passes when the form is closed, then the report is printed.

I would have been ok with the original idea if there had been an effective way to only stop processing on the form that called the loop, and continue as normal on all the other open forms.   But I couldn't find a way to just pause the form, but the symptoms indicated to me that access as a whole was being caught by my pauses no matter how I tried to implement them, this was not acceptable.

Thanks Rance, sorry we couldn't be more help.  I thought that the do events code would stop running code on that form only, but I may be wrong on that.