Delay.

[stephen c]

Hi. I am trying to create a delay of longer than a couple of seconds, and came across this solution. It says to place this in the declarations:

Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliseconds As Long)

and then this in your regular code to call it (assuming 3 minutes):

Call Sleep (180000)

OK, so I think I get it. Access is using a Windows sleep functionality. No work will be done in Access for 3 minutes. I have no problem with that. What I don't understand is where the heck to put the "declaration." It's just something I've not had to do yet. I created a module in the database called Sleep, and inserted that code. Then, when I tried the "Call...." it doesn't work.

Any explanation would be appreciated.

Thanks!

Stephen the newbie-ish

[June7]

"Private" means the procedure can be called only from the module it is declared in.

Place the declaration in a general module but change the Private to Public (or just remove Private) then the procedure can be called from anywhere in the database.

[andy49]

Why not set a variable stoptime as the current time and then loop until
current time > dateadd("s"stoptime",180)

Or similar.


Sent from my iPhone using Tapatalk

[stephen c]

I tried June7's recommendation, and still get this error:
Compile error: expected variable or procedure, not module.

Here is exactly what I had done.
1. Create a module, named it Sleep.
2. The total contents of that module are:
Option Compare Database
Public Declare Sub Sleep Lib "kernel32" (ByVal lngMilliseconds As Long)
3. In the code called by the button I have:
Call Sleep (6000)

Aaand error.

Any ideas? I'm sure I'm doing something basic wrong.

Thank you!

Stephen.

[stephen c]

Hi, Andy. Thank you for your reply. I have been told not to do that, because it absorbs resources, particularly for longer wait times.

[andy49]

Are you running 32 bit or 64 bit windows


Sent from my iPhone using Tapatalk

[andy49]

Change your sleep module so it's called something else. It's confusing access. The name of the module you store the sub in doesn't matter


Sent from my iPhone using Tapatalk

[stephen c]

Hi Andy. Changed the name. Im on 64 bit. Aargh. Should that then say kernel64?

[andy49]

No add ptrsafe before the word sub


Sent from my iPhone using Tapatalk

[andy49]

see this link to help

[stephen c]

Thank you guys for all of your help. Now, the delay works! (Andy, I didn't add prtsafe yet, because I'm not sure exactly where. I did so in the declarations and got error)

Now, the odd thing is that the thing I'm waiting on doesn't execute until AFTER the delay! See full code below. What is supposed to happen is the program checks a record for the presence of the checkbox=true. If so, it incriments i, displays the new value in the textbox checkqty, then a delay, then on to the next record to check for i. I'm using the msgbox just as a test feature. I know this is a ridiculous program. In the future, "check for i" will be replaced with a call to an outside JAR, so this is just for mock-up.

I have no idea why the value would wait until after the delay to display! Thanks, folks!

--------------------

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM consolidatorlisttable;")
Dim i As Integer
Dim starttime As Date
i = 0
rs.MoveFirst
Do While Not rs.EOF And Not rs.BOF
If rs!portalsend = True Then i = i + 1
rs.MoveNext
Loop

Me.checkqty = i 'displays the value in i in the textbox checkqty

Call Sleep(15000)

' starttime = Now
'Do While DateDiff("s", starttime, Now) < 60
'Loop

MsgBox "test message"
rs.Close
Set rs = Nothing

[andy49]

Thank you guys for all of your help. Now, the delay works! (Andy, I didn't add prtsafe yet, because I'm not sure exactly where. I did so in the declarations and got error)

Now, the odd thing is that the thing I'm waiting on doesn't execute until AFTER the delay! See full code below. What is supposed to happen is the program checks a record for the presence of the checkbox=true. If so, it incriments i, displays the new value in the textbox checkqty, then a delay, then on to the next record to check for i. I'm using the msgbox just as a test feature. I know this is a ridiculous program. In the future, "check for i" will be replaced with a call to an outside JAR, so this is just for mock-up.

I have no idea why the value would wait until after the delay to display! Thanks, folks!

--------------------

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM consolidatorlisttable;")
Dim i As Integer
Dim starttime As Date
i = 0
rs.MoveFirst
Do While Not rs.EOF And Not rs.BOF
If rs!portalsend = True Then i = i + 1
rs.MoveNext
Loop

Me.checkqty = i 'displays the value in i in the textbox checkqty
docmd.requery "checkqty" might help
Call Sleep(15000)

' starttime = Now
'Do While DateDiff("s", starttime, Now) < 60
'Loop

MsgBox "test message"
rs.Close
Set rs = Nothing

Try requerying the textbox to pick up the new value

[June7]

Don't think textbox can be requeried, at least I get 'does not support property or method' error. What exactly would be requeried in a textbox? It does not have a query like a combobox or listbox or form. A form can be refreshed but that doesn't help either. Interesting, I set a breakpoint on the Sleep() call and the value immediately shows in the textbox.

I don't think there is a fix for this.

Why do you need delay?

[stephen c]

Andy and June, first, thank you so much for your help. Part of my reason for wanting to understand the delay, and how things display is general learning. That said, this is a simplistic example of what I'm trying to build. The end result is that each loop will, instead of increasing the value of i and displaying it, will actually launch a JAR. That JAR needs time to run before the next one is launched (if the box is checked.) A better solution would be to have the folder with the JAR in it hold an app.bat.end file. The JAR will delete that file while running, and then recreate it when it closes. There would be no timing to consider, IF I could figure out how to get Access to test for the presence of that file. I think I'll start a new tread for that one!

[andy49]

Np Stephen. Access can test easily if a file exists. Just search this forum.


Sent from my iPhone using Tapatalk