Evaluate result of sql query

[Tyork]

I will first explain the business problem and then discuss what I think my most efficient solution is.

Business Problem


I've built a multiuser database that allows tracking and reporting for daily tasks for everybody in our bookkeeping department. There are four deadlines associated with each task. I need a message to pop up when a deadline has been missed.

Possible Solution
Call a procedure every minute to evaluate tasks for the day. The called procedure would pass today's tasks into an array via SQL. A "for loop" would then evaluate the different tasks in the array to determine if the current time was greater than the deadline time AND if the deadline status field was blank (indicating it had not been completed).

I'm stuck at the poing where I need to pull the results of the SQL query into the array - I'm not sure how to go about doing this. Once the array has been populated I can code the rest. This is what I have so far:

 
Sub CheckDeadlines()
Dim Deadline1Array() As Integer
Dim ArraySize As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
SQLArraySize = "SELECT COUNT(*) as ArraySize FROM BMS_Occurrence WHERE day = date()"
Set rs = db.OpenRecordset(SQLArraySize, dbOpenDynaset)
ArraySize = rs!ArraySize
ReDim Deadline1Array(1 To 3, 1 To ArraySize)
SQLPopulateArray = "SELECT TaskName, Deadline1, Deadline1Status FROM BMS_Occurrence WHERE day = date()"

Any help in solving this would be appreciated including suggesting another method. Thanks!

[weekend00]

you don't need any program to find out the task which pass the deadline. A query can pick all these tasks. You may do further steps in you program with those tasks.

The query to pick up over due tasks:
SELECT TaskName, Deadline1, Deadline1Status FROM BMS_Occurrence WHERE Deadline1< now()

[Tyork]

Thanks. That's an easy solution - I don't know why I was trying to make it more difficult.