How can I use IIF to output a message box with a beeping sound?

[Claudiusjerry]

I have a form that contains a timer, which when the time reaches "00:00", I want the IIF to be able to detect and pop out a window with system sound/ beeping sounds
How can I detect "00:00" after counting down (the time in the form changes every second), and pop out windows with continuous beeping sounds?
What I mean is unless you close the window, the beeping sound will continuously release to remind the users.

This is my simplified code in box Text29

(This code works properly)

The pop-out window works well, then I try to add beeping sounds to the window:
I tried to modify a lot of ways like
=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", vbCritical), "None") OR =IIF ( Text31 = "00:00", MsgBox (" Time's Up!", vbExcalamtion), "None")

It returns with "Name?" in the text box (Which is the error I believe)

Even if I write my codes as a function and I call it, it cannot be able to call back...
=IIF ( Text31 = "00:00", Text29_AfterUpdate() , "None")

(I found the speak function on the internet and it works properly after testing)

Also, I tried other built-in functions but still not correct.
= IIF ( Text43 = "00:00", Beep(), "None") OR DoCmd.Beep OR DoCmd.Beep() Or RunMacro etc...

When I tried calling Text29_DblClick, it worked properly, the system spoke and windows popped out, but what I want is for it to detect the change into "00:00" and calls pop out, not double-clicking the box for the trigger.

When I tried using Text29_Change/OnChange/OnDirty/Dirty/AfterUpdate.....
All of the call and beeping functions don't work, I don't know what is the problem.

Are there ways to perform this function and call it successfully?

 

[Gasman]

I cannot believe a time control holds a string?
Try 0 instead.

 

[Claudiusjerry]

I cannot believe a time control holds a string?
Try 0 instead.

"00:00" is acceptable, I tried setting it as DblClick and it works. The problem is that the AfterUpdate/ OnDirty function does not work.
But still thanks a lot.

 

[Gasman]

I believe those events are for user interaction. They do not run if the control is modified by vba. Same with the change event.
Why can't the code go in the timer event?

 

[cheekybuddha]

I tried to modify a lot of ways like
=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", vbCritical), "None") OR =IIF ( Text31 = "00:00", MsgBox (" Time's Up!", vbExcalamtion), "None")

It returns with "Name?" in the text box (Which is the error I believe)

You can not use VBA constant names in an expression.

Try:

=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", 16), "None")
OR
=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", 48), "None")

vbCritical = 16
vbExclamation = 48

 

[The_Doc_Man]

Even if I write my codes as a function and I call it, it cannot be able to call back...
=IIF ( Text31 = "00:00", Text29_AfterUpdate() , "None")

You cannot use that implied call in an IIF because Text29_AfterUpdate() is a sub, not a function, and therefore has no associated value. It is not that it is returning 0; it doesn't return anything at all. VBA should return an error on the attempt. Do you have warnings disabled? Your other attempts at placing functions or subs in the IIF's 2nd and 3rd arguments suffers from the same problem. IIF wants values for its TRUE and FALSE alternatives. Commands and actions are not values. See also David's response (CheekyBuddha).

It returns with "Name?" in the text box (Which is the error I believe)

That error means it couldn't find some object or variable that you named.

 

[Claudiusjerry]

You can not use VBA constant names in an expression.

Try:

=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", 16), "None")
OR
=IIF ( Text31 = "00:00", MsgBox (" Time's Up!", 48), "None")

vbCritical = 16
vbExclamation = 48

I tried the expression you provided, but why it doesn't have any program sound? I can only receive program sound when I set VbCritical in the function but not in the expression.

So, MsgBox constants can only perform different message box formats but with no sounds?

 

[Claudiusjerry]

You cannot use that implied call in an IIF because Text29_AfterUpdate() is a sub, not a function, and therefore has no associated value. It is not that it is returning 0; it doesn't return anything at all. VBA should return an error on the attempt. Do you have warnings disabled? Your other attempts at placing functions or subs in the IIF's 2nd and 3rd arguments suffers from the same problem. IIF wants values for its TRUE and FALSE alternatives. Commands and actions are not values. See also David's response (CheekyBuddha).



That error means it couldn't find some object or variable that you named.

So, if the IIF is fulfilled, why can't I apply my function to the expression as TRUE and FALSE statements? like =IIF( Text31 = 0, function 1(), function2())

 

[theDBguy]

So, if the IIF is fulfilled, why can't I apply my function to the expression as TRUE and FALSE statements? like =IIF( Text31 = 0, function 1(), function2())

Hi. Welcome to AWF!

Are you able to share a sample db to demonstrate your problem?

 

[Claudiusjerry]

Hi. Welcome to AWF!

Are you able to share a sample db to demonstrate your problem?

I'm afraid I can't, I am helping others to do this task which the file has set with permission and authentication also the owner doesn't allow me to share the file.

but, I can try to tell my problem more concisely,
Let's say
I am expecting to write an expression of IIF that detects immediately when the value in another box changes to "alarm", it will pop out a window reminding the user time is up, which I would like to use an exclamation or critical message box as it can provide system sounds.
However, when I use both methods in the expression, the system sounds are missing, while when I'm using vbcritical or vbexclamation in my function codes, it carries system sounds.

