Solved

How can i create a reminder in Access!?

Posted on 2009-04-15
20
620 Views
Last Modified: 2012-05-06
How can i create a reminder in access to remind the responsible employee.. for example, the order or task deadline is on 16/april.2009. an email or a popup message will start every time he logs into the file or an email would be sent to him..

0
Comment
Question by:mwael
20 Comments
 
LVL 84
ID: 24146339
How do you determine the ResponsibleEmployee, and how do you "match" that person to the person currently viewing the database? You can get the Windows Username of the machine the user is using, but if you do not have those Usernames matched to the ResponsibleEmployee, then there's no way for Access to know which of your Employees is logged in.

If you could tell us a bit more about your setup, we could help further.

FWIW, it's pretty simple to show a message to the user:

Msgbox Me.TaskName & " is due on " & Me.TaskDueDate & " and has been assigned to you."

Something like this could be run from a Form (the Me.TaskName and Me.TaskDate are fictitious names; you'd have to change them to match your project).
0
 

Author Comment

by:mwael
ID: 24146478
yes im assigning a user name and pass for every user and im creating a table for our employee.but still hinking how to link that :S
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24147154
Here's one approach ...

1.) Build a Task Table that has the user name, task and task details (date created, date due, etc.).

2.) Build a Reminders form where the Record Source is your Task Table and set it to Modal so that it will keep the focus until closed.

3.) Set an Open Event of your main form to open the Reminders form with a where clause ... "[user_name]='" & CurrentUser() & "'"

This will open the Reminders showing all tasks that has been assigned to the current user each time he/she opens the application.  The Modal setting will force them to close the reminder before they can continue.

Just an idea ...

ET
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24149253
if you have MS Access installed, what email tool are you using? Is it MS Outlook? If so then I can provide some code to automate the creation of calendar events
0
 

Author Comment

by:mwael
ID: 24155354
yes im using ms outlook
0
 
LVL 1

Accepted Solution

by:
NicholasSmith earned 500 total points
ID: 24155425
you'll need to reference the msoutl.olb object from your MS Office directory in program files first. if you get any problems with this then let me know
Public Sub AddCalendarEvent(ByVal datDateToCall As Date, ByVal strSubject As String, ByVal strRetailerName As String, _

 ByVal strContactName As String, ByVal strPhoneNumber As String)

  Dim objOutlook      As Outlook.Application

  Dim objNameSpace    As Outlook.NameSpace

  Dim objFolder       As Outlook.MAPIFolder

  Dim objAppointment  As Outlook.AppointmentItem

  

  'If you start Microsoft Outlook, run a second program that accesses Outlook, and then exit Outlook while

  'you still have the second program running on your computer, you may receive an error message that is

  'similar to the following: The remote server machine does not exist or is unavailable (error 462)
 

  On Error Resume Next

  Set objOutlook = Outlook.Application

  

  If Err.Number = 462 Then

    'reinitializes the Outlook object model if above error occurs

    Set objOutlook = CreateObject("Outlook.Application")

    Err.Clear

  End If

  

  On Error GoTo err_AddCalendarEvent

  Set objNameSpace = objOutlook.GetNamespace("MAPI")

  'Set objFolder = GetOutlookCalendarFolder

  

  Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

  With objAppointment

    .ReminderMinutesBeforeStart = 0

    .Start = datDateToCall

    .End = datDateToCall

    .Subject = strSubject & ": " & strRetailerName

    .Body = "Please Call..." & vbCrLf & vbCrLf & _

     "Retailer: " & strRetailerName & vbCrLf & _

     "Contact Name: " & strContactName & vbCrLf & _

     "Phone Number: " & strPhoneNumber 

    .Save

  End With

  

exit_AddCalendarEvent:

  On Error Resume Next

  Set objAppointment = Nothing

  Set objOutlook = Nothing

  Set objNameSpace = Nothing

  Set objFolder = Nothing

  Exit Sub

  

err_AddCalendarEvent:

  MsgBox Err.Number & " " & Err.Description, vbCritical + vbOKOnly, "Error creating Calendar Event"

  Err.Clear

  Resume exit_AddCalendarEvent

End Sub

Open in new window

0
 

Author Comment

by:mwael
ID: 24204036
so this where should i put it? in a new module?or what?
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24204535
it would go in a new module if you want to access it from other forms or subroutines
0
 

Author Comment

by:mwael
ID: 24214646
ok i inserted it into new module
then?
0
 

Author Comment

by:mwael
ID: 24214649
im so bad in modules amcros and codes
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24214747
The line:

AddCalendarEvent date(), "call Back", "Joe Bloggs Enterprises", "Mr Bloggs", "0800 100 100"

would then add the record using the above parameters from any piece of code.

NOTE: you do need a reference to MSOutl.olb which is found in the Office10 directory of your Microsoft Office installation
0
 

Author Comment

by:mwael
ID: 24214832
:S
 
please can we do it step by step?
ok.. o add the first codes in a module.. then i have to add the above line to it?
so afterthat the reponsiple employee will recieve an email automaticlly?
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24214990
if you are unsure how to implement the above code into an application then what i'd be doing is pretty much writing it all for you which would then become a larger task as opposed to offering some guidance in the right direction.
0
 

Author Comment

by:mwael
ID: 24215034
then?
it ok.. u can do it! i need only tips
 
i refrenced the outlook in access.. and i inserted the codes in the module
 
then?
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24215076
if you inserted the code in to your modules and referenced the outlook object then the line:

AddCalendarEvent date(), "call Back", "Joe Bloggs Enterprises", "Mr Bloggs", "0800 100 100"

would add a call back for today for Mr Bloggs of Joe Bloggs Enterprises to call back on 0800 100 100.

obvuiously you would use your own parameters based on whatever criteria you are using.

this call back is added to microsoft outlook into the calendar and will prompt the user as a normal calendar event would. there is no further help that you need
0
 

Author Comment

by:mwael
ID: 24215118
ok thnx dear.. :)
0
 

Author Comment

by:mwael
ID: 24215350
it keeps giving me error!
 
i called the module i built but..
0
 

Author Comment

by:mwael
ID: 24216271
r u sure it sends email to responsiple employee?
0
 
LVL 1

Expert Comment

by:NicholasSmith
ID: 24216533
it wont create an email but would pop up as a calendar event on the outlook of the person who created the event
0
 

Author Comment

by:mwael
ID: 24216618
aha... great.. but i have many many may users.... will that be possible in this case?
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now