1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007. Can it alert me when a date is due?

Discussion in 'Business Applications' started by tmetford, May 4, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. tmetford

    tmetford Thread Starter

    Joined:
    Apr 8, 2008
    Messages:
    56
    Hi,
    My database contains date fields that indicate when a particular action needs to be executed (e.g. send a follow up letter to the sales call).
    Can Access 2007 be configured to alert me when that date arrives?
    Thank you.
    Tim
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes providing the database is open.
     
  3. tmetford

    tmetford Thread Starter

    Joined:
    Apr 8, 2008
    Messages:
    56
    Okay, that's good. Would you tell me how that can be done please?
    I am gaining knowledge about using MS Access 2007 and am reasonably adept re general computing skills.
    Does Access have such a "module" for generating an alert already built in, or will I need to create a macro or use VBA script to do this? (both of these are new things to me)
    Thank you for your help.
    Tim
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Tim, do you want the Alert on the Day in the Field or the Day before?
    First of all you create a query for therecords and in the "alert" field you put in the Criteria row
    Date()
    for the same day, or
    Date() -1
    for the day before.
    You then create an Autoexec macro (that runs when the database is opened) to open a "hidden" form based on the query
    On that form you have some simple VBA in the "On Load" or "On Current" event procedure like
    If Not Isnull(me.recordcount) or me.recordcount > 0 then
    me.Visible = True
    msgbox "the Records shown require action today"
    end if
     
  5. tmetford

    tmetford Thread Starter

    Joined:
    Apr 8, 2008
    Messages:
    56
    Thank you, I've done what you suggested and it's beginning to work which is great.
    I can get a form (or report) to show the requisite fields that tell me what action is now needed when that form is opened (loaded).
    How do I get this form to open by default when the database itself is opened at the beginning of the working day?
    Also, it'd be useful to have the script search for all records that possess a date field that's within a range; say, between 3 days ago and 1 day in the future (a total range of 4days, just so that any recently missed actions are flagged up).
    What would be the expression/s to insert into the script you suggested, to enable this?
    Thanks.
    Tim
    Tim
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all the Form can be opened using the Autoexec macro that I mentioned.
    It can also be opened using the Main Menu>Tools>Startup

    To have a range of dates in the queries Criteria you use
    Between Date() - 3 And Date() + 1
     
  7. mnm123

    mnm123

    Joined:
    May 6, 2009
    Messages:
    44
    Hi, I read the threads above and I would appreciate some more information. I am working with my coworker on access 2007 and we need to set up the database for automatic alerts on specific date fields. We are not as familiare with access as we would like so I was wondering if anyone could in detail explain how we would set the automatic alerts up. I know its a lot to ask but I would appreciate it. Thanks so much.....
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can do that for you tomorrow.
     
  9. mnm123

    mnm123

    Joined:
    May 6, 2009
    Messages:
    44
    Thank you I appreciate it. Will it actually alert the person as soon as the y open the database?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes it will do that, it could even email them if you wanted it to.
     
  11. mnm123

    mnm123

    Joined:
    May 6, 2009
    Messages:
    44
    Wow that would be great. If you could give me instructions how I could get the database set up and have an email sent that would be great.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all follow my instructions in Post #4, to see if you can get part working.
    If you are having real problems you can email or post a zipped copy of the database and I will set it up. But it must be in Access 2003 format and not contain any personal data.
     
  13. mnm123

    mnm123

    Joined:
    May 6, 2009
    Messages:
    44
    We actually did step 4 and tested it out. A message comes up but only if you go into that particular form on the database. We set up code in the vbs box. Had to play around with the code a bit so that it would recognize that the date had expired and it was due for action.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you open the form using an Autoexec macro when the database opens?
     
  15. mnm123

    mnm123

    Joined:
    May 6, 2009
    Messages:
    44
    No we did not. We couldn't figure out how to get that piece set up...
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/824171

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice