How to create an Alerts/Diary system using MS Access to produce popup alerts for certain events?

Hi all
Im currently doing a program for work which is a sort of Alert/diary system.
It needs to run in the background and produce popups when a certain day/time is reached ie.
A new employee starts on 08/05/2006
The system needs to produce a popup a month later to check on employees progress.
So on 08/06/2006 a popup would appear regardless of what program they are in and remind them. they can then cancel the alert when the action has been done etc.
They also need to be able to set up their own alerts ie. they could insert an alert after someone has had a disciplinary letter for a month later so they can check on the situation to see if the employee is still behaving badly.

The company currently use an access database to store their employee details amongst other things.
So what im asking is:

1. How would you go about starting something like this?
2. How do you run a program in the background?
3. Has anyone got any example programms you can send me?
4. Has anyone got any advise they can give about this sort of program?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jonathan KellyCommented:
Outlook does this very well already.

alternatively if you have the employees start date you could write a utility to compare the current date with the start dates.

You could place the utility in everyones startup list so that when they logon the check is carried out.
Hamed NasrRetired IT ProfessionalCommented:
EastyAuthor Commented:
lol thanks for that. Anything more to add?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
Try this:

Create an unbound form to function as an alert
Create a command button to hide the alert
You may create other fields to handle other tasks

Private Sub cmdHideMe_Click()
    Me.Visible = False
End Sub

Private Sub Form_Load()
    Me.Visible = False
End Sub

Private Sub Form_Timer()
    Me.Visible = True
End Sub

Set the time inerval to a suitable value, try with 10 sec = 10000
When the alert shows, hide it and after 10 seconds it shows again.

Good luck!
EastyAuthor Commented:

I'm gonna increase the points value.

I'm fairly new to access so need a bit of a push to get going on this.

1. How would you go about starting something like this?
2. How do you run a program in the background?
3. Has anyone got any example programms you can send me?
4. How would i compare todays date with the employees start date (code example)

Please help

Jonathan KellyCommented:
we have given you two possible solutions.

If you go hnasr way you can set how often the check should be carried out but you will have an access app running in the background continously polling your database.

My way the check runs when the user logs in.
You could also use Windows Scheduler to run the check at predtermined intervals throughout the day.

in either case the key piece of code will be something like:
SELECT EmplyeeName FROM EmployeeTable WHERE EmployeeStartDate = Date()

open this within a recordset and display the results to the user.

Jonathan KellyCommented:
be more specific if you need more.
EastyAuthor Commented:

The program needs to produce an alert 1 calendar month and maybe 6 calandar months after the employee's start date.
So SELECT EmplyeeName FROM EmployeeTable WHERE EmployeeStartDate = Date()
would produce the alert for the employees start date if im not mistaken.

Is there issues with date? How do you calculate an exact month? Is it 28/29/30 or 31 days?
Is it feasable to run access in the background? would it take up too many resources?

The program also needs to allow users to set their own alerts up for certain events to remind themselves.
Is it worth setting up tables to store the event information or would it be better to just run queries when
event information was required?
Jonathan KellyCommented:
use datediff

So SELECT EmplyeeName FROM EmployeeTable WHERE DATEDIFF("m",EmployeeStartDate,Date()) >=1

If you really cant use something like Outlook (which already has this type of functionality built in)  then
you could have a table with Event_Description,Active,RemindInterval,StartDate,User.

You need a Function to read this table and pull reminder details for the current user.
SELECT Event_Description FROM EventTable WHERE ((Active = True) AND (User = CurrentUser) AND (DATEDIFF("m",StartDate,Date()) >=RemindInterval))

The user will need to action these ( make Active = False perhaps).

And Then you need some sort of scheduler, be it the Timer Event, Windows Scheduler or someother Scheduler to kick off your code.

I dont think you can build an Access App that will truly sit "in the background" but you could minimise it on startup.
For a true background running program you need to build a Teminate Stay Resident(TSR) or Windows Service.
Ive never attempted one of those so I dont know whats involved.

The On Timer Event as suggested by hnasr is probably the easiest route.

EastyAuthor Commented:
Ive been looking at Outlook and its functionality and i must admitt it is ideal for the job.

If say 2 people have the same email address eg
then the 2 people can share the tasks cant they? So they can have ownership of a task
but they will both still receive the alert?

The problem is that the main program/database that we use is written in access and they would like
the alerts to be produced in that program. But as we know access isnt really built for that kind of task.

The other problem is for example each employee who starts will need a progress check  over 300 employees (someone to phone the branch and arrange for someone to ask how things are going etc). This is done after 1 month and 6 months (probation)
Ideally you do not want to set up each of those 'tasks' individually, can outlook talk to MS Access to set these up automatically?

But for setting up 'random' alerts outlook is fantastic.
Hamed NasrRetired IT ProfessionalCommented:
Good comment Datrias,

You can add code to the On Timer Event to take care of Time to fire the alert.

For date issues, consult access help looking for date:)
You can find the variance in days/months/years between the values of the Employee EmployeeStartDate and the Current date.

Then if variance in date > number of days/months/years, show alert.
EastyAuthor Commented:
Ive been thinking about the whole thing. It doesnt seem sensible to be running a version of access
in the background with the on timer events as this would take up a lot of resources. As Datrias said to do this
I would be better off using a TSR for this sort of program. Unfortunately I dont have the skills or time to write this sort of program.

The only 2 options i believe i have are:

1/ To write seperate queries for each event. I can then run all the queries with an on-click event to produce a report that shows all the up & coming events. Cons of this are that the user cannot set up their own alerts.

