Setting Alarms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm using Access '03. I have a sales contacts database consisting of a
Main table and a few child tables. One of the related tables is called
Follow-up. In this table the salespeople store follow-up or to do items.
The fields include date, time, regarding, ALARM and results. The Alarm field
has a combo box set with the following options: 5 minutes, 10 minutes, 30
minutes, 1 Day, 1 Week, 2 Weeks. Each follow-up item is assigned a reminder.


They want to have a "reminder" form available from a command button that
would display the follow-up items due. This would have to be based on the
computer clock I would imagine, unless using some kind of calculation based
on the =now() function would work...

Could somebody point me in the right direction? I suggested using Outlook
for this, but that would be too easy. Thanks for your time.
 
The easiest would be a query that picks up all items that have gone over time
- you could then base a form on that query and create a button that opens
that form. The criterion by which the right items could be picked up would
be that the date entered plus the Alarm interval is greater than (as you say)
=Now(). A useful function would be DateAdd (you can look it up in Help for
the right arguments). The tricky bit, as I see it, is that you're going to
have to find a way to resolve the different interval units.

A way to do this would be to create a new numeric field (e.g. "Interval") in
the table that's updated automatically when someone chooses an Alarm value
and you could use the Alarm control's AfterUpdate event to calculate this:

Sub Alarm_AfterUpdate()
If Alarm.Value = "5 min" Then Interval = 5
If Alarm.Value = "1 day" Then Interval = 24 * 60
etc.
End Sub

The field Interval could then be added to the original Date/Time in the
query itself and the criterion would be that this value is greater than Now().

Hope that makes sense!
 
Thanks Martin. I have started to work on your suggestions and then realized
I need a need to somehow translate my "Alarm" field into real time. Right
now it's a combobox containing text "5 mins", "1 Day" "1 Week", etc., which
is all text so it's worthless. I've searched the newsgroup and help on the
DateAdd function and there is lots of info, and I have the input date field
so now all I need is an Alarm field that contains data that can be used in
the calculation. Unfortunately I'm drawing a blank as to how to do this.
I've tried creating a new field to store an amount of time. I tried the
Date/Time data type, the number data type. I will keep at it. Thanks for
any help.
 
Back
Top