Search for value in query column and execute a statement (1 Viewer)

Hi everyone! Im a little bit stuck here on how to approach what im trying to do.
Some help would be nice You guys always save the day.

What im trying to do on my form is play a sound file when a certain value appears in one of the form fields. Now, dont worry! I got the whole sound file and field value nailed. It works.

My problem:
I need to search this value in a query (1 particular column actually).
My form executes a requery, and when it does i want it to check if the value "30" is in that column. How do i do this?

Here's my code so far. Doesnt give me an error, it does nothing really...

Private Sub Form_Timer()

DoCmd.Requery

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM cosumersquery")

If drinks.Value = 30 Then 'this is my form field
API_PlaySound "D:\exceeded.wav"

Else
End If

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

As you can see im looking through my cosumers query.
The column i actually want to search in that query is called "drinks".

Thanks in advance guys!

Why the timer event and a recordset?
how many records are returned from your query? you're only testing one record.
You could probably use a Dcount.

Thanks for the reply! The timer is needed because its showing a overview on a big screen and needs refreshing and all that.

Dlookup would be ok, dont know how to use that on an entire column. I thoug it was only for 1 field.

I need to search through all records. Theres not many in the query, like 20 records.

How would i search a column in a query in vba, for a value in all records. i thought i came close...

1. the form field should be referenced as:
Me.Drinks
This will give you intellisense and make it absolutely clear that you are referring to a form field rather than a TempVar.
2. Opening the recordset isn't actually doing anything. You are not reading it. What are you expecting to happen?

Its a continuous form though.

I didnt expect it to read thats why im putting my problem here, because i dont know how to make it read the column and check if theres a value of 30.

Exactly my problem . HOW do i do that?

All forms work the same way - one record at a time. It is just with continuous or DS forms, you can see multiple records at once. The code behind the form doesn't run in a loop, it runs based on events. You click on a button, tab into a control, pick something from a dropdown list, scroll to a new record, Every form has lots of events and the idea is to hang code in the event that is triggered by the action you want to handle. For example, before a record gets saved, we want to ensure that the required fields are present and the values make sense. Code to do that goes in the Form's BeforeUpdate event.

Timer events are very unusual so please try to explain in business terms rather than technical terms what you want to accomplish. in more than 25 years of using Access, I have only found two uses for the timer event.
1. keeping track of inactivity so the app can automatically close itself if the user leaves it unattended for too long. I don't normally do this because my BE's are generally SQL Server so I don't ever have an issue with locked BE's but it is more appropriate when using Jet or ACE as the BE.
2. Refreshing a permanently displayed form. This is also quite unusual and I ran into it only twice. Once was for an application that ran at an animal shelter and each room had a large monitor that showed what animals were in what pens and the other was for a wholesale florist to display their inventory and keep it as current as possible. Fresh flowers turn over very quickly and you don't want two people selling the last dozen yellow roses.

Also, you might want to reconsider the noise. Using color to highlight the form would be less intrusive. Also, many computers in business environments don't actually have speakers installed.

Thanks Pat.

I could spend hours writing why i need to do certain things. As a matter of fact, its much more complex then i posted here. I already made a light version of the code so it would be down to the point. Its also not going to help others if i change what i want to do or reconcider. Please, i designed it just right.
The sound is needed, and there are speakers. I pay attention to these kind of things. Threads are always full with answer that lead away from the question.

I need a loop to check all the records if theres a value of 30 in a column of my query.

It cant be that hard. I dont think its such a complicated question really.

Im going to figure it out, ill post the reply to help others if i found it.

You don't need a loop and you probably don't need a timer event. But unless I know what you are trying to do, I can't offer a definitive solution. Events are not arbitrary. The code needs to go into some event but what event? The code you need - which is almost certainly going to be a domain function rather than opening a recordset, needs to go into the correct event. If it turns out that a timer event is appropriate, how will we prevent the same "error" from being reported more than once?

Do you want to do this check when a user moves to a new record? Do you want to do it when a user updates a record? Are you trying to determine if there are ANY records that satisfy the criteria? Are you trying to determine if some other user entered a record with this criteria and you want to notify the current user.

It cant be that hard. I dont think its such a complicated question really.

Click to expand...

Its not that hard but it can be complicated and leaves a lot of questions when trying to offer a solution.
for instance your original code should have looked like
But this will play the sound each time it encounters a drink = 30 and each time the timer event runs.

to play a sound once if the condition is met you would do something like

But now what happens if drinks = 31? you might want your trigger to be drinks > 29 so the trigger fires until the condition is corrected.

that said, as Pat and I pointed out a simple Dcount would be better than a opening a recordset.
Something like

HTH

Thanks alot guys! The top one was what i was looking for

I understand Pat.

The thing is i should have posted a full disclosure. Sorry for that.
I was just trying to simplify it for other people and get to the core problem.

What i was ACTUALLY trying to do:
Instead of drinks, i have a calculated field that has "elapsed time in minutes" result.

On a big screen monitor, it has orders coming in from the top down.
Each order has a waiting time. So when the order is not being picked up after 30 minutes, it will play an alarm sound. I put this on the timer even because the screen refreshes every 45 seconds. So when it does, the result will be 31 by that time, and so it wont play again. But it was only doing that for the top order (first record). Now it just searches for anything thats 30.

Thanks, love yall