Want a sound to play after requery if a new record exists 2

[load3d]

I have a form that performs a requery every 5 seconds using the ontimer event. When a new record exists in my appointments table it populates on the form. What I want to do is get a sound to play after the me.requery function IF a new record exists.

"IF a new record exists then API_PlaySound "c:\windows\media\chimes.wav"


I have tested the sound module if I put the API_playsound function into the ONLOAD event, it works.


Option Compare Database

Const SND_ASYNC = (1)
Const SND_NODEFAULT = (2)

Declare Function sndplaysound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Public Sub API_PlaySound(pWavFile As String)

Dim LResult As Long

LResult = sndplaysound(pWavFile, SND_NODEFAULT + SND_ASYNC)

End Sub

 

[MichaelRed]

i ass u me the question is something like 'how do i have make this happen on the event'?

Assuming the question, one approach would be to compare the recordcount of the recordset to the previous value in the ontimer event.



MichaelRed

 

[load3d]

That is a good assumption. Thanks. I still haven't cracked it yet so I'm open to any more details or solutions. The form that is refreshing is using data based off of a select query that selects appointments that were created in the last hour and then displays on the form when it does it's requery. The approach you suggested would probably work, I suppose I could just count all of the records and every time a new record is added it plays a sound. Can you help with syntax? I'm not sure what functions to use? dcount? I would have to store the value of the previous requery and compeare it to the latest requery. How would do I tell Access to remember the value of the dcount?

 

[load3d]

I created a seperate table where i'm using a dcount of the appointments table to count the number of appointments and using an update query to update the number of appointments. Then I'm using dlookup to compare the actual number with the number in the new table.

I'm running into another problem now....

My update query prompts me for a value each time it runs, it's asking me for the value of the field. The problem is within the update query and I know it has something to do with the fact that I'm running the docmd.openquery cmd from a subform. The problem is in the update query in the "Update To" section. right now, I have Forms!Mainform!subform!field (field that I'm writing the dcount to)

when the update query runs it prompts me for a value when I manually type the value in, it is reflected in the table that i'm updating. However, it's not pulling he value from the field like it should.

I'm trying my best to speak a language that everyone understands.. let me know if I can be more detailed.

 

[MichaelRed]

the prompt means the query is asking for the value, normally this is a "parameter" which the query uses as part of the criteria for selection of values. mayhap you can review the query and see if you have misspelled the field name. another, less probable course of action would be to post the SQL of the query -after verifying all of the field names- to see if someone here can spot the err.




MichaelRed

 

[load3d]

Exactly. When it prompts the value and I enter in a random number the table gets updated. Basically, when I execute the openquery command it can't find my field. I have checked the spelling a dozen times but that is a common mistake for everyone.

The SQL View

UPDATE ApptCount SET ApptCount.AppointmentCount = [Forms]![newdesign]![Form]![atehourappts]![apptnumber]
WHERE (((ApptCount.AppointmentCount) Is Not Null) AND ((ApptCount.ID) Is Not Null));

Updating table - ApptCount, appointmentcount column
Main form - Newdesign
subform - atehourappts
subform field - apptnumber (holding a value)

I have checked and verifed that the apptnumber field contains a value, and the names are correct.

Another thing that I would like to note is that I have other update queries that I moved from a main form into a subform that no longer work now since I turned them into subforms. When those queries are executed they prompt me for values as well. It has to be something with the Update To or the criteria that I'm doing wrong.

 

[ProgramError]

[Forms]![newdesign]![Form]![atehourappts]![apptnumber]

try

forms!newdesign!atehoursappts!form.apptnumber


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[load3d]

Nope. It still prompts for a value.

 

[PHV]

What about this ?
[Forms]![newdesign]![atehourappts].Form![apptnumber]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[load3d]

Thanks. That should work but it does not work. Does it matter that within in my main form there are 3 seperate sub forms? They have all have different names. My main switchboard which is also a subform is controlled by a sourceobject. I'm not sure if this matters, but I have already ruled out alot of things.

Thanks PHV!

 

[PHV]

Follow the expression builder (loaded forms) to discover the correct syntax.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[load3d]

What do you mean by loaded forms? I did the expression builder on the query but that didn't work.

 

[MichaelRed]

hard to really know without some more info, but are you asking the query to get the value from the control your are attempting to populate?

UPDATE ApptCount SET ApptCount.AppointmentCount = 
[Forms]![newdesign]![Form]![atehourappts]![apptnumber]

WHERE (((ApptCount.AppointmentCount) Is Not Null) AND ((ApptCount.ID) Is Not Null));

the first "line" is updating the [AppointmentCount] Field to the value of the control on the form.

At least at a glance, this value (the control) MUST not be populated until the query has run the first time the query is run.

There MUST be some other part of the process which then / alternatively populates the control. The query should not need to reference the control, but should be the control source for it?

The count of the appointments needs to be what the control is displaying, not act as the operator value to update the count?



MichaelRed

 

[load3d]

Here is the "Code" for the Form Timer event. Please note the single quotes on some lines as I need to get the update query to work before I can make the pseudo code work.

Also notice that I'm running update query on form load with the controls value being set to get the true number appointments.


__________________________________________

Private Sub Form_Load()

DoCmd.SetWarnings False

Me.apptnumber = DCount("*", "Appointment", [AppointmentStamp])
Me.apptnumber.Visible = False
DoCmd.OpenQuery "qry_apptcount"


_____________________________________________

Private Sub Form_Timer()



Forms!newdesign!Frame.SetFocus

' Dim beforequery As String
' Dim afterquery As String


Me.Requery

' afterquery = DCount("*", "Appointment", [AppointmentStamp])

' If dlookup("*", apptcount, apptnumber) = afterquery then API_PlaySound "c:\windows\media\chimes.wav"

On Error Resume Next

Me.apptnumber = DCount("*", "Appointment", [AppointmentStamp])

DoCmd.SetWarnings False

DoCmd.OpenQuery "qry_apptcount"

End Sub

 

[load3d]

I'm not sure if I noted this before but I have other update queries in this database that stopped working when I converted them to the form/subform structure. I didn't realize it at the time that I posted. However, this is no longer about getting a sound to play. Once I figure this out my DB will function again. Ah, it's early Monday morning so excuse me if I sound even more like a newbie.

 

[MichaelRed]

Agaiin, I do not really have time to 'critique' this. I would generally use a query to simply retrieve a value from a recordset ~~

I don't really see the need for the conditional clause (assuming ApptCount.Id is realy an identity (or autonumber).

So,

Dim dbs as DAO.Database
Dim qdf as DAO.QueryDef
Dim rst as DAO.Recordset
Dim MyVar as Long

strSQL = "Select Count(*) as NumAppts
From ApptCount;

set qdf = dbs.queryDefs(strSQL, dbopendynaset)
set rst = qdf.openrecordset
MyVar = qdf!NumAppts

[Forms]![newdesign]![Form]![atehourappts]![apptnumber] = MyVar

of course this also assumes that you are (or at least can reasonably) use the DAO recorset object or can readily translate to your favorite alternative.

(p.s. this is typed in HERE, so not tested even for declarations or syntax)




MichaelRed

 

[PHV]

The following seems very strange to me ...
Me.apptnumber = DCount("*", "Appointment", [AppointmentStamp])

Place the cursor inside the DCount word in your code and press the f1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[load3d]

Thanks everyone for your help with this. PHV your analytical approach allowed me to rethink the approach I was taking. This is really not the correct way to do this but it works.

Since the problem all along was that Access wasn't allowing me to reference the field through the syntax I was using. I changed the update query too this and and made a copy of the field to the main form and set it to visble = false.

UPDATE ApptCount SET ApptCount.AppointmentCount = [Forms]![newdesign]![apptnumber]
WHERE (((ApptCount.AppointmentCount) Is Not Null));


Then I changed my ontimer event to this:

Private Sub Form_Timer()
Dim counts As String
counts = DCount("*", "Appointment", [AppointmentStamp])

'set focus to the main form
Forms!newdesign!Frame.SetFocus

'requery the subform
Me.Requery

'play sound if the appointments do not much (assuming there is a new appointment)

If Forms!newdesign!apptnumber = counts Then GoTo 100 Else API_PlaySound "c:\windows\media\chimes.wav"

'count the appointments again
Forms!newdesign!apptnumber = DCount("*", "Appointment", [AppointmentStamp])

'update the table with the new number (if a new one exists)
DoCmd.SetWarnings False
100 DoCmd.OpenQuery "qry_apptcount"
End Sub

 

[PHV]

The way you use the 3rd argument of the DCount function is still strange ...