Results 1 to 12 of 12
  1. #1
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11

    Query on start up / load

    Hi,

    How can i get access to run a query on start up and if the query meets criteria get it to send an email, send-object, if it doesn't then just get it to open as normal.

    Ta



    Kev

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    What have you tried so far, and what were the issues/problems?

  3. #3
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11
    Quote Originally Posted by orange View Post
    What have you tried so far, and what were the issues/problems?
    Hi ,

    thanks for the reply. I have a calibration database and have three queries , the first is for overdue calibration items, the second is for calibration items that require calibration this week and the third is a query that will give advance warning 1-3 days before an item is due.

    What I would like to due on load is send an email with report attached for both late & due items if identified by the query. I have tested the queries with dummy data and they work well however I can't figure out how to send the email if the query finds the data.

    Any suggestions that you can give would be much appreciated.

    Thanks.


    KevWB

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    You can use a DCount() to test whether the query returns any records. If it does, you can use SendObject to send out a report based on that query.

    If DCount(...) > 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11
    Good Morning Paul and Thanks,

    Not come across this before but will give it a go and let you know, thanks for the response.

    Ta

    Kev

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    No problem Kev; post back if you get stuck. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11
    Hi,

    I have tried dcount() but I am a little stuck. A macro opens the query[late calibration] first, I then have the following if statement;

    If DCount([next due],[late calibration])>=1

    Then sendobject

    [next due] is a field within the query

    I have also tried

    If DCount([late calibration]![next due],[late calibration])>=1

    Every time I run the macro I get the following error

    Access cannot find the name"next due" you entered in the expression.

    Any advice would be much appreciated, have also tried using count() but get the same result.

    Thanks in advance

    Kevin

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    You don't need to open the query, Access will do that internally. The DCount() uses string arguments (and since you just want a count, * is more efficient than specifying a field):

    If DCount("*","[late calibration]")>=1 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11
    Hi Paul,

    it's me again, tried what you suggested but I am now getting;

    Access cannot parse the expression If DCount("*","[late calibration]")>=1, have also tried without [] but does not make any difference, any suggestions, thanks for your help and patience

    Ta

    Kev

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Is "late calibration" the name of the query? Does it run okay if you open it directly? Did you add the "Then" after that part? Can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    KEVWB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    11
    Quote Originally Posted by pbaldy View Post
    No problem Kev; post back if you get stuck. Welcome to the site by the way!
    Hi Paul,

    Tried again, it worked just as you advised, I owe you one,much obliged

    Ta

    Kev

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Happy to help Kev.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Load SQL results into an array
    By Tyork in forum Programming
    Replies: 8
    Last Post: 01-24-2011, 01:58 PM
  2. Can't Load a picture from the table
    By dada in forum Reports
    Replies: 1
    Last Post: 09-18-2010, 10:17 AM
  3. Instant load of a form
    By carstenhdk in forum Forms
    Replies: 2
    Last Post: 05-06-2010, 12:27 AM
  4. On Load, form not visible
    By Bruce in forum Forms
    Replies: 15
    Last Post: 02-24-2010, 04:06 PM
  5. Program to load data
    By fsmikwen in forum Access
    Replies: 2
    Last Post: 12-10-2009, 08:36 AM

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