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:
Code:
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
