Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1228
  • Last Modified:

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

I have a function that asks a question with a msgbox.

there is a form that is shown prior to the display of the msgbox that shows the user the data that they can choose from.

Problem I'm having now is that the forms display fields are too small for the data that is being presented.

I need a way to slow down the presentation of the msgbox so that the form that has the answers can be looked at.

I probably should have made that msgbox a form, but it isn't and now I'm stuck.

here is some logic/pseudocode to show you what Ive got:

search open forms for a form with the right name

if found
select form
if not found
open form
end if


value = msgbox(stuff)

This is what Id like to have:

if found
select form
if not found
open form
end if

wait 10 seconds to search the form

time up?

ask if need more time.

if yes
wait again

if no

value = msgbox(stuff)

its the wait stuff thats got me baffled.

any help appreciated.

0
Rance_Hall
Asked:
Rance_Hall
  • 8
  • 6
  • 3
  • +3
3 Solutions
 
yhwhlivesinmeCommented:
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

Then when you call it, just use the line:

sSleep (100)

where 100 is the # of miliseconds that you want it to sleep
0
 
yhwhlivesinmeCommented:
you can also use doevents, something like:

do while Sometest = True
    doevents
loop
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
An easy way would be to set the Timer Interval property of a form to be 1000 * however many seconds delay you want after the form opens, then in the form's Timer event type whatever VBA code you wish.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jerryb30Commented:
For doevents:
DoCmd.OpenForm "yourform", acNormal
Do While Application.CurrentProject.AllForms("yourform").IsLoaded
DoEvents
Loop
0
 
yhwhlivesinmeCommented:
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
0
 
Rance_HallAuthor Commented:
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.

0
 
yhwhlivesinmeCommented:
would you mind sharing some of your code with us so we can see exactly what's going on?
0
 
Rance_HallAuthor Commented:
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.





0
 
yhwhlivesinmeCommented:
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)
0
 
jefftwilleyCommented:
Use Jim's suggestion to put the pop-up message on your pop-up form using a timer. That way the form can remain modal and you can still interact with the msgbox on top.

Private Sub Form_Timer()
    Dim lnVal As Long
    lnVal = MsgBox("Would you like to continue messing around?", vbYesNo)
    If lnVal = vbYes Then
        Exit Sub
    Else
        "Do whatever you plan on doing here"
    End If
End Sub

As Jim said, set your timer interval to check at whatever interval you want. 30000 being 30 seconds.

J
0
 
Rance_HallAuthor Commented:
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.
0
 
Rance_HallAuthor Commented:
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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
yhwhlivesinmeCommented:
sweet, thanks jim :)
0
 
mbizupCommented:
This is a 50 pt Q...  :-(
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Rance_HallAuthor Commented:
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
0
 
yhwhlivesinmeCommented:
would you mind sharing with us your final solution? for the benefit of the community?
0
 
Rance_HallAuthor Commented:
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.
0
 
yhwhlivesinmeCommented:
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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 8
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now