Can I set an alert for a date column?

M

mix

Hi.

I dont think Access can do this, but maybe one of you have tried it. I have
a table storing archive data and have set "destroy by date", I want to set up
an alert which will send an e-mail to alert someone that the archived data
(boxes full of paperwork) need to be destroyed. Can Access do this, I know
SQL Server can do it in the form of a trigger, but wanted to know if it could
be set in MS Access.

Thanks in advance.
 
A

Arvin Meyer

Anything that can be done with a trigger can be done with an Access form.
The difference, of course, is that a trigger is enforced at the table level
by the data engine. Access can either use the before or after update events,
or can use the form's open/load/close/unload events to run code which
accomplishes the same thing as a trigger. In your case, I'd use the form's
open event to check if there are records which conform to your criteria and
notify the user.

You can use email, but if you want it fully automatic, you will need
something to get around Outlook's security messages. For that, look at
Redemption:

http://www.dimastr.com/redemption/

or an smtp email program.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
M

mix

Thank you for your reply and suggestions. Would you be able to tell me where
I could find an example of the code I will need to add to the On open event?
 
A

Arvin Meyer [MVP]

Here's a sample of some code that checks a date in 2 tables and asks if the
user wants to run a transaction to update several tables. the transaction
(RunTrans) is not posted here for security reasons:

Private Sub Form_Open(Cancel As Integer)
'***************************************************************
' Name: Form_Open
' Purpose: Post Effective Date Updates to Items and Option Items Tables
'
' Author: Arvin Meyer
' Date: March 27, 2002
' Comment: Check if wanted, then run in a Transaction
'
'***************************************************************
On Error GoTo ErrorHandler
Dim rstItems As DAO.Recordset
Dim rstOptions As DAO.Recordset
Dim db As Database
Dim lngCount As Long

Set db = CurrentDb
Set rstItems = db.OpenRecordset("qryItemsToPost", dbOpenSnapshot)
Set rstOptions = db.OpenRecordset("qryOptionItemsToPost", dbOpenSnapshot)

' If either recordset has items to post, lngCount will be >0
lngCount = rstItems.RecordCount + rstOptions.RecordCount

If lngCount > 0 Then
If MsgBox("You have records with effective dates that need posting." & _
vbCrLf & vbCrLf & "POST THEM?", vbYesNo, "Post Records?") = vbYes
Then

Call RunTrans

End If
End If

Exit_Here:
On Error Resume Next
rstItems.Close
Set rstItems = Nothing
rstOptions.Close
Set rstOptions = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_Here
End Select
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top