Notifications/Alarms (1 Viewer)

[Justin]

Is there I way that I can setup a notification alarm in Access? I am doing a database for storing staff contracts and I have a field for "contract end date" and I want to set something up so that I am notified by a dialog box when the length of the agreement is 6 months from expiring.
I have the following bit of code which picks up the correct number of records, but the persons name does not change. Can anyone help please?
thanks

Dim dbs As Database, rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb
'strSQL = "SELECT * FROM Main WHERE (((Main.[Termination Date]) <= (Date() + 180)))"
strSQL = "SELECT Main.[Presenter Name], Main.[Termination Date]FROM Main WHERE (((Main.[Termination Date])<=(Date()+180)))"
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF
MsgBox ("The contract of presenter " & [Presenter Name] & " is due to expire in approximately 6 months time.")
DoCmd.Requery
'Debug.Print rst.RecordCount
rst.MoveNext
Wend
rst.Close
Set dbs = Nothing

 

[directormac]

A couple of thoughts...

You can set up an "alarm" by adding a checking routine that gets called from the autoexec macro. This will run whatever checks you want each time the databse is started.

Have you tried running this without the requery?

Depending on the number of presenters you have, it might be easier on the user if you open a form listing all the ones whose contracts are about to come up, rather than have a separate message box for each one. Or make it a report that is brought up in print preview, that way the user can hit the print button and have a hardcopy list of those folks to serve as a reminder.

Just some random ideas...

--Feeling-Random Mac

 

[Justin]

thanks I will try those out !