The refresh time of a function is too short which results in an infinite loop.

[Claudiusjerry]

I am writing a system with a timer function for each classroom, when the time is up, I implemented a speaking function that calls
speak "Room" & Id & "is calling!"
and pops up a warning window (vbexclamation) that requires you to press OK to finish the alerting process.
If the data is not deleted, the alerting system will call again.

There is a possibility that all the classrooms may be up at the same time.
When classroom 1 speaks and pops-up window and the user clicks OK, classroom 2 alerts and pops-up window, and then classroom 3, classroom 4......

The problem is, that the system refreshes too fast. The alerting pattern works like this:
My pattern: Classroom 1 -> 2 -> 3 -> 1 -> 2 -> 3 -> 1 -> 2 -> 3.......
Correct pattern: Classroom 1 -> 2 -> 3 -> 4 -> 5 -> 6 -> 7-> ...... -> 15 AND THEN -> 1 -> 2 -> 3 -> 4......(If no data deleted)

The system also contains a rule that if the classroom data is not cleared, that specific classroom will alert again after 30 seconds (I counted).
To clear the data need to double-click the Classroom ID box.

This results in an infinite loop between the first 3 classrooms, classroom 1 alerts and pops-up windows, and when I click OK, classroom 2 speaks and pops up, and then Classroom 3, while the speaking function is calling, I cannot modify or click anything in the system. which my system will be stuck in a 1->2->3 loop and I cannot alert classroom 4-15 and cannot clear data by double click the Id box.

What can I add to the function so that it waits until all classrooms are alert and then returns to classroom 1, together with sufficient responding time for me to clear data?

This is my speaking and window pop-up function.
The concept of the function is Tekst31 = "" when time is running, Tekst31 = "alarm" when time is up, which triggers the function to call and pop-up windows.
Box 22 (Keuzelijst 22) is responsible for showing the ending time, which will become null after clearing data and trigger Tekst31 to return to "" and stop the function of room ID,
as I tested before, clearing classroom 1 will only clear row 1 data, and other classroom functions are still running and able to alert and pop up.

This is the double-click function for clearing data.

Thanks a lot.

 

[The_Doc_Man]

I believe you designed yourself into a little corner there. Let me try to explain.

You didn't mention it so I don't know if you did this - but if you didn't, it is time to do it now. Do you have an accurate timing for the SPEAK function to speak the words you wanted it to say? If not, you can put a function called Timer (see link below) in your loop before and after the SPEAK function to capture start/stop times as two variables. The Timer function returns a SINGLE representing milliseconds since midnight. Subtract the first time from the second one to get elapsed time in milliseconds. Divide the difference by 1000 and that SINGLE is the number of seconds it takes SPEAK to speak. The time is reported as milliseconds even though it really subdivides as clock-ticks (1/60th of a second) - but that should be close enough for the purpose of speech timing.

Timer function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

You said the correct call-out would be "Classroom 1 -> 2 -> 3 -> 4 -> 5 -> 6 -> 7-> ...... -> 15" so 15 rooms? Your 30-second threshold with as many as 15 things to speak will run into trouble if it takes longer than 30 seconds. You even repeat the phrase, which I can imagine would easily take more than 2 seconds. With 15 different things to say, if each phrase takes only 2 seconds, that is 15x2=30 seconds - which is exactly the 30-second repetition limit. If it is even the slightest fraction longer than 2 seconds then your loop can NEVER catch up based on that design.

You reported "while the speaking function is calling, I cannot modify or click anything in the system." Yes, that is probably true. Access is single-threaded and code is run synchronously so SPEAK probably doesn't return to your code until it is finished. If your code is blocked by SPEAK then you will have issues in getting much else done anyway. Note also that you stated you need to take into account how long it takes you to double-click the pop-up. That should figure into your loop timing.

You have a few options. One requires a timing change in the loop. One requires you to keep track of which rooms have been called but for which a response is waiting. To reach your goal you have to decide which of your exacting design goals you will sacrifice.

Timing: Knowing the timing of the SPEAK function for your phrase, you can multiply that by 15 and instead of 30 seconds, make the time equal to 15.01 (or 16) x the SPEAK duration.

Room tracking: Keep an array of integers or Booleans to note that you are waiting for a double-click of a particular pop-up window and don't allow SPEAK to repeat the message verbally until the pop-up is cleared. (I.e. once pop-up 1 is popped up, room 1 cannot trigger anything.)

There are other ways to do this in a variant on queueing the call-outs. That approach would preserve the order in which each room call-out occurred. But I don't want to start that discussion until you know which way you want to go with fixing the impossible timing that you currently have.

 

[Claudiusjerry]

I understand the concept you mentioned, but it seems does not allow any modification during the pause, so I cannot clear the data by double-clicking the clear box.

 

[Claudiusjerry]

I tried calling a sub in the function, but it works sometimes, and sometimes no response, why?

 

[Josef P.]

How is the OutOfTime function started?

Maybe some kind of message queue will help. The forms “report” into a queue (could also be a table) and the messages are processed from there in the order in which they were entered.

 

[Claudiusjerry]

How is the OutOfTime function started?

Maybe some kind of message queue will help. The forms “report” into a queue (could also be a table) and the messages are processed from there in the order in which they were entered.

It just detects the change of the Tekst31 box, if it's not empty, then it will trigger alert and pop up window. While the Tekst31 box will be empty when time is still running, and return " alarm" when tine is up, so that's how a non empty Tekst31 box triggers the function.

Can you give me some examples, I don't really understand the message queue.

 

[Josef P.]

[message queue]
The simplest implementation:
Table with timestamp (or sequence number) and data field for classroom ID.
Some process inserts a new ID into the table as a new data record. This may run within a few milliseconds.
A "queue function" then processes the data from the table.
The record with the smallest timestamp is then always processed and deleted or marked as completed. Then the next one and so on.


Unfortunately, based on the code shown so far and your description, I can't imagine how the time processing takes place.
If the user calls the function by changing it, he is responsible for the correct sequence.
Can you provide a small example application that shows the problem?

 

[Claudiusjerry]

um...

So there is a drop list box for you to select time, if you select 1 minute (00:01), the time function will add one minute to the current time, if current time = current time + 1 minute, then trigger the Tekst31 to perform "alarm", which triggers the OutOfTime() function.

Simply just when current time = count down time + the current time starts counting, then trigger.

So it's like that, I choose 00:01 ( 1 minute in the Alarm column)
which Eind T means End Time, which is that time + 1 minute,
if current time = End Time
Then Nog will show "Alarm"
and OutOfTime function will detect the Nog Box is not empty, trigger an alert, and pop up a window.

This is how it looks like when the time matches.

I don't really understand the (End Time = current time) logic as I am not the one writing this function, but it works fine when I use it.


So the problem is it I want to show a new message box after the first pop-up window, allowing me to clear the Alarm Box, when the Alarm Box is empty, then nog will also return empty, and no OutOfTime function of that Classroom is triggered anymore.

After the First Msg, I added another msgg to ensure clearing data, if Yes then should be clearing all data, but the problem is, when I set multiple rooms with timers and times up at the same time, only the classroom alerts first can delete data with clicking Yes, while others do nothing...



When I implement the data delete function by Sub and double-click the Classroom ID box like below, it works in every classroom.

 

[The_Doc_Man]

Part of your confusion here is that events are in a queue (of sorts) within Access. I have to assume that your problem starts with an an OnTimer event that triggers your alerting process. You are probably treating it like a timer "interrupt" event, but that's not entirely correct. Events in Access can be asynchronous with respect to other events, but no event can actually interrupt another event. (Which is different from error traps, which CAN interrupt event flow.) That double-click that you use is another event that gets put into a queue and has to wait its turn. But when the queue gets clogged up as you described, that turn might not come right away.

At least part of the problem we will have in helping you is because Access is not open-source, so we don't always know exactly what it is doing. But we do know that all VBA code is interpreted linearly, which means that events wait for each other.

Josef P.'s description is probably good for a queue. I would add the requirement that you query the queue before adding anything to it, to prevent having two or three (or twenty) entries for the same room waiting to be acknowledged. Basically, your code has to have "patience" built in to not add another Room #1 alert if a previous alert for Room #1 is still waiting. AND the form that is driving this queue is the one that should call the SPEAK function and show the pop-up. It is the repeated alerting that loads down your system with all those repetitive alerts that come at you so fast that you can't catch up to them.

 

[Claudiusjerry]

Part of your confusion here is that events are in a queue (of sorts) within Access. I have to assume that your problem starts with an an OnTimer event that triggers your alerting process. You are probably treating it like a timer "interrupt" event, but that's not entirely correct. Events in Access can be asynchronous with respect to other events, but no event can actually interrupt another event. (Which is different from error traps, which CAN interrupt event flow.) That double-click that you use is another event that gets put into a queue and has to wait its turn. But when the queue gets clogged up as you described, that turn might not come right away.

