We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

Easty
Easty asked
on
Medium Priority
936 Views
Last Modified: 2012-06-21
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?

Thanks
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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 Professional
CERTIFIED EXPERT

Commented:
Interesting!

Author

Commented:
lol thanks for that. Anything more to add?
Hamed NasrRetired IT Professional
CERTIFIED EXPERT
Commented:
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!

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Ok

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




CERTIFIED EXPERT

Commented:
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.




CERTIFIED EXPERT

Commented:
be more specific if you need more.

Author

Commented:
Ok

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?
CERTIFIED EXPERT

Commented:
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.



Author

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 peoplecentre@warrenjames.com
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 Professional
CERTIFIED EXPERT

Commented:
Good comment Datrias,

 Easty,
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.

Author

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.
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
Ok

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.
CERTIFIED EXPERT

Commented:
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 ?





CERTIFIED EXPERT

Commented:
if im way off the wall with this or you dont know what im on about - just say so :)

Author

Commented:
Think im getting it now

Ive got my table with:

AlertID
Subject
Description
StartDate
DueDate
RemindInterval
Status
Priority
User

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.

CERTIFIED EXPERT

Commented:
ok, where is your data?

Author

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
CERTIFIED EXPERT

Commented:
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 ?

Author

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.

CERTIFIED EXPERT

Commented:
ok.

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?

Author

Commented:
Yeah

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.
CERTIFIED EXPERT
Commented:
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.addnew
  alerts("subject") = employee("id")
  alerts("description") = employee("reminder type")
  alerts("duedate") = employee("duedate")
  alerts("RemindInterval") = NoOfDaysToAlertUserBeforeDueDate
  alerts("status") = "Active"
  .....
  alerts.update
end if
next employee









Author

Commented:
Ok brill

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

CERTIFIED EXPERT

Commented:
Data Access Objects

allows you to work with JET throught VBA

code would called by your startup routine.

its only psuedo code btw

Author

Commented:
Ok thats great just gonna get my head down now and try and develop something.

Thanks alot for your help.
CERTIFIED EXPERT

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.