Using Date/Time equation to alert users of overdue updates

[coolcps]

This is what I am trying to implement:
I have a form for priority work orders, it has fields that automatically update the time when modified. On priority 1 work orders I want to make it so that if the time modified is 1 hour past the current time(and priority 2 is 2 hours past), then either the form starts blinking (any form in my database) or maybe a pop-up alert comes up. Preferably something that will make Access pop up from the task bar or start blinking, but that is not totally necessary.

I need code that is constantly running on any form that is open in the database (so that as soon as current time - time modified > 1 hour the alert goes off), I am not sure exactly on how to do that. The date time equation should not be that tough though.

If anything is confusing I will try to clarify anything. Thanks for any help!

 

[namliam]

Well query-ing your table(s) is not really an option as that will draw to much resources.

I suggest you make a (hidden) form that contains this time.
Upon opening of the database open this form with a timer of say 10 secs? or 1 minute?
Also retrieve the latest priority list/time and put it on the form.
Each edit of a priority or record then needs to update this form as well.

The timer can then check only on the form if TIME () > OnFormTime then do something.

Not sure how to make access have focus and resize to be visible (again), doing a msgbox or something is easy enough. Tricky part may be to display the message then what if the user clicks "OK" but doesnt actually update and stuff like that!?!

Good luck!

 

[DCrake]

If you are using Outlook you could send the orders to the reminder section and program accordingly. When an order has been processed get it to go to Outlook and delete the reminder. Outlook will then take over the handling of the reminders. This is the difficult bit. The fact that Outlook is handling the reminders means that you do not need to have the application open to display the warnings.


David

 

[coolcps]

Both of those are interesting resolutions.

namliam, what is the requerying method? Could I make it update every 5-10 minutes? There should never really be more than a handful of priorities at any one time, would it really be that tought to have it only update open priorities?

DCrake, is there any where I can reference for sending outlook reminders? Everyone who works on these tickets have an org box open that would be perfect to have reminders pop up in.

Thanks for the help so far.

 

[namliam]

The Timer determains the MILLI seconds and the "On Timer" determains the check done.

The point is the # of open priorities may be low, over time the # of closed things will increase. If this DB then resides on the network, slowing it down...
But note, that when this code runs even taking just 2 maybe 3 seconds it blocks users from doing ANYTHING. Thus speed is of the utmost importance!

Problem with the reminders, how do you undo them, once updated?

 

[DCrake]

If you do a search on Outlook Reminders there are a few examples of commuicating with Outlook.

David

 

[coolcps]

The Timer determains the MILLI seconds and the "On Timer" determains the check done.

The point is the # of open priorities may be low, over time the # of closed things will increase. If this DB then resides on the network, slowing it down...
But note, that when this code runs even taking just 2 maybe 3 seconds it blocks users from doing ANYTHING. Thus speed is of the utmost importance!

Problem with the reminders, how do you undo them, once updated?

Is there a way that I could have my all closed priority tickets be moved to a seperate table after a week of closure, in order to keep the table with open priorities in it relatively small?

 

[namliam]

Yes, you can run different queries upon database open to archive closed tickets, sure...

But then your going to strugle with reports or searches as you have to do both over 2 seperate tables.

 

[coolcps]

Yes, you can run different queries upon database open to archive closed tickets, sure...

But then your going to strugle with reports or searches as you have to do both over 2 seperate tables.

It still seems like a decent possibility.

As it is currently we have a computer hooked up to a plasma, so I am thinking I can have a form that displays the open tickets on that TV and just have the form on the TV flash until updated. Currently I have this set up:

tbl_PWO (master table of all priority tickets)
qry_PWO_display (this only displays open tickets)
frm_qry_PWO (form displaying qry in datasheet mode)
frm_display (form to house the subform of frm_qry_PWO)

Anyway you can explain on how to archive closed tickets from the table? Thanks for all the help.

 

[namliam]

Just run 2 queries upon database open...
1) Append all the closed queries to a history table
2) Delete all the closed queries from the current table.

 

[MrGrumpy]

Another option to consider for your alerts / overdue priorities, is to change the background to red (or similar color). I have used this in the past by going to the fields property sheet (from the form), and changing the "back color" (in the "format" tab) to "system window", and selecting the color. It's probably a bit more appealing than "flashing", and easier than a pop-up.

I use this in a subform, so every item in the subform has a red background. I'm still a noob, but someone with a bit more know-how (Mr backwards "Mailman" maybe) might be able to tell you how to do this to a selected record in a list box (I'm assuming you are looking at a list box for your work list). It will also be easy to add a second listbox for overdue priorities.

One of the DB's I have made had a list of pending work. I had a separate "Do Now" list that covered urgent work. This list was colored red, and that strategy worked well.

 

[coolcps]

Just run 2 queries upon database open...
1) Append all the closed queries to a history table
2) Delete all the closed queries from the current table.

Sorry to keep asking stupid questions, but is therea anyway you could give me some more details on what code or macros to use for appending and deleting the closed records from one table to another?

Also I really like MrGrumpy's idea, is there any way to make the line of an overdue record in a data sheet turn red? Thanks again.

EDIT: After playing with the queries, that part looks pretty easy. But I am still not sure on making a record turn red. In datasheet mode it looks like conditional formatting would get the job done. If I use "Expression is" I should be able to input an expression that will make this work, correct?

 

[namliam]

Just run 2 queries upon database open...
1) Append all the closed queries to a history table
2) Delete all the closed queries from the current table.

MOre than that should not be needed... Just make 2 queries that do both...
Then create an autoexec macro or something that always run on opening of the DB. Then simply execute the queries in the macro or use:
Currentdb.Execute "Queryname"
in VBA to run them...

As for Grumpy's idea, use Conditional formatting.