It is a simple expression of my code:
= IIf ( [Text31]="alarm" , MsgBox("Testing", 16), "None")
Suppose 16 = VbCritical and it carries system sounds

So, I am not sure whether using a message box in expression provides system sounds or not...

 

[theDBguy]

I'm afraid I can't, I am helping others to do this task which the file has set with permission and authentication also the owner doesn't allow me to share the file.

but, I can try to tell my problem more concisely,
Let's say
I am expecting to write an expression of IIF that detects immediately when the value in another box changes to "alarm", it will pop out a window reminding the user time is up, which I would like to use an exclamation or critical message box as it can provide system sounds.
However, when I use both methods in the expression, the system sounds are missing, while when I'm using vbcritical or vbexclamation in my function codes, it carries system sounds.

It is a simple expression of my code:
= IIf ( [Text31]="alarm" , MsgBox("Testing", 16), "None")
Suppose 16 = VbCritical and it carries system sounds

So, I am not sure whether using a message box in expression provides system sounds or not...

Have you tried that expression? I don't recall it's possible to call a function within an IIf() function though. Maybe you meant to use the If/Then statement? This is why I wanted a sample file, so I can see exactly what you were trying to do and where you were using that expression. If you can't share the original file, can you create a mockup version, so we can clearly see what the problem is? Thanks.

PS. Also, part of the reason why I wanted to see a working file is I wanted to see how the textbox value gets change to 00:00. I was wondering if you were using a Timer event.

 

[Gasman]

So create a db with just that code needed to see the issue, especially as this is not even data related.

 

[cheekybuddha]

I found the same result using Acc2007 - a msgbox called from within an IIf() expression showed but produced no sound.

I could get the desired result (ie sound along with MsgBox) by creating a public function in the form's module, eg:

Function OutOfTime() As String

  Dim ret As String
 
  If Me.Text31 = "00:00" Then
    ret = "Time's up!"
    MsgBox ret, vbCritical
  Else
    ret = "None"
  End If
  OutOfTime = ret

End Function

Then use as the expression in the ControlSource:

=OutOfTime()

 

[Claudiusjerry]

I found the same result using Acc2007 - a msgbox called from within an IIf() expression showed but produced no sound.

I could get the desired result (ie sound along with MsgBox) by creating a public function in the form's module, eg:

Function OutOfTime() As String

  Dim ret As String
 
  If Me.Text31 = "00:00" Then
    ret = "Time's up!"
    MsgBox ret, vbCritical
  Else
    ret = "None"
  End If
  OutOfTime = ret

End Function

Then use as the expression in the ControlSource:

=OutOfTime()

Thanks alottttt!!!!!!!!!
It successfully releases a system sound.

Just curious, is it impossible to release a system sound until the user clicks OK?
I tried it before by using a loop, but the system lagged and turned off.
Can I write the loop as this?

 

[theDBguy]

Thanks alottttt!!!!!!!!!
It successfully releases a system sound.

Just curious, is it impossible to release a system sound until the user clicks OK?
I tried it before by using a loop, but the system lagged and turned off.
Can I write the loop as this?

View attachment 115128

You would have to use a custom form rather than a MsgBox to allow for continuous loop in your code.

 

[Claudiusjerry]

Thanks alottttt!!!!!!!!!
It successfully releases a system sound.

Just curious, is it impossible to release a system sound until the user clicks OK?
I tried it before by using a loop, but the system lagged and turned off.
Can I write the loop as this?

View attachment 115128

Or maybe I can apply the speak function before the message box pops up, which also helps to remind the users.

Thank you for helping me with this issue!
Thanks a lot. @cheekybuddha @Gasman @theDBguy @The_Doc_Man

 

[theDBguy]

Or maybe I can apply the speak function before the message box pops up, which also helps to remind the users.

Thank you for helping me with this issue!
Thanks a lot. @cheekybuddha @Gasman @theDBguy @The_Doc_Man

Good luck with your project.

 

[cheekybuddha]

Untested, but you could try something like this:

Function OutOfTime() As String
  Dim ret As String
 
  If Me.tekst31 = "" Then
    ret = "None"
  Else
    ret = String(3, Chr(9)) & "Alarm!!!"
    Me.TimerInterval = 1000    ' 1 second
    MsgBox ret, vbCritical
    Me.TimerInterval = 0       ' Turn off timer when MsgBox is closed
  End If
  OutOfTime = ret
 
End Function

Private Sub Form_Timer()
  Beep
End Sub

 

[The_Doc_Man]

Just for clarification:

IIf function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

The 2nd and 3rd arguments must be a value or expression. (See table under heading SYNTAX.)

That means that you could call a function that returns a value but not a sub or object-method that does not return a value.

MsgBox function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Because MsgBox is a FUNCTION and not a SUB, it DOES return a value and therefore could participate in the required expression for the 2nd and 3rd arguments of IIF.

 

[rikstick]

I recently did something very similar to this in Excel using VBA. When my timer reached 120 seconds, I added Beep to my code. I converted the time value using the CDbl() method which converted the time value back to a double value.