Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Cool Unanswered: Automatically Send Email When Date in Table = Current Date

    Hi All !

    Thanks for all the help i've been getting here, im making good progress. One day i'll be proficient enough to save some other geek's life

    Here's what i'm trying to do.

    A table has a date column. I want to send out an email to X number of recipients when that date/time e.g Nov 01, 2004 5pm is reached. Is there some way i can use a macro or setup a module with some VB code or some trigger in Access 2000 to send an email when a certain date is reached.

    On a similar note, when that date is reached, i want to run a query.

    Any views/suggestions?

    Thanks!
    R

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi.

    The sending of an email is fairly straightforward. Investigate the DoCmd.SendObject method.

    What seems more difficult is triggering this method. Presumably you want to run something each time the user logs on/starts a session. This again is fairly straightforward but you may want to preserve a date or some other information between sessions. I know of three options for this.

    You can store the information in a table - gets a bit complicated if you have more than one user and your tables are central.

    You can use the Registry. Investigate SaveSettings and the related methods.

    Finally you can use SystemProperties. Believe it or not these are persistent between sessions and you can merrily add as many properties as you like.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    What I have done Is open Forum Hidden and in the Timer Event run the Code
    just remenber to set the TimerInterval Right
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2004
    Posts
    19

    Wink

    Ok guys,

    i logged in using another account, i started the thread. I think both of you guys didnt get it so let me put some more details out.

    There's a schedule table with workshop dates in a date column. I want it like this.. A workshop with Nov 2,2004 as the date is setup. I want an email to be automatically sent out on Nov 1, 2004 as a reminder of the workshop to all registrants (registrant info is stored in another table with proper FKey etc)

    Now how to actually come up with a trigger or module that activates based on time?
    Since this access DB is a backend with JSP/beans as the front end, i researched and found i cant use a timer in a JSP/bean so it comes down to using something in ACCESS or trashing the idea

    R

  5. #5
    Join Date
    Apr 2004
    Posts
    8

    Thumbs up

    If you're doing it in VB, you can use a Timer control. In the Timer_Tick event, add code to poll the database and check to see if Now() >= TriggerDate.

    Hope this helps -
    Steve

  6. #6
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    R

    If your Access application is a continous 'always-up' system then the only way to go is with the On Timer event as suggested by Myle and Steve. I've just done some calculations and you can set the timer interval between one thousandth of a second and 596 hours!

    However if you open and close the Access application on a daily basis then you have known events you can trap and test to see whether you have actioned the reminder notices or not.

    In either case you may want to store information about the status of the reminder facility. There are three ways I know to pass data between sessions: a table, the Registry and the System Properties. I use the Registry approach for a feature that emails me an Excel table from my Access system in the Philippines to my PC here in Saudi every three days. In this particular case the action is triggered by the Filipino user closing the system.
    Rod

    fe_rod@hotmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •