Pop-up message when date is due

[Faio]

Can anyone suggest a way to do this?

I have an employees database which is something like this.

PF#, Name, Startdate, Increment date.
----------------------------------------
20041, John, 30/1/2004,30/1/2004

Whenever the now() date is 30/1/2005 or 2 days before the due date, a
notification message will be send to this address (e-mail address removed).

Can anyone help me on this?

 

[Steve Schapel]

Faio,

From the example you gave, it is not clear what you are referring to as
the "due date". But the basic concept of "how" to do this is reasonably
straightforward. For example, you might end up with code that looks
something like this...
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM Employees WHERE
[DueDate] = Date()+2")
Do Until rst.EOF
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "almost
due", "'" & ![Name] & "' is due in 2 days!"
Loop
rst.Close
Set rst = Nothing

However, you will need to decide "when" this will happen. If the
database is opened afresh every day, but normally only once per day, you
could put the code on the Open event of a form which always opens when
the database opens. Or, you might rely on the user of the database to
manually run this by clicking a command button. Or, you might need to
have a table where the process also updates a date field to the current
date, and then refer to this field within another If...Then clause
within the code, to ensure that the same email only gets sent once. Or,
if the database does not get used every day, you might need to use
Windows Task Manager, or some other scheduling utility, to run it via a
command line which opens the database and activates a macro. So you
see, a lot depends on the usage of the database and your specific
requirements.

 

[Faio]

Thanks Steve.

Sorry for not given enough details for my question.

What I mean is

E.g

PF#, Name, Startdate, Increment date.
----------------------------------------
20041, John, 30/1/2004,30/1/2004

Whenever a user opens a database, and the computer's date is 28/1/2005 (2
days before) 30/1/2005, a
notification message will be send to this address (e-mail address removed).

Do you get it now?

Thanks and hope to hear your view.


To clarify this, the due date I am referring

Faio,

From the example you gave, it is not clear what you are referring to as
the "due date". But the basic concept of "how" to do this is reasonably
straightforward. For example, you might end up with code that looks
something like this...
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM Employees WHERE
[DueDate] = Date()+2")
Do Until rst.EOF
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "almost
due", "'" & ![Name] & "' is due in 2 days!"
Loop
rst.Close
Set rst = Nothing

However, you will need to decide "when" this will happen. If the
database is opened afresh every day, but normally only once per day, you
could put the code on the Open event of a form which always opens when
the database opens. Or, you might rely on the user of the database to
manually run this by clicking a command button. Or, you might need to
have a table where the process also updates a date field to the current
date, and then refer to this field within another If...Then clause
within the code, to ensure that the same email only gets sent once. Or,
if the database does not get used every day, you might need to use
Windows Task Manager, or some other scheduling utility, to run it via a
command line which opens the database and activates a macro. So you
see, a lot depends on the usage of the database and your specific
requirements.

--
Steve Schapel, Microsoft Access MVP

Can anyone suggest a way to do this?

I have an employees database which is something like this.

PF#, Name, Startdate, Increment date.
----------------------------------------
20041, John, 30/1/2004,30/1/2004

Whenever the now() date is 30/1/2005 or 2 days before the due date, a
notification message will be send to this address (e-mail address removed).

Can anyone help me on this?

 

[Steve Schapel]

Faio,

Well, then it's pretty much what I said. Assuming you have a form that
opens whenever you open your database, put the code on the Open event of
that form. And I guess it would be more like...
Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM Employees WHERE
DateAdd('yyyy',1,[Startdate])-2 = Date()"

By the way, on an unrelated matter, 'Name' is a "reserved word" (has a
special meaning) in Access, and as such it should not be used as the
name of a field or control or database object. I suggest change to
Employee or some such.

 

[Faio]

Thanks Steve,

It really help.

Faio,

Well, then it's pretty much what I said. Assuming you have a form that
opens whenever you open your database, put the code on the Open event of
that form. And I guess it would be more like...
Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM Employees WHERE
DateAdd('yyyy',1,[Startdate])-2 = Date()"

By the way, on an unrelated matter, 'Name' is a "reserved word" (has a
special meaning) in Access, and as such it should not be used as the
name of a field or control or database object. I suggest change to
Employee or some such.

--
Steve Schapel, Microsoft Access MVP

Thanks Steve.

Sorry for not given enough details for my question.

What I mean is

E.g

PF#, Name, Startdate, Increment date.
----------------------------------------
20041, John, 30/1/2004,30/1/2004

Whenever a user opens a database, and the computer's date is 28/1/2005 (2
days before) 30/1/2005, a
notification message will be send to this address (e-mail address removed).

Do you get it now?

Thanks and hope to hear your view.