reminder by date

[steve111]

hi ,

I have a table called schedule maintainence
in that table I have a "date" field
is it possible that if " todays" date = a date in the field date a e-mail can be sent to me to remind me there is a scheduled maintainence to be done.

thanks
steve

 

[MarkK]

The issue here is what will trigger this action. Does a user open the database at least once a day? If so, run code to do this check when the database is opened. Another option is to run a Windows scheduled task. Create a database that only does this one job and then shuts itself down, and schedule windows to open it once a day.
hth

 

[CJ_London]

If you are using outlook, have some code to update outlook with an event with reminders/repeats - but then outlook needs to be open.... but then it would be for you to receive emails

 

[JLCantara]

@Steve: yes. Create a scheduled job that will start your Access app auto. at power on. Create a default (on start) proc or form that will do both: display a warning message and sending an Email. If you are not using the app, someone must (isn't it) so do the same on that PC.

 

[vbaInet]

Another option is to run a Windows scheduled task. Create a database that only does this one job and then shuts itself down, and schedule windows to open it once a day.
hth

I like the idea of creating a scheduled task in Windows and I think that you're able to send an e-mail via the task too; unless that functionality no longer exists, in which case the task could fire up the lone db MarkK mentioned (or a script), that will handle sending the email.

 

[vbaInet]

CJ, were you working on some code that does scheduling? I vaguely remember you mentioning this.

Calling command prompt's schtasks command with the "/create" switch will enable you create the task. This can be run through VBA.Shell()

 

[CJ_London]

CJ, were you working on some code that does scheduling?

I was - it basically mimics the windows scheduling form but runs from within access. The client has a number of machines but they are not always connected to the network so it was decided not to update windows scheduler. So we kept the 'front end' so users can schedule the tasks and define what those tasks may be but simplified to run on the database open event - it scans through the list of uncompleted tasks to see if any are relevant for today and then runs them in one hit. We had other issues in that we also had to create a 'pre processing check' to ensure all data was in place before running - and if not abort and highlight missing data for someone to fix before running again manually.

I don't think it is particularly relevant for the OP - way more complex than required. All he wants is a reminder email based on a date in a table. That is very straightforward

 dim  rst as dao.recordset
 set rst=currentdb.openrecordset("SELECT * FROM myTable WHERE fldDate=Date()")
 if not rst.eof then 'there are dates for today
     DoCmd.SendObject acSendNoObject, , , [EMAIL="myname@myURL"]myname@myURL[/EMAIL], , , "You have scheduled maintenance today", "Don't forget!"
 end if
 set rst=nothing

What matters is what triggers the event - opening the db or a form in the db, outlook or windows scheduler? all of which are irrelevant if the appropriate machine/app is not switched on/active. If the OP has a server or another machine which is permanently on, then the task can be set there (providing he has an email server), but he's not responding to any of the suggestions so no idea whether that's possible or not