Can Access send reminders from a database to a user?

[Guest]

I designed a rental database that tracks rentals of equipment. Can Access
send a reminder to me (even through Outlook) that will tell me when a rental
is due back according to data entered in the database?

 

[Joseph Meehan]

I designed a rental database that tracks rentals of equipment. Can
Access send a reminder to me (even through Outlook) that will tell me
when a rental is due back according to data entered in the database?

Assuming Access is running, you can program it to check (when first run,
every hour or whatever) for any due dates equal to or over the current date
and time. I understand it can send you e-mail if needed, but I have only
used it to alert me directly.

 

[Guest]

Great! That sounds like what I am looking for. What would I look under in
help to find out how to do this? I cannot find any key words in the index.
Thanks

 

[Alex White MCDBA MCSE]

The way I normally deal with this problem/challenge is a hidden form within
Access that has a form timer that checks every x minutes/hours for entries
within an event table.

The other way if you are using outlook, is to automate outlook setting an
appointment, outlook would have to be open for the reminders to happen.

http://support.microsoft.com/?kbid=209963

Hope it helps.

 

[Joseph Meehan]

Great! That sounds like what I am looking for. What would I look
under in help to find out how to do this? I cannot find any key words
in the index. Thanks

I have used the form Alex was talking about and I have written some VBA.
I don't have any examples since I retired and left all that stuff at work.

 

[Alex White MCDBA MCSE]

The below code could be better written but it should give you some idea of
what to do, all it does is every time the form_timer goes off, queries the
TblEvents Table for Events that have passed a specific time that have not
been actioned, for a specific person and if that happens then a form pop's
up and allows you to jump to a specific record. Sorry for the long-winded
code, wrote a long time ago, could do with some updating.

Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
Dim SQL As String
Dim intMinutes As Integer
Dim adoEvents As New adoDB.Recordset
SQL = "SELECT TblEvents.pEventNo, TblEvents.Event_Scheduled_For_Date AS
[Event Date], "
SQL = SQL & "TblEvents.Event_Scheduled_For_Time AS [Event Time], "
SQL = SQL & "TblEvents.Event_Description AS Description, "
SQL = SQL & "TblEvents.Event_Notice, "
SQL = SQL & "TblEvents.For_CONSULTANT_INITIALS AS [For Cons], "
SQL = SQL & "TblEvents.Actioned AS Done, "
SQL = SQL & "TblEvents.Entered_By_CONSULTANT_INITIALS AS [Entered By] "
SQL = SQL & "FROM TblEvents Where TblEvents.For_CONSULTANT_INITIALS = '"
& glbUI & "' And TblEvents.Event_Scheduled_For_Date <=#" &
Trim(str(Month(Format(Now(), "D/M/YYYY")))) & "/" &
Trim(str(Day(Format(Now(), "D/M/YYYY")))) & "/" &
Trim(str(Year(Format(Now(), "D/M/YYYY")))) & "#"
SQL = SQL & " And TblEvents.Actioned=False Order By
Event_Scheduled_For_Date, Event_Scheduled_For_Time "
With adoEvents
If .State = adStateOpen Then
.Close
End If
.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If .RecordCount > 0 Then
Do While Not .EOF
If Format(.Fields("Event Date"), "D/M/YYYY") = Format(Now(),
"D/M/YYYY") Then
intMinutes = DateDiff("n", Format(Now(), "hh:mm:ss"),
Format(.Fields("Event Time"), "hh:mm:ss"))
If intMinutes < .Fields("Event_Notice").VALUE Then
glbpEventNo = .Fields("pEventNo").VALUE
DoCmd.OpenForm "FrmEventNotification", acNormal, , ,
, , vbModal
Form_FrmEventNotification.pEventNo.VALUE =
..Fields("pEventNo").VALUE
.Close
GoTo Exit_Form_Timer
End If
Else
glbpEventNo = .Fields("pEventNo").VALUE
DoCmd.OpenForm "FrmEventNotification", acNormal, , , , ,
vbModal
Form_FrmEventNotification.pEventNo.VALUE =
..Fields("pEventNo").VALUE
.Close
GoTo Exit_Form_Timer
End If
.MoveNext
Loop
End If
.Close
End With
Exit_Form_Timer:
Exit Sub
Err_Form_Timer:
msgbox err.number & vbcrlf & err.description
Resume Next
End Sub