Creating an Event Reminder

[txaccess]

My database application includes a series of activities, events and dates that requite that a User is reminded of upcoming activities (ala Outlook). For example, contract renewals - remind the contract owner of upcoming contracts due to be renewed.

Is there a way to code a schdeduling function that trawls the database for upcoming dates in the background and if one is found that meets the criteria, will be displayed to the event owner automatically - I want to avoid the user having to run reports to find events and activities that are upcoming.

Thanking everyone in advance.

I haven't failed, I have just found 10,000 ways that it won't work!

 

[SiJP]

Loads of ways...

One is to create a function that opens a recordset to your various tables, looking for dates within a certain date range, and outputs the dates into an email...

What sort of vba level are you? Can help you out no problems

------------------------
Hit any User to continue

 

[txaccess]

Thanks SiJP

My VBA skills are self taught and evolving. I would have to say that at the moment I can do the basics, and have tackled some more advanced code - not scared of hiving anything a try! Your help would be appreciated.

I haven't failed, I have just found 10,000 ways that it won't work!

 

[SiJP]

Alrighty then... let me fiddle with some stuff, and I'll drop some ideas your way! Will be leaving the office very soon, so will post back later when I'm home..

------------------------
Hit any User to continue

 

[txaccess]

Thanks, I look forward to reading your ideas

I haven't failed, I have just found 10,000 ways that it won't work!

 

[SiJP]

Ok, maybe I haven't gone home yet but....

1) Create a table within your database:

tblEventReminderFields
Field: ID as Autonumber (pk)
Field: TableName as Text (50)
Field: DateField as Date/Time
Field: UserField as Text (50)
Field: IDField as Number

What the intention is, will be to use this table to know which fields in what tables to check!

2) I'm assumin geach contract has a user name stored against it? Well, if you haven't already, create a table to relate the username iwth an email address.. potentially in your existing usertable, if not structure as follows:

tblUsersEmails
Field: UserID as Autonumber (pk)
Field: UserName as Text (50)
Field: eMailAddress as Text (255)

3) Create a new Module: basEventReminder

Next, we want to create a function:

Public Function fCheckEvents()
    Dim strEmailTo As String
    Dim rsEventField As DAO.Recordset
    Dim rsEventData As DAO.Recordset
    Dim rsUsers As DAO.Recordset
    Set rsEventField = CurrentDb.OpenRecordset("tblEventReminderFields")
    
    If rsEventField.BOF Then
        Exit Function 'just in case there aren't any records, we dont want any nasty crashes
    End If
    
    rsEventField.MoveFirst
    
    While Not rsEventField.EOF
        Set rsEventData = CurrentDb.OpenRecordset(rsEventField("TableName"))
        
        If rsEventData.BOF Then
            Exit Function 'just in case there aren't any records, we dont want any nasty crashes
        End If
        
        rsEventData.MoveFirst
        While Not rsEventData.EOF
            'check out each field in here..
            If rsEventData(rsEventField("DateField")) >= (Date + 5) Then   'e.g. flags anything in next five days
            
                'get who to send the email to
                Set rsUsers = CurrentDb.OpenRecordset("SELECT eMailAddress FROM tblUsersEmails WHERE UserName = '" & rsEventData(rsEventField("UserField")) & "'")
                rsUsers.MoveFirst
                While Not rsUsers.EOF
                    If strEmailTo <> "" Then
                        strEmailTo = strEmailTo & "; " & rsUsers!eMailAddress
                    Else
                        strEmailTo = rsUsers!eMailAddress
                    End If
                    rsUsers.MoveNext
                Wend
                
                'send the email
                DoCmd.SendObject , "", "", strEmailTo, "", "", "Forthcoming Event", "Your forthcoming event is for record " & rsEventData(rsEventField("IDField")) & " in table " & rsEventField("TableName") & "...", False, ""
            End If
            rsEventData.MoveNext
        Wend
        rsEventField.MoveNext
    Wend
End Function

4) Then all you need to do is add an On_Open event to a form that is called on startup, that contains the code: Call fCheckEvents .. or create an autoexec macro that calls this function.. either way..

DISCLAIMER:

I have created this on the fly.. its air code so will need a fair amout of tweaking.. but this should give you a starting point

I've assumed you are using MS Outlook as your email client.

HTH's

------------------------
Hit any User to continue

 

[txaccess]

Thanks, I will give this a try. If I attach the code to the Op Open Event, will the reminder only be generated when the form is opened? Is there a way to have the reminder pop up automatically given a defined parameter - e.g. send reminder to contract owner 30 day's before renewal event. This should not be dependent on the user opening the form, even if they do not log in an Outlook e-mail could be genarated.

To answer your questions, each event is associated with a user/owner & I am using Outlook 2003 as the e-mail.

Thanks

I haven't failed, I have just found 10,000 ways that it won't work!

 

[txaccess]

Thanks, I will give this a try. If I attach the code to the On Open Event, will the reminder only be generated when the form is opened? Is there a way to have the reminder pop up automatically given a defined parameter - e.g. send reminder to contract owner 30 day's before renewal event. This should not be dependent on the user opening the form, even if they do not log in an Outlook e-mail could be genarated.

To answer your questions, each event is associated with a user/owner & I am using Outlook 2003 as the e-mail.

Thanks

I haven't failed, I have just found 10,000 ways that it won't work!

 

[txaccess]

Ooops, must have hit submit twice on that last post!

I haven't failed, I have just found 10,000 ways that it won't work!

 

[SiJP]

will the reminder only be generated when the form is opened?

Yes - unless.... create a form that is hidden in the background.. set the timer to run the code every xxx mins

Is there a way to have the reminder pop up automatically given a defined parameter - e.g. send reminder to contract owner 30 day's before renewal event.

Yep of course.. you can change the code to 30 days.. I set it to check the dates within 5 days... but you could change it

(1) This should not be dependent on the user opening the form, (2) even if they do not log in an Outlook e-mail could be genarated.

1) See the above bit about form timers..
2) Alternatively to email, you could add a record into a new table,.. have a form pop up that shows all the reminders in a table..

The complete alternative to the above code is to create a form to filter records that are relevant (e.g. within the last 30 days) for that user, and have this form pop up every however many minutes... more coding required (more time than I have unfortunately!!!)

In all, its a great idea to have .. if I was personally doing this, I would have the contract reminders emailed - perhaps you could run the database on a stand-alone pc (kind of like a server) and have the email open all the time on this - that way you are not relying on that user having his/her email open.

One small thing about outlook 2003.. it tends to kick macro security in for any vb apps that call the sendobject function meaning you need a user sitting at the PC to click OK on every message that comes up, in order to send that mail... there is a free app on the net somwhere (use google) called ClickMe that can get around this - I use it for the above scanrio (server / emails) in one of my apps with a great deal of success. Or create your own SMTP app...




------------------------
Hit any User to continue

 

[txaccess]

Great stuff - that should all keep me busy for the foreseeable. Thank you. I will let you know how it turns out.

I haven't failed, I have just found 10,000 ways that it won't work!

 

[SgtJarrow]

txaccess,

I am coming in on the end of this but let me make a suggestion. You mention not being scared to dive into some VBA....why not use automation for some of this?

You go in a create a record of some sort...and an Outlook calendar or task is created with reminders set. This takes advantage of Outlook already having the reminder tools. Now, things become a bit more tricky if you are not the person to be reminded...but you could simply send the person a meeting request or task notification which when they accept will be added to their Outlook as appropriate.

I have used this in the past and love it. Automation like this, while a bit of coding, clearly makes sense.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com