Reminder 2

[whill1]

I have a table that lists events with a date and a time field. How do I set up a Macro or a procedure to remind me that an event is comming up? Is there a way to adjust the amount of time the reminder will notify me before the event, because different events require different amounts of time to prepare for.

Thank You!!!!

 

[vbajock]

Sounds like you need to add an additional field to your table, called DaysInAdvanceToNotify. Then create a form on the table. In the on load event of the form, you would need to put code in to loop through the records in the underlying table to calculate whether the record is in the notify range, and to send a message to the screen if it is. Of course, I wouldn't bother, because all that kind of functionality is built into Outlook, so I just put it there.

 

[whill1]

Thanks vbajock,
I need to rephrase my question. I can set up the reminder in a macro to alert me for a particular date -- no problem. What I have trouble with is the syntax for dealing with time. Say I want the reminder to alert me 2 hours before a meeting. You know how you use the date funcion to manipulate dates like

.[Field]>= Date()-7 would return all dates starting 7 days ago.
I do not have a clue what the syntax is to manipulate time functions. I am trying to avoid having someone manually enter the reminder time. What I would really like to do is to figure out the syntax so that the reminder time would automatically entered in a field based on a predefined list from a dropdown list.... say 15 min. 30 min. 45min. 1 Hour. etc... Then this would be more straight forward in the macro or code that sparks the reminder.
Sorry this a little long winded but I hope it makes it more understandable.
Thanks,
whill1

 

[cgarts]

Hello,

Some hints

Dates and time are based on a day = 1
10 days is 10
an hour is (1/24)
12 hours is (12/24)
15 minutes is (1/96)
etc

A sub that will look for your alarm to go off is

Private Sub Form_Timer()
If Now() > [YourEventDateFieldHere] - Eval([YourWarningTimeFieldHere]) Then
MsgBox "Event Warning"
End If
End Sub

That looks to see if the "alarm time has come.

To build a combo that will easily enter the warning time add these properties:

ColumnCount Type in "2"

RowSourceType Select "ValueList"

RowSource Type or copy in
" 20;20 days;10;10 days;5;5 days;2;2 days;1;1 day;(12/24);12 hours "
with no spaces and no quote marks. Just an example - see above for other times you can add in.

ColumnWidths Type in " 0,2 " with no spaces and no quote marks.

Is this any help??????

I'll be online again Monday late.

C

 

[vbajock]

If you look in the help file on the functions DateDiff and DateAdd, it shows how to use these functions using hours, minutes, etc as well as date values to get the solution to just about any date problem.

 

[whill1]

cgarts,
Thank you!!!! That was very helpful. I am now in the process of making this work. I just have to figure out how to make the reminder stop after the appointment time is past. In the code: If Now() > [YourEventDateFieldHere] - Eval([YourWarningTimeFieldHere]), It seems to work ok but when I try If Now() > [YourEventDateFieldHere] - Eval([YourWarningTimeFieldHere]) And If Now < [AppointmentTime] Then MsgBox..... This added If seems to stop it from working. My understanding is that if both statements before and after the "And" are true it will execute and if one or both are false it does not execute. One thing that may be a problem -- when I am in the code module and I hover over my fields for Date, Time, or the warning field I see [FeildName] As TextBox instead of a Value for the field. Is this ok? or there a link missing somewhere?
Thanks Again,
whill1

 

[cgarts]

Hello,

Sorry I'm not answering faster but I am not on the machine all the time.

To "shut off" the alarm I created a Yes/No type field in the table of appointments.

I called it AlarmAnswered.

So the table has:
Field Name DataType
EventName Text IDs the event
EventDt DateTime date and time of the appointment
Warning Text warning in day fractions
AlarmAnswered Yes/No shut off

You may want a record ID too with Autonumber

Build your alarm form with this for a RecordSource

SELECT TblEventAlarm.EventDt, TblEventAlarm.Warning, TblEventAlarm.AlarmAnswered, TblEventAlarm.EventName, CDate([EventDt]-Eval([Warning])) AS Expr1
FROM TblEventAlarm
WHERE (((TblEventAlarm.AlarmAnswered)=False))
ORDER BY CDate([EventDt]-Eval([Warning]))
WITH OWNERACCESS OPTION;

This will put the appointment that has the most pressing alarm time "first in line". It will also ignore any record that you have elected to "shut off" by checking the AlarmAnswered checkbox. Those records "disappear" from your form. They will still be in your table but not available to the form.

I set the form's Timer to 60000 - the exact number doesn't matter - just not so low as to make it "go off" too often, but to "check" often enough.

On the OnTimer event I put this:

Private Sub Form_Timer()
Dim Str1 As String
' the next four lines of code just run
' a shutoff I put on the form to
' build it with less hassel
' just an option group with two buttons
' one is valued at 0
' one is valued at 1
' this just shuts it off if the option buttons are set to 1
If OptOnOff = 0 Then
Else
Exit Sub
End If
Str1 = EventName & " at " & EventDt
If Now() > TxtDt - Eval([CboDtA]) Then
MsgBox "Alarm for " & Str1, , Nz(EventName, "No Name")
End If
End Sub