At least part of the problem we will have in helping you is because Access is not open-source, so we don't always know exactly what it is doing. But we do know that all VBA code is interpreted linearly, which means that events wait for each other.

Josef P.'s description is probably good for a queue. I would add the requirement that you query the queue before adding anything to it, to prevent having two or three (or twenty) entries for the same room waiting to be acknowledged. Basically, your code has to have "patience" built in to not add another Room #1 alert if a previous alert for Room #1 is still waiting. AND the form that is driving this queue is the one that should call the SPEAK function and show the pop-up. It is the repeated alerting that loads down your system with all those repetitive alerts that come at you so fast that you can't catch up to them.

Then is it possible for me to add a new function after the SPEAK and pop-up of Classroom 1 that deletes all Classroom 1 data, goes to Classroom 2 SPEAK, and pops up and deletes?

Which delete action is added in the sequence preventing an infinite loop?

Or, is it available for deleting row data in SUB or Function that calls in another Function?
I tried several times to delete data as a function or a SUB but it only works once out of ten trials.

 

[The_Doc_Man]

The action to be added to prevent the infinite loop would require that you know what room alerts are already pending. If you are about to add a room but detect that it already has an alert pending, DON'T ADD ANOTHER ALERT. Just dismiss it without action.

If you take Josef P.'s queue approach, the queue will be in a table. It is something you can query (perhaps even with a DCount, to see if you have at least one entry already pending). The only added work is that when you clear the room, you have to delete all the alerts/entries for that specific room, too.

Your problem isn't having an alert event pending. It is adding another (redundant) alert event for the same room before you have had time to clear the first event. Don't do that because (obviously from the discussion) you can't keep up once the events start cascading.

From the way you described this problem, I think it must be something you inherited. I infer that you didn't design or implement this. Am I right?

 

[Claudiusjerry]

The action to be added to prevent the infinite loop would require that you know what room alerts are already pending. If you are about to add a room but detect that it already has an alert pending, DON'T ADD ANOTHER ALERT. Just dismiss it without action.

If you take Josef P.'s queue approach, the queue will be in a table. It is something you can query (perhaps even with a DCount, to see if you have at least one entry already pending). The only added work is that when you clear the room, you have to delete all the alerts/entries for that specific room, too.

Your problem isn't having an alert event pending. It is adding another (redundant) alert event for the same room before you have had time to clear the first event. Don't do that because (obviously from the discussion) you can't keep up once the events start cascading.

From the way you described this problem, I think it must be something you inherited. I infer that you didn't design or implement this. Am I right?

Yes, I understand now, thank you very much.
I will try to delete the alert before moving to the next room.

 

[Isaac]

You can also add pauses manually, i.e.

Sub Pause()
dim dtNow as Date
dtNow = now()
do until now()>dtnow+timevalue("00:00:05") '5 seconds
do events
loop
End Sub

 

[Claudiusjerry]

You can also add pauses manually, i.e.

Sub Pause()
dim dtNow as Date
dtNow = now()
do until now()>dtnow+timevalue("00:00:05") '5 seconds
do events
loop
End Sub

Oh, This works, but why can I not modify anything during that pause, and the things I modified will be shown after the pause...

By the way, how do you write a function that detects the content of a text box, and then clears all row data?
Why I tried Text31 = NULL OR Me.Text31 = NULL OR Me.Text31.value = NULL, all of them cannot clear the data of that specific text box?

 

[The_Doc_Man]

Try using an empty string ("") rather than NULL.

 

[The_Doc_Man]

how do you write a function that detects the content of a text box, and then clears all row data?

You can write something like

IF NZ( TextBox, "" ) <> "" then 
   {text box is not empty}
   {point A}
ELSE
   {text box is empty}
   {point B}
END IF
{point C}

Your question is ambiguous in this sense: In what conditions will you want to clear the data and be more specific about "clears all row data" since Access doesn't use "row" like you would use it in Excel. You have three cases here, represented by points A, B, and C. You would use point A if you found that you needed to clear something because the text box was occupied. You would use point B if you found that because the text box was NOT occupied, you needed to void whatever else you meant by "row." And you would use point C if you actually didn't CARE what was in the text box because you did something else at points A or B.

The question is ambiguous in another sense. WHEN would you wish to execute this test? Are there any other conditions to consider like a deferred action or some other complexity?

