How to delay a Query but continue to enter data?

[CTQE]

Hello Everyone,
I am using Access 2007 & trying to update a database to include a check & balance.

Currently a user enters their name into a form, selects a check box (Pass or Fail) & clicks a save button. Real simple form. The actual testing is performed on a standalone piece of equipment separate or outside of the MS Access database.

Here is the challenge, if someone does select Fail, I want to ensure this person comes back into a blank form (new entry) & confirms they have eventually passed. I want to give a time limit of 10 minutes before an email is sent out to their superiors. (I have the email portion figured out already).

Other entries will take place before the failed user comes back to the entry form so the form needs to be available for other people to enter their results.

I believe this can be done using some VBA and a query but I am not sure where to start especially with the 10 minute allowance to recheck the test status

Hopefully this makes sense.

Thanks

 

[plog]

I don't fully understand the context of what is to happen or what triggers everything. But if you want to know how long the last record a person entered was, then you need to store the DateTime with each record.

When they submit that form, the table it goes to needs to have a RecordCreated field that gets the Date/Time the record was created. Then you can have a query to find all those older than 10 minutes that were Fail.

Again, not certain all the moving parts of this, but do know that's the way to go for getting time since last record.

 

[CTQE]

Thank you for your prompt response.
I have since added a date/time field for each record.

How do I automate the execution of the query 10 minutes after the initial Fail entry record was saved into the table? I only need this query to run when the entry check box for Fail is selected.

Thanks for your help so far!

 

[plog]

How do I automate the execution of the query 10 minutes after the initial Fail entry record was saved into the table?

I don't think you are asking the right question. A query does nothing. If you open a query in a forest and no one is around to see the data does it really matter if you open the query?

You talked about emails and notifying people. Isn't that the ultimate action you want to take? Again, I don't understand all that you want to do, I just saw you wanted to calculate 10 minutes from when a record was created and provided a way to do that.

I only need this query to run when the entry check box for Fail is selected.

Chicken and egg. You can't know if the check box for fail was checked (or that it was 10 minutes ago) unless you run the query to find out.

You really need to come up with a list of specs and objectives. You're messing around working on details that are in the middle of the process without a clear understanding of the whole process.

 

[CTQE]

This would be the process
1.) User enters name & checks appropriate check box of test result
2.) If user selects Pass check box, record is saved no additional actions required
3.) If user selects Fail check box, record is saved & a "count down" begins (10 minute count down). If that user does not report back to the database to perform step #2 within 10 minutes then an email is sent to a person.

The query is executed when Fail is selected. The query will analyze whether a second entry (Pass) was entered within 10 minutes. If the pass entry is present then nothing occurs. But if the query does find there were no subsequent entries then an email is sent.

So the query is only seen in the forest when someone is there. If there is no one in the forest then the query does not report a message.

 

[plog]

So the query is parenthetical actually, its a means to an end. You want an email sent 10 minutes after a 'Fail' has been submitted if no corresponding 'Pass' has been submitted.

The problem is timing. You can set up a scheduled task (http://windows.microsoft.com/en-US/windows/schedule-task) in windows, but it has to be triggered by time (Every 10 minutes, every hour, every Tuesday, etc.). So you can create a process in Access to send your emails, then use the task scheduler to run it every 10 minutes. The problem is that if a 'Fail' is submitted 1 minute after the last 10 minute scheduled task, its going to be 19 minutes until it will be sent.

I am sure you can play with the frequency to get that to a desired range, but your emails will never be sent exactly 10 minutes after a 'Fail'.

Another possible way is a Timeout. There's VBA functions on the internet that delay code execution. So when a fail is submitted you tell it to pause for 10 minutes then run some other function. The problem with that is I don't know how its going to effect performance of your database since technically code will still be executing for those delayed 10 minutes. Also, multiple fail submissions could cause it to hiccup as well or send out an email early.

Those are the 2 options I believe you have.

 

[CJ_London]

how 'accurate' does the 10 minutes need to be? I'm thinking if you had a form timer event that ran once a minute. That event would create a recordset of all records where the time is between 10 minutes or older and email has not been sent. The recordset contains the relevant email addresses plus whatever data is required to be emailed.

Then you would have a loop to send the emails

So the process would be

create recordset - select all records where timefld is less than dateadd("n",-10,now()) and fail=true and emailsent=false
while not EOF
    send email
    update table with emailsent =true
    movenext
wend

 

[CTQE]

Thanks guys, you inspired a thought which I believe may work.
If the Fail button is selected, the record is saved to the table but in the background another form opens with a timer (10 minutes).
At the end of the 10 minute timer it executes a query to return a true false statement based on date time of the fail & current time (which would be 10 minutes after the initial Fail entry).

Thanks for your help, this should would like a charm.

 

[plog]

but in the background another form opens with a timer

I hope this works for you, but what about multiple pending emails? Suppose Tony submits a 'Fail' at 9:12, form launches with countdown. At 9:15 Larry submits a 'Fail, what happens? Form is already running for Tony and you only have 1 form.

I don't understand Timer events on forms that well, so maybe you can incorporate multiple ones on a form and assign Larry a different one. Or maybe make multiple forms. Just something to keep in mind.