Play sound on new record in report

[crann]

Hi

I have a report that shows a list of maintenance jobs. Each job has a priority code for example planned maintenance is a P3 and emergency call out is P1.

Is it possible to play a sound if a new record is added with a P1 status when the report refreshes which is set to around 5 minutes.

So report auto refreshes, detects a new P1 status job has been added and plays a sound just the once (not on every refresh)

not the best with code so an help would be great.

Thanks

 

[sneuberg]

If you only want it played once then I think you will need to store the fact that it was played in a field (Edit: or see next post) . So first I suggest added a field to the table let's say as Yes/No field named Played. The default value of this field would be False or No. Then in the code the refreshes the report you could detect new P1 with a Dcount something like.

If DCount("*", "[[COLOR="blue"]name of table or query[/COLOR]]", "[[COLOR="blue"]name of status field[/COLOR]] = 'P1' and [Played] = False") > 0 Then
    fPlayStuff ("[COLOR="blue"]C:\SoundFile.Wav[/COLOR]")
    CurrentDb.Execute "UPDATE [[COLOR="blue"]name of table or query[/COLOR]] SET [Played] = True"
End If

where you need to determine the values in blue. Code to play sounds which has the fPlayStuff function can be found here.

If you can get by with a beep then replace fPlayStuff ("C:\SoundFile.Wav") with Docmd.Beep. Also there are other way to play sound so you might want to google "ms access play sound" and have a look first.

 

[sneuberg]

As an alternative to adding a field I guess you could have a global variable, i.e. put

Public PlaySound As Boolean

in a module and then just set it to true any time a record with P1 is added and then in the refresh code just

If PlaySound then
   'play the sound
   PlaySound = False
End If

 

[crann]

Hi
Thanks ok got to be honest I am a little lost. Step by step if possible.

I have the following code on the timer event of my report that refreshes report at intervals:

Private Sub Report_Timer()
Me.Requery

If DCount("*", "[tblJobSheet]", "[JobStatus] = 'P1' and [Played] = False") > 0 Then
fPlayStuff ("Z:\5741226.Wav")
CurrentDb.Execute "UPDATE [tblJobSheet] SET [Played] = True"

End If
End Sub

The sound file i want ot play is saved as Z:\5741226.Wav.

I have added a yes/no field in table with field names as above.

I created a new module but not sure what code needs to go in there or what to save the module as.

thanks

 

[sneuberg]

I forgot to mention that the code in this web page will not work if you have the Office 64 bit edition. Do you? If not then go to the the VBA editor (CREATE Tab, Macros & Code, Visual Basic) Click Insert then Module to insert a module. Then copy and paste all the the code in the web page into the module. The name of the module doesn't make much difference as long as you don't give a name that the same as a function name. I suggest "Play Sounds Code" for a name. Click Debug and then compile to make sure everything is ok.

 

[Frothingslosh]

I forgot to mention that the code in this web page will not work if you have the Office 64 bit edition. Do you? If not then go to the the VBA editor (CREATE Tab, Macros & Code, Visual Basic) Click Insert then Module to insert a module. Then copy and paste all the the code in the web page into the module. The name of the module doesn't make much difference as long as you don't give a name that the same as a function name. I suggest "Play Sounds Code" for a name. Click Debug and then compile to make sure everything is ok.

Out of curiosity, does it just need the variables in the API references updated for 64-bit, or do the API functions just flat-out not work?

 

[crann]

Hi
No i have 32 bit. I have now got it working the only issue is when there is a "P1" in field it plays sound but then changes all fields yes/no to yes not just the "P1" field.

Any guidance

Thanks so much

 

[sneuberg]

Hi
No i have 32 bit. I have now got it working the only issue is when there is a "P1" in field it plays sound but then changes all fields yes/no to yes not just the "P1" field.

Any guidance

Thanks so much

I realized that it would do that as I was writing my suggestion which is why I added the follow-on post suggesting a global variable. Because I if I understand what you want, it doesn't make any difference that all of them are set to Yes. The next PI that's entered will create a record with PI = False and that is sufficient to play the sound or am I missing something?

If it just bugs you that all of them are being set then change

CurrentDb.Execute "UPDATE [tblJobSheet] SET [Played] = True"

to

CurrentDb.Execute "UPDATE [tblJobSheet] SET [Played] = True WHERE [JobStatus] = 'P1'"

 

[sneuberg]

Out of curiosity, does it just need the variables in the API references updated for 64-bit, or do the API functions just flat-out not work?

So far as I know if the declare statements were updated it would work. But since I didn't feel like doing that it was easier just to say it doesn't work with 64 bit office.

 

[crann]

ok thanks so much, that works great.

I think it is actually quite uselful to be able to check if the sound was played, that way I can check the team were given an audio prompt.

 

[sneuberg]

Attached are some Wav files I've used.

 

[moke123]

if you really wanted to drive the point home, you could always yell at them with...

 Dim s As Object
    Set s = CreateObject("SAPI.SpVoice")
    s.Speak "Hey Stupid, you missed something"
    Set s = Nothing

 

[crann]

Would it possible to add into the code the following:

If PStatus field = P1 then flash the PStatus text red continously until Pstatus is changed.

The report shows live maintenance jobs and scheduling and we have a very short response time for P1 jobs hense why we have set the audible alert and want text to flash until we process the job.

Thanks

 

[sneuberg]

Edit: See next post.

If you have Access 2010 or later you can display the PStatus field and the other fields in red using conditional formatting. I'm not sure about getting it to flash. You might be able to turn the conditional formatting on and off with a timer. I'll look into it if you have Access 2010 or later. Do you?

If you don't have Access 2007 I believe it can still be done (at least the red text) but it involves more code. I'll see if I can find the code if that's the case.

 

[sneuberg]

Actually this seems to be simpler than I'm making it out to be. I googled "ms access blinking text" and found this Web Page which you probably could adapt to your situation.

 

[arnelgp]

the question is why are you using report instead of form? many events do not fire on report view. report view like a continuous form.

 

[crann]

Hi

I use a report as I am only displaying a list of live information to the users. I do not require data entry or editing from this screen.

I didn't think it was right to use a form and then lock out all the fields?

I only use a form if I need to enter or edit data. Is that correct?

Any advice welcome.

Thanks

 

[arnelgp]

I only use a form if I need to enter or edit data. Is that correct?

you can use form as display info and data entry.
say you have a pop-up notification on the screen? do you use report on it?
anyway, there is no rule, it's your database.

 

[AccessBlaster]

How do you display the reports choices to the user? A form would be the preferred method I would think.

 

[crann]

Hi
Sorry maybe I was not clear in my reply. The report I am talking about is a Matrix of maintenance jobs it is always on display on a slave pc which displays any changes in data via a sharepoint linked table.

Our team use this report to see where jobs sit in terms of their priority. As and when a new job is added, edited or completed so its position and traffic light indicator changes.

The origins of the data are of course inputted through a Job Sheet Form so no choices or changes are made on the report screen.

I was just trying to get a P1 status job to flash on the list with an audible sound when it arrived into the matrix.

Thanks guys