@Claudiusjerry - you need to be a bit more specific in the questions you ask because (as you can see from my counter-questions) there can be wrinkles in making things work like you want. Since we aren't mind-readers, we don't KNOW what you want.

 

[Claudiusjerry]

I'm so sorry for giving too little information about my problem, please give me some time to organize my problem.

 

[Claudiusjerry]

So, this is a whole view of my timer table:

I applied a double-click function in the column ID, it will remove all data in the row representing ID 1, which is Room 1.
Also, I applied a function to the column Nog, when time is up,
the logic is: start time (current time) + chosen time (Alarm) = End Time (End T.),
Nog will show the word "Alarm", which normally is "".

Then my function will detect Nog is not "", and then run the following function.

Public Function OutOfTime() As String
    Dim ret As String
    Dim stet As String
    If Me.Tekst31 = "" Then
        ret = "     None"
    Else
        Speak "Room" & Id & "Calling!"
        ret = "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & " ** Room " & Id & "** Calling."
        stet = "  Popped-up"
        Beep
        Msg = MsgBox(ret, vbOKOnly)
       
       
        If Msg = vbOK Then
            Another_test
        End If
       
       
    End If
    OutOfTime = stet
End Function

So, when Nog is "Alarm", it triggers the Else statements and pop-up a window.
Since it is an OKonly message box, it must be able to call the Another_test function in the following.


The following is the clear data function (Another_test)

Public Function Another_test()
    Dim Count As Integer
        Beep
        msg2 = MsgBox("" & Chr(9) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "Clearing data for **Room " & Id & "**?" & Chr(13) & "" & Chr(13) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "Yes for clearing all data, No for keeping Name and Notes.", vbYesNo)
        If msg2 = vbYes Then
            [FirstName] = Null                 'Name column
            [Behandelaar] = Null              'Th. column
            [Note] = Null                          'Note column
            [Keuzelijst22] = Null               'Alarm column
            [Ocupatie] = "Schoonmaken aub"  'Occupation column
            MsgBox "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "**Room " & Id & "** ALL Cleared."
        Else
            [Keuzelijst22] = Null
            [Ocupatie] = "Schoonmaken aub"
            MsgBox "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "" & Chr(32) & "Alert Cancelled Only.", vbOKOnly
        End If
End Function

Then, the whole logic is, that when time is up, the specific room will alert, let's say Room 1,
The system will speak -> beep -> pop up a window -> showing that Room is calling -> the system pops up a window asking to delete room data, clicking whatever options -> the data (Alarm) will be deleted and reset to Null.
(Only deleting the data in the Alarm of that specific room will stop the system from alerting again after refresh.)

Below is a correct trial:

->

->

->

The problem is, that when I use double-click on the ID box, it deletes all data in that specific room (a whole row), when I change it into a function and call it inside the OutOfTime function, it will contain bugs.

Here are different fail situations:
1. It can successfully delete all first-room data, but when the rest of the rooms are alert and ask for deletion, all of them do nothing, meaning only room 1 can be deleted.
2. All of them cannot be deleted.
3. When more than 2 rooms time up at the same time when I ask for deleting the alerting room data, it deletes another room randomly but not the room alerting(the smallest ID room/the highest row room)

As you can see from the image provided, it is situation 3, when room 1 is alerting, it asks for deleting, and I clicked Yes. It deleted all room 2 data.

One thing I discovered during multiple trials, I found out the clearing function is not stable on focusing the room that is alerting currently, and I have to wait for the system to refresh again (30 seconds) to ensure it can continuously delete the next room.
I can delete the first room when the time is up, and then if I click delete in room 2 (without double-clicking the ID box), it will not do anything, but after I delete room 1, I immediately switch to the design view of the table, and wait for the system to refresh, when I change back to the form and choose to delete for room 2, it works, so I don't understand why.

Therefore, how am I able to write a proper function that focuses only on the room that is alerting for deletion?
Is it a must to wait for the system to refresh for 30 seconds to delete the row data after alerting and clicking OK without double-clicking the ID box?

 

[Josef P.]

How do you ensure in your procedures that they run with the right data set?

 

[Gasman]

Better start using Option Explicit at the top of every module.
You are not using it where Another_Test is located.

You will need to add it manually for every module already created.
For new modules, you can set it in Options in the VAB window. Tools/Options

Option Explicit statement (VBA)

Office VBA reference topic

learn.microsoft.com