sending e-mail alerts prompted via access

[HD]

I use access 2007 to manage our permitting proscess. Is there anyway i could
write a program in access to prompt it to send me an e-mail alert based on a
date in the date field. I want to set it up to notify me that x-number of
days has passed since a permit was issued and no activity on that permit has
be observed.

Thanks

 

[Magius96]

There's a million and one different ways you could approach this. Keep that
in mind as I present to you my typical approach.

I'm first going to make a few assumptions: I'm going to assume that you
want this run once a day, no matter how many times the database gets opened.
I'm going to assume that the database gets opened at least once a day. I'm
going to assume that there is a form that is opened everytime the database is
used. Finally I'm going to assume that you are an experienced Access
Developer, if not let me know and I'll go into fine grained detail.

First, create a new table with only one field. Make this field a Date field
with a name like "LastRan".

Secondly, build yourself a query that selects the data that you want sent.

Third, build a report based off the query you just built.

Now, enter the code screen for the form you use everyday, and in the OnLoad
event have your application check to see if today's date is present in the
table you created earlier. If it's not present, then use DoCmd.Send to send
the report by email, and use DoCmd.RunSQL to add todays date into the table.

This way, the report will be mailed out only once a day. Personally I think
this is by far the easiest method, though I know there are a lot more methods.

 

[HD]

Thank you Magius96. That did help

First, all your assumptions are correct and i am by no means a developer.
However i do believe i understand the concept. You are essentially creating a
report once a day of the records meeting the date listed in the lastran date.
So lets say i issue 5 permits Jan 1st and i want to check the status every 90
days then my lastran date would be 90 days from Jan 1st.

Or, is the lastran the timeframe i have established for a followup and it
only generates a report of any permits that have not been open/modified
within this time frame?

I also just thought about the possibility of somehow linking the dates i
issue with my microsoft outlook calender. I could have another date field for
the followup date and that would automatically be entered into the outlook
calender and then that program would issue the reminder.

 

[Magius96]

Actually lastran should always contain the date that the report was last built.

To select records where the date field is over 90 days simply use this in
the query as a field filter
<now()-90

 

[mattiss]

Hello Magius,

I have a similar question. I am trying to set up an automatic e-mail alert
to users 5 days before a deadline date field. I don't want to ask you to
write me a book, but your reply lost me at "now enter the code screen".

thanks,
matt