Solved

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

Posted on 2006-10-31
21
1,169 Views
Last Modified: 2007-12-19
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
Comment
Question by:Rance_Hall
  • 8
  • 6
  • 3
  • +3
21 Comments
 
LVL 6

Accepted Solution

by:
yhwhlivesinme earned 50 total points
ID: 17844194
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17844198
you can also use doevents, something like:

do while Sometest = True
    doevents
loop
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 17844206
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 17844296
For doevents:
DoCmd.OpenForm "yourform", acNormal
Do While Application.CurrentProject.AllForms("yourform").IsLoaded
DoEvents
Loop
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17844321
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
 
LVL 8

Author Comment

by:Rance_Hall
ID: 17845062
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17845076
would you mind sharing some of your code with us so we can see exactly what's going on?
0
 
LVL 8

Author Comment

by:Rance_Hall
ID: 17845154
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17845329
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 50 total points
ID: 17845411
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Author Comment

by:Rance_Hall
ID: 17845433
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
 
LVL 8

Author Comment

by:Rance_Hall
ID: 18341230
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18341254
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 18341267
sweet, thanks jim :)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18341279
This is a 50 pt Q...  :-(
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18341327
>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
 
LVL 8

Author Comment

by:Rance_Hall
ID: 18341409
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 18341422
would you mind sharing with us your final solution? for the benefit of the community?
0
 
LVL 8

Author Comment

by:Rance_Hall
ID: 18341622
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 18341674
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now