msgbox notification if time = x minutes... help!!

[slsy1212]

Hi, I'm making a database in access where the number of minutes a resident has kept something is recorded in through a query. What i need to make is a msgbox notification that checks if there are any entries where the minutes is equal to a certain number of minutes, and then displays the name of the entry with the overdue item in the msgbox, ie "the resident: John Smith has items overdue". in addition the function has to run in the background, and automatically checks for overdue items.

i tried doing this by making a query that extracts entries with minutes = x (it can't be > x as i only want the msgbox to appear when its overdue by that number of minutes), and i even managed to get it to display the name of the resident in the msgbox, but then i ran into a problem: most of the time the query will be empty, where there are no items overdue, and then it comes up with an error saying, no record found. can anyone help me to make a statement that only executes when there is an entry in that query?

heres my code so far:
Private Sub Form_Load()
Me.TimerInterval = 10000
End Sub

Private Sub Form_Timer()
Dim rsVacs
Dim strEmplName As String
Set rsVacs = CurrentDb.OpenRecordset("VacQ2")
strEmplName = rsVacs.Fields("Resident_Name").Value
(this is the bit i can't figure out ) If strEmplName in VacQ2 is not empty Then
MsgBox "The following resident:, " & strEmplName & " has not returned the vacuum cleaner in more than one hour."
End If
End Sub

Please help me, i've searched all over this forum and found nothing relevant! Thanks in advance....

 

[meboz]

You could do it a couple of ways.

1. When you open a recordset, the first thing you should do is rst.movelast, in order to force the entire recordset to load. Then you could check rst.recordcount, and if its greater than 0, run your msgbox function.

2. Use the EOF (end of file) property of the recordset to check if the curser is past the last record. If the recordset is empty, rst.EOF is true.

 

[slsy1212]

Yay!

i finally got it to work! i just thought i might as well try, i followed your advice and tried
if rsVacs.RecordCount > 0 Then...
and it works!! thanks so much for your help meboz, its much appreciated! but now i realized theres another smaller problem... if theres more than one record in there, ie there is a possibility that two items were let out at the same time and so are overdue at the same time, it only reads the first record? Is there any way i can get it to read all records and say, "The following residents: John, Sally have items overdue"?