Results 1 to 8 of 8
  1. #1
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88

    Trigger A Reminder to Occur At A Later Date


    Does anyone know if it is possible and if so how I would accomplish the following scenario in Access 2010. I have created a data base for a funeral home. One of my tables includes the date of death of the individual (DOD) (i.e. 04/16/2014). From that date I want to trigger a reminder to follow up with a family letter 6 months from that date. Would I do this with a macro or would I do this with VBA Script? I am a novice thanks to everyone for their tolerance and patience with Access and learning as I go thanks to this Board I have learned so much.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access is 'event driven'. Something must happen, like a button click

    Do this with code behind a button that opens a report showing records that meet that criteria.

    Or set a form to open by default when db opens and code in form Open event that automatically opens the report. This would mean opening and closing the db daily.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    I follow what that Access 2010 is event driven. An understanding the logic which was presented how would I configure event to trigger when I open Access 2010? How would Macro or VBA look which would generate the massage that it has been “6 months since Mr. John Smith Has Died Please Send Update letter to Family” So I would need message to generate the clients real name in message and be triggered by date of death.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Assuming there could be multiple clients that match this criteria, simplest approach is to open a query or report that lists those clients.

    VBA code in the form Open event:

    DoCmd.OpenReport "UpdateLetterReport", acViewPreview
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    Ok but how would I actually write in query to only look for records from the DOD. Which for the sake this say is 11/20/2013 which is approx 6 months prior to current date. in the DOD column on report query, would I write = < Currentdate by 6 months? Not sure on how to actually write the previous statement

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Criteria under the DOD field:

    BETWEEN DateDiff("m", -6, Date()) AND Date()

    or

    BETWEEN Date() - 180 AND Date()

    or

    Date() - 180

    or

    <=Date() - 180

    Is there a field to document that followup letter was sent - a date or checkbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    No but I think that may be a wise idea to have a field that shows it was sent. Do you think it would be wise to set it to generate email reminder from that query?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If there is such a field, it could be used to aid in filtering records and prevent repeat contacts. Wise? Isn't that what you want the query for?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-01-2013, 12:24 PM
  2. Replies: 1
    Last Post: 12-15-2011, 08:47 AM
  3. Control when Saves occur
    By caddcop in forum Forms
    Replies: 0
    Last Post: 04-05-2011, 04:15 PM
  4. advance date reminder
    By Denis in forum Database Design
    Replies: 1
    Last Post: 11-14-2010, 07:40 AM
  5. date trigger
    By hyperionfall in forum Access
    Replies: 1
    Last Post: 08-08-2010, 01:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums