If a field changes then play the beep sound

[Iram]

Hello.
I have a continuous form that refreshes automatically every 60 seconds. On
this form I have a generic field that is not tied to anything however it
counts how many records are on the form

=Count([CaseNumber])

If this field changes either up or down I need Access to play a sound or
play the access beep noise and pop a msgbox.

Do you know of a code that can help me acheive this goal?

Your help is greatly appreciated.
Iram

 

[Gina Whipp]

Have a look here...

http://allenbrowne.com/func-04.html I would put it in the On_Change of the
field.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[John W. Vinson]

Hello.
I have a continuous form that refreshes automatically every 60 seconds. On
this form I have a generic field that is not tied to anything however it
counts how many records are on the form

=Count([CaseNumber])

If this field changes either up or down I need Access to play a sound or
play the access beep noise and pop a msgbox.

Do you know of a code that can help me acheive this goal?

Your help is greatly appreciated.
Iram

What's triggering the update? Do you have code in the form's Timer event, or
is it something builtin?

Unfortunately I don't think there are any form events that you can use
directly: the Change, BeforeUpdate, and AfterUpdate events of this textbox (or
the form) will not fire unless you type into it (defeating the purpose).

What you might want to do is in fact use the form's Timer event. If you're
using it already just put a line

Beep

in it; if not set the timer interval to 60000 (milliseconds, one minute). Have
a Static variable set to the count every time the routine runs; beep if it's
changed.

 

[Gina Whipp]

Okay, I missed that... I guess I should READ and not SKIM! John is
correct. The sound file will not do anything in your scenario!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello.
I have a continuous form that refreshes automatically every 60 seconds. On
this form I have a generic field that is not tied to anything however it
counts how many records are on the form

=Count([CaseNumber])

If this field changes either up or down I need Access to play a sound or
play the access beep noise and pop a msgbox.

Do you know of a code that can help me acheive this goal?

Your help is greatly appreciated.
Iram

What's triggering the update? Do you have code in the form's Timer event,
or
is it something builtin?

Unfortunately I don't think there are any form events that you can use
directly: the Change, BeforeUpdate, and AfterUpdate events of this textbox
(or
the form) will not fire unless you type into it (defeating the purpose).

What you might want to do is in fact use the form's Timer event. If you're
using it already just put a line

Beep

in it; if not set the timer interval to 60000 (milliseconds, one minute).
Have
a Static variable set to the count every time the routine runs; beep if
it's
changed.

 

[Iram]

The form auto refreshes by the Form: On Timer: Event Procedure

Private Sub Form_Timer()
Me.Requery
End Sub

The Timer Interval is 6000

How do I do what you said, "Have a Static variable set to the count every
time the routine runs; beep if it's changed."?

Like I said I have a Text Box in the form header that counts all the records
in the detail section.
=Count([CaseNumber])

And in actuality I need the Beep to only occur when the number in this field
goes up not down.

I should probably explain the use of the db. It is used as task managing
program. People send each other tasks. When someone receives a task I would
like the beep sound to occur and a MsgBox to appear telling them they have a
new task.
If you have another solution that will give me the results that I need it
will be more than welcomed.

Iram

Hello.
I have a continuous form that refreshes automatically every 60 seconds. On
this form I have a generic field that is not tied to anything however it
counts how many records are on the form

=Count([CaseNumber])

If this field changes either up or down I need Access to play a sound or
play the access beep noise and pop a msgbox.

Do you know of a code that can help me acheive this goal?

Your help is greatly appreciated.
Iram

What's triggering the update? Do you have code in the form's Timer event, or
is it something builtin?

Unfortunately I don't think there are any form events that you can use
directly: the Change, BeforeUpdate, and AfterUpdate events of this textbox (or
the form) will not fire unless you type into it (defeating the purpose).

What you might want to do is in fact use the form's Timer event. If you're
using it already just put a line

Beep

in it; if not set the timer interval to 60000 (milliseconds, one minute). Have
a Static variable set to the count every time the routine runs; beep if it's
changed.

 

[John W. Vinson]

Private Sub Form_Timer()
Me.Requery
End Sub

The Timer Interval is 6000

How do I do what you said, "Have a Static variable set to the count every
time the routine runs; beep if it's changed."?

Like I said I have a Text Box in the form header that counts all the records
in the detail section.
=Count([CaseNumber])

And in actuality I need the Beep to only occur when the number in this field
goes up not down.

Change the timer event to check:

Private Sub Form_Timer()
Static iCount As Integer
Dim CurrCount As Integer
Me.Requery
CurrCount = dcount("[CaseNumber]", "[tablename]")
If CurrCount > iCount Then
Beep
iCount = CurrCount
End If
End Sub

This will allocate a "sticky" variable iCount, which will hold its value over
time as the Timer event gets called repeatedly. Each time it will count the
number of non-null CaseNumbers in the table tablename - edit this to the name
of an appropriate table or query. If that number is greater than the value
remembered from last time in iCount, it will beep and store the new count.

 

[Iram]

Beautiful, however I forgot to mention that the table is shared by many
folks. So I changed the part where you talked about CurrCount to this below.
However when the refresh occurs no beep sounds. Do I have the syntax right on
the above statement?

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", "[Task'd CSO] =
'fOSUserName()'")

There are spaces in the field names with apostraphies, and pound symbols,
and I am using the fOSUserName() module that looks to see who is logged into
the computer. Basically it's saying count CSE Case Numbers in table tbl_Main
where the Task'd person is me (the person logged in). Can you help me fix the
above statement?



Iram

Private Sub Form_Timer()
Me.Requery
End Sub

The Timer Interval is 6000

How do I do what you said, "Have a Static variable set to the count every
time the routine runs; beep if it's changed."?

Like I said I have a Text Box in the form header that counts all the records
in the detail section.
=Count([CaseNumber])

And in actuality I need the Beep to only occur when the number in this field
goes up not down.

Change the timer event to check:

Private Sub Form_Timer()
Static iCount As Integer
Dim CurrCount As Integer
Me.Requery
CurrCount = dcount("[CaseNumber]", "[tablename]")
If CurrCount > iCount Then
Beep
iCount = CurrCount
End If
End Sub

This will allocate a "sticky" variable iCount, which will hold its value over
time as the Timer event gets called repeatedly. Each time it will count the
number of non-null CaseNumbers in the table tablename - edit this to the name
of an appropriate table or query. If that number is greater than the value
remembered from last time in iCount, it will beep and store the new count.

 

[Douglas J. Steele]

No, your syntax isn't correct. What you've got will look for the literal
string fOSUserName(), as opposed to the value returned by the function call.

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", _
"[Task'd CSO] = '" & fOSUserName() & "'")

Exagerated for clarity, that's

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", _
"[Task'd CSO] = ' " & fOSUserName() & " ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Beautiful, however I forgot to mention that the table is shared by many
folks. So I changed the part where you talked about CurrCount to this
below.
However when the refresh occurs no beep sounds. Do I have the syntax right
on
the above statement?

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", "[Task'd CSO] =
'fOSUserName()'")

There are spaces in the field names with apostraphies, and pound symbols,
and I am using the fOSUserName() module that looks to see who is logged
into
the computer. Basically it's saying count CSE Case Numbers in table
tbl_Main
where the Task'd person is me (the person logged in). Can you help me fix
the
above statement?



Iram

Private Sub Form_Timer()
Me.Requery
End Sub

The Timer Interval is 6000

How do I do what you said, "Have a Static variable set to the count
every
time the routine runs; beep if it's changed."?

Like I said I have a Text Box in the form header that counts all the
records
in the detail section.
=Count([CaseNumber])

And in actuality I need the Beep to only occur when the number in this
field
goes up not down.

Change the timer event to check:

Private Sub Form_Timer()
Static iCount As Integer
Dim CurrCount As Integer
Me.Requery
CurrCount = dcount("[CaseNumber]", "[tablename]")
If CurrCount > iCount Then
Beep
iCount = CurrCount
End If
End Sub

This will allocate a "sticky" variable iCount, which will hold its value
over
time as the Timer event gets called repeatedly. Each time it will count
the
number of non-null CaseNumbers in the table tablename - edit this to the
name
of an appropriate table or query. If that number is greater than the
value
remembered from last time in iCount, it will beep and store the new
count.

 

[Iram]

John and Douglas you guys are the bomb!
It worked beautifully!


Thanks.
Iram

No, your syntax isn't correct. What you've got will look for the literal
string fOSUserName(), as opposed to the value returned by the function call.

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", _
"[Task'd CSO] = '" & fOSUserName() & "'")

Exagerated for clarity, that's

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", _
"[Task'd CSO] = ' " & fOSUserName() & " ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Beautiful, however I forgot to mention that the table is shared by many
folks. So I changed the part where you talked about CurrCount to this
below.
However when the refresh occurs no beep sounds. Do I have the syntax right
on
the above statement?

CurrCount = DCount("[CSE Case#]", "[tbl_Main]", "[Task'd CSO] =
'fOSUserName()'")

There are spaces in the field names with apostraphies, and pound symbols,
and I am using the fOSUserName() module that looks to see who is logged
into
the computer. Basically it's saying count CSE Case Numbers in table
tbl_Main
where the Task'd person is me (the person logged in). Can you help me fix
the
above statement?



Iram

Private Sub Form_Timer()
Me.Requery
End Sub

The Timer Interval is 6000

How do I do what you said, "Have a Static variable set to the count
every
time the routine runs; beep if it's changed."?

Like I said I have a Text Box in the form header that counts all the
records
in the detail section.
=Count([CaseNumber])

And in actuality I need the Beep to only occur when the number in this
field
goes up not down.

Change the timer event to check:

Private Sub Form_Timer()
Static iCount As Integer
Dim CurrCount As Integer
Me.Requery
CurrCount = dcount("[CaseNumber]", "[tablename]")
If CurrCount > iCount Then
Beep
iCount = CurrCount
End If
End Sub

This will allocate a "sticky" variable iCount, which will hold its value
over
time as the Timer event gets called repeatedly. Each time it will count
the
number of non-null CaseNumbers in the table tablename - edit this to the
name
of an appropriate table or query. If that number is greater than the
value
remembered from last time in iCount, it will beep and store the new
count.