Add sound when subtracted value = 0 in subform

[neelsfer]

I have a timing program. My one client want's a new feature.
The cyclist do laps. The number of laps are predetermined ie 2. When the rider completed the 2 laps, i subtract the original number (2) from number of laps completed ie 2, and if its 0(zero) the person is finish.
When this quantity is 0, i want a beep sound to go off. I get it right, but it then also beep should i press the refresh button or go in and out of screen.
Code is in "on current"

Dim iRetValue As Long
  If me.Lapsremain = 0 then iRetValue = sndPlaySound(CurrentProject.Path & "\beep6.wav", SND_ASYNC)

I need it to only beep when a new record is added and the subtraction value comes to =0 This value is displayed in a subform (RaceresultsSF) next to the subform (RaceTimingSF) where capturing is done.
Please see the attached picture so see where the zero is displayed.

any suggestions

[TheSmileyCoder]

The problem comes from using the in-correct event. The On-Current is fired when moving between records or to a new record. You probably need to use either the Forms AfterUpdate/BeforeUpdate, or possibly a textbox' before/after update. It depends on how you input the laps I suppose.

[neelsfer]

I have tried all options.
I get the wav files to play when you have 1 lap or 0 laps left, but it continue thereafter to beep if you press refresh or go in or out of screen.
the code i used is in "on enter" in racenumber field in RaceTimingSF subform.

Dim iRetValue As Long
[Forms]![racesetupXCf]![RaceresultsSF].Requery
If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 1 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bell.wav", SND_ASYNC) Else
 
If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 0 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)

The requery above is used to update another subform on the same screen with calculated times
see attached example and read instructions on screen pls.
please help

[TheSmileyCoder]

Well I've spend quite a bit of time looking at your database, but to be honest its simply to messy for me to spend more time on. Half the comments are just old that you have commented out, I have no idea what to makes heads or tails off.

Only thing I can say is that the ENTER event is NOT the correct event to use, since it will fire each you enter the record and that does not seem to be what you want. However since your basing your testing of whether or not to play the sound on a field in another form, you have to take that into account. The AfterUpdate of the control will not work, unless you use the same event to update the other form. (Before doing your check)

[neelsfer]

thx TheSmileyCoder so far.
I get it to work but is seems to continue to repeat the last sound produced when the lapsremain field in the RaceresultsSF subform was either 0 or 1, when i press refresh or enter the form from another.
I have just given you important parts of the application and deleted a lot of the buttons and functions before posting it, to reduce the size.

i think the problem is in the racenumber field of the RaceTimingSF subform when pressing enter, with this code

DoCmd.SetWarnings False
[Forms]![racesetupXCf]![RaceresultsSF].Requery
If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 1 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bell.wav", SND_ASYNC) Else
If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 0 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
KeyAscii = 0
Cancel = True

Sorry I forgot to include another 2 sound files. Create a directory called c:\rt and copy these 2 attached files into there, together with main application file.You can then hear the sound problem.

[neelsfer]

Got it right after i copy and pasted the code into many different events.
Eventually I put this code into "After insert" event of specific form and it worked
It first updated the other subform and then checked for 0 or 1 in lapsremain field, followed by sound.

# [Forms]![racesetupXCf]![RaceresultsSF].Requery
# If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 1 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bell.wav", SND_ASYNC) Else
# If [Forms]![racesetupXCf]![RaceresultsSF]![LapsRemain] = 0 Then iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)

Thx TheSmileyCoder, you put me on the right track

[NeoPa]

Excuse me if I'm off-base here, but it seems you have a trigger which determines when a state is active, when really you need a trigger which determines when a state has been reached. The difference being that it only causes the sound once. When the state is reached, rather than every time a particular piece of code is encountered once that state has been reached (X <= 0 or whatever).

To ensure a sound only gets triggered once, it makes sense to set a boolean variable to indicate when it has been triggered already. So, for instance, if X <= 0 is the triggering logic, and the new boolean variable is blnY, then this would change to X <= 0 AND Not blnY. The code that is triggered must still make the required sound, but it would have an extra task of setting blnY to True. That way it would only be run the once.

This fundamental logic could be manipulated if the logic needs to allow for the process to run again or any other complications, but the basic logic should be sound.

[neelsfer]

thx Neopa. It sounds like the correct way to do it. For now with my limited knowledge, i seemed to got it working by luck, by changing the event from where its fired from.Took me 2 solid days however.

[NeoPa]

I can't say for sure without a better appreciation of your exact issue, but sometimes triggering it in a different way obviates the requirement for programming the extra logic, so don't feel you've necessarily found a less good solution. At least it's another option should you need it, which is always handy to have in your locker.

The important point really is that you have found a solution.

[TheSmileyCoder]

If you want to learn more about the events and when and in what order they fire, a simple approach is to make code for each event containing a msgbox, with the event's name. For example:

Private Sub Form_BeforeUpdate()
  MsgBox "Form_BeforeUpdate"
end Sub

Then you can accurately determine in which order they fire, and also possibly what the correct event to use is. (After_Insert sounds like a good event to use in your case.)