2/ Use outlook to set up the alerts with an effective alert system. Cons of this are that each alert will have to be set up individually. Outlook doesnt know about the data in our database.

Or a combination of both.
Jonathan KellyCommented:
I think option 1 is a great idea.

you could use our example of the events table, build a form to allow users add Events and then run a report at the beginning of each day/week/month that will show all upcoming open/unactioned events.

EastyAuthor Commented:

How would users create their own? Can you create queries through a data entry form?
Im not too sure on that. This way they can have ownership of the event and can select whether its 'active' or not
Sounds good.
Jonathan KellyCommented:
What do you mean by Query?
When I say Query Im talking about the Access Query Object which is essentially a saved sql statement.

Giving the users ownership of the event is a good idea.

For this to work you need to decide on a common policy for your Events i.e.
All events must have an Owner,Type(Employee 6 Month Review),Due_Date,RemindInterval,Status(Active/Complete/etc..),Description,CreateDate.....

If you can fit all types of events into this one table then you can simply build a system around it.

Creating an event would simplyt meand adding a record to the events table.
To get a list of events, run a report to show all events where status = Active and RemindInterval <= DATEDIFF("d",Due_Date,Date())

Give the users a form to change the status of their event to Complete so that Event no longer shows on the weekly report.

if we can go this way all we need (at a min) are 1 table (Events), 1 Query (EventList), 1 Form(Add/Edit Events) and 1 report(Event List Report)

couldnt be simpler ?

Jonathan KellyCommented:
if im way off the wall with this or you dont know what im on about - just say so :)
EastyAuthor Commented:
Think im getting it now

Ive got my table with:


Ive also set up my input form. The main issue i have now is to set up the 1 month and 6 month alerts without having to input each individual alert seperately. Could i for example run a query that would populate the alert table with standard alerts. Otherwise there would be no pint in doing this. I need to populate the table from my data automatically.

Jonathan KellyCommented:
ok, where is your data?
EastyAuthor Commented:
Wow that was quick.
Cheers by the way your a saviour.

Ok i have an employees table and a branches table. I have set up the following query to find a month after the employees start date:

SELECT wj_alerts.Subject, wj_employees.Forename, wj_employees.Surname, wj_branches.Name, wj_employees.StartDate
FROM wj_alerts, wj_employees INNER JOIN wj_branches ON wj_employees.Department = wj_branches.ID
GROUP BY wj_alerts.Subject, wj_employees.Forename, wj_employees.Surname, wj_branches.Name, wj_employees.StartDate, Date(), wj_alerts.AlertID, wj_employees.EndDate
HAVING (((Date())=DateAdd("m",1,[wj_employees]![StartDate])) AND ((wj_employees.EndDate) Is Null));

So i need to check all startdates on startup or something along those lines. then create new records for every new employee startdate
Jonathan KellyCommented:
you seem to have it done.

Is wj_alerts is the alerts table ?

lets step back for a second and review what it is you actually need.
1. You need to a system to alert users when an employee review is due.
2. You need a system to allow users create, manage and share there own alert/events/reminders ?

Correct ?
EastyAuthor Commented:
wj_alerts is the alert table yes.

1. You need a system to alert users when an employee review is due. Correct
2. You need a system to allow users to create, manage and share there own alerts/events/reminders. Correct

There will be a number of 'set' alerts ie 1 month review + 6 month review that apply to every single employee and probably more
as the when the system is up and running more will be uncovered.

The problem is we dont want to be inputting each of these 'set' alerts individually because this would be too time consuming. Is there any way that on say 'startup' a query could be run to update the wj_alerts table with these 'set' alerts so they are produced automatically
at the moment its a stand alone query and doesnt add any data in to the wj_alerts table.

Jonathan KellyCommented:

well you have two choices.
1. You can query for Employee Alerts and Other Alerts seperately (2 reports) OR 2. you can add the Employee alerts to the Alerts table and manage all alerts with same rules.

I would go with option two if you can.
This means you will need to add Employee alerts to the alert table as they happen. You could add some code to your startup routine to use your existing Employee Alert Query to add records to the new Alert table.
Make sure you dont add the same alert twice.

Then your report wil use the Alerts table to list all Alerts and users can action these so that they dont reappear and/or are rescheduled.

with me?
EastyAuthor Commented:

I want option 2 really so we can manage all alerts together. So I need to add all alerts to the alert table as they happen.
If i ran 'light queries' on statup it wouldnt affect the performance too greatly i guess. But the main question now is how do
i create the new alert records from the running of the query? and watching for duplicates.
Jonathan KellyCommented:
lets say subject is employee id and description is 1 or 6

each employee should only be entered once for each - correct ?

in dao
dim employee as dao.recordset
dim alerts as dao.reordset

for each employee
 check if they exist already in alerts for this reminder (either 1 or 6)
 if not then
  alerts("subject") = employee("id")
  alerts("description") = employee("reminder type")
  alerts("duedate") = employee("duedate")
  alerts("RemindInterval") = NoOfDaysToAlertUserBeforeDueDate
  alerts("status") = "Active"
end if
next employee

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EastyAuthor Commented:
Ok brill

Where would that code go?
What is a dao? (Ive only been doing access 2 months :-) )

Jonathan KellyCommented:
Data Access Objects

allows you to work with JET throught VBA

code would called by your startup routine.

its only psuedo code btw
EastyAuthor Commented:
Ok thats great just gonna get my head down now and try and develop something.

Thanks alot for your help.
Jonathan KellyCommented:
if your learning this stuff from scratch I recommend you start learning ADO rather than DAO if you dont need to debug older systems.

ADO ActiveX Data Objects is newer all singing all dancin version of DAO

up to you - loads of help available for both
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.