Microsoft Access Notifications with a Form Timer

A nice feature to have in a custom Microsoft Access application is a notification telling users about certain data-driven events or statuses occurring in the system.  These can be pop-up messages or emails or behind the scenes automatic data updates or changes.

The mechanism to do this in Microsoft Access is a form timer and one popular method to accomplish it is below.

The two form events that make this possible are “On Timer” and “Timer Interval”. “On Timer” is the event that fires VBA code when the “Timer Interval” value is reached and “Timer Interval” is set in milliseconds (1 second equals 1,000 milliseconds, 5 seconds = 5,000 milliseconds, etc.). So if you want the timer to fire the event every 15 minutes, that would be 900,000 milliseconds for the “Timer Interval” value.

The VBA code that fires behind the “On Timer” event can of course be whatever you want.  For example, you may want to execute a select query using a recordset object to find out the status of records based on some criteria and if the criteria is met, pop up a message box for the user notifying them of something. Another possibility is to send emails to various users notifying them, or do both.  Data updates are also common so for example the “On Timer” code might execute Add, Insert and/or Delete queries to change data behind the scenes based on various criteria.

Lastly, I like to set up the timer on a form that does nothing but hold the timer settings mentioned above. You should then have the timer form load with an autoexec macro or from another form (e.g. a Log In form or Startup form) when the application first loads and set the timer form’s visible property to hidden at run-time using the Visible property so the end user never sees it.

Of course if your back-end for the application is SQL Server that has SQL Server Agent jobs, you can use the jobs for email and data change events but it won’t give you the end-user pop-up.  Windows tasks calling Powershell scripts can also accomplish some of this.