Sending reminders, be they weekly, monthly, paper, snail mail or email

The following logic, frequently known as "ticklers", applies to any timed requirement such as printing reports, emailing or snail mailing friendly and/or not-so-friendly reminders.

Determine the interval for sending the message, i.e. seven or fifteen day or monthly.  I'd suggest retaining this interval in an appropriate table such as ProgramOptions or JobOptions.  Possibly with an override for specific clients, organizations or individuals

Create a query where the criteria on a date field is <= today's date. Now display the data on a continuous form for the user to manually verify that things look reasonable and for them to change the dates for a given record, etc, etc. I'd likely create these records in a separate "history" table so the users can see everything that was ever sent.

Flag these records as "being processed" Now run through a recordset performing the appropriate action such as printing a repor or "form" letters or emailing the person. Once everything has printed or emailed successfully then you can change that "finished processed flag" to finished processing and you update the date on the client records to the next interval.

By using <= Date() this handles the Monday situation where you need to process the data from over the weekend.

By using a being processed and finished processing flag you verify that a problem of some sort isn't going to cause this set of reminders not being sent. These can be such simple problems as a printer jam or out of paper or someone pressing cancel on the printer or the outgoing email server refusing messages

These flags also handle the problem where someone adds an item to the table during the time period that between "processing" and "finished". And that this item will be handled properly tomorrow.

[ Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available