On the form I have controls for each field in the recordset.

ControlName: ControlType: Bound to:
TxtEventName Textbox EventName
ChkAlarmAnswered Checkbox AlarmAnswered
TxtDt TextBox EventDt
CboDtA Combo Warning

The combo is as described before - Value List - 2 columns - " 20;20 days;10;10 days;5;5 days;2;2 days;1;1 day;(12/24);12 hours;(6/24);6 hours;(3/24);3 hours;(3/48);90 minutes;(1/48);30 minutes "
Bound Column is 1 - column widths 0,2 .

Your form will open with the earliest and most pressing appointment as the current record. It will run the above code repeatedly, and will pop up a messagebox when an unchecked alarm deadline is passed.

Each control you can edit that could change the alarm time sequence should have an after update event that forces the form to requery so that once again the first event in line becomes the current record and the alarm will "ring" first for the most urgent appointment. Add these:

Private Sub AlarmAnswered_AfterUpdate()
Me.Requery
End Sub

Private Sub CboDtA_AfterUpdate()
Me.Requery
End Sub

Private Sub ChkAlarmAnswered_AfterUpdate()
Me.Requery
End Sub

Private Sub TxtDt_AfterUpdate()
Me.Requery
End Sub

Is this any help?

C

 

[thecreator]

If you are interested (and not too far gone down the track you have) there is a knowledge base article 160502 "Using Automation to Add Appointments to Microsoft Outlook" (for Access 97 anyway) which shows you how to add appointments to Outlook from Access .... you could then use the Outlook functionality to manage the reminders and not rely on having your database open in time to remind somebody.

http://support.microsoft.com/default.aspx?scid=kb;en-us;160502&Product=acc97

Hope this helps

 

[whill1]

Hey All, I can't think you enough!!!!! especially cgarts!!!
this works like a charm. I did use a different approach to have access look through all the records and find the ones reminders set. I used a "For;Next" loop get past the first record only issue. Also found that this code worked well:
Private Sub Form_Timer()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtActAppt As Date
Dim Reminder As Date
Dim cnn As Connection

DoCmd.Requery
Set db = CurrentDb



strSQL = "SELECT * FROM YourTable "
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
Reminder = ((rst!YourDate + rst!YourTime)-Eval _
(rst!RemindTime))
dtActAppt = (rst!YourDate + rst!YourTime)
If Now > Reminder And dtActAppt > Now Then
'MsgBox "Check Your Calendar You have an upcomming event!"
Beep
End If
rst.MoveNext
Loop
End Sub
Thanks again I hope this will help someoneelse
whill1

 

[suddu]

I Thank the forum members for great help.

Here is another issue I have. How do I set the alarm on to my appointment; where I have fields with upcoming events but at the same time have fields when that event was completed with its own date. I want the message to keep poping with the required info about the pending procedure till the event is completed and the entry made in the form for date called for that event.

For example, i have fields as date enrolled and then I have field on a second subform about upcoming appointments based on date enrolled and they come up with datediff function now i want the alarm to pop up with the patient name and phone # just on the day of the event and keep poping up till the next entry on the subform for that event, in this case the call completed date is entered. and then the message stops.

Thanks

 

[whill1]

Suddu,
First have you read this whole thread and do you understand all or part of it? Assuming that you understand most of it- You will probably will want to use the timer function for this. On the timer interval you choose run code similiar to this.
Private Sub Form_Timer()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtActAppt As Date
Dim Reminder As Date
Dim cnn As Connection

DoCmd.Requery
Set db = CurrentDb

strSQL = "SELECT * FROM YourTable "
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
Reminder = (rst!enrollDate)
dtActAppt = (rst!CallCompletedDate)
If Date()=Reminder And dtActAppt < Date() Then
MsgBox "Your message here !"
or you can enter code here to open any form
Beep
End If
rst.MoveNext
Loop
End Sub

Just make sure that you attach this code to a form that will remain open. If the form is not open the reminder will not work. You will have to play around with this to get it to fit your needs but it provides some info. I would also suggest reading all the threads you can find on the topic
I hope this helps.
whill1

 

[suddu]

Thanks Whill1 for your reply. I am a bit unclear on how to really set this up. SO let me explain a bit further.

My form contains a field named Date_Enrolled . Now depending on this date the sub form named visits conatins fields like 3 month reminder; 9 month reminder and so on. The values in these fields are calculated based on the Date_Enrolled value and it does show in the form when opened. Now my problem is for access to look for the value in 3 month reminder and send up a msg box or warning that this particular record is due and keep repeating this till the person makes the callto the patient and enters the date in another field named 3 month called date.

I am new to VB coding , have been able to understand quite a bit but not a whole lot to fix problems like these. So if you could use names from my fields and let me know hoe to overcome this issue.

thanks

 

[suddu]

Thanks for this post, I was able to use the microsoft fix to get the values in outlook but Now i want to get multiple appointment times based on a single date and they are in the future with 3, 9 and 12 months increment.

So the problem is how to insert all the values in their respective calender with one single click using microsoft code and also how to include in the subject 3 to 4 variables in outlook, presently My sibject code reds as Me.SSN and only gives Social Security number in outlook.