Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello to all.

    I'd like to have one of my operators check off a daily checklist. I do not need to know every day if he has done it. I only need to know if he has NOT done it.

    I would like to run a query every weekday that looks to see if the table has a record for that day. If there are no records... then I would like to have Access send me an email.

    I'd like that query to run automatically every on weekdays.

    Any thoughts on how to go about this? Thanks

    Jason

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Access can only perform actions if it is running.
    Is the database permanently open, or is it opened every weekday?

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans.

    It is not open all the time, but possibly we could keep it open.

    Or run a .bat to open it?

    Thanks.

    Jason

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a .bat file that opens the database and runs some code, and use the Task Scheduler in Windows to run this batch file once a day. But depending on when you'd like it to be run, it might interfere with "normal" usage of the database.

    If the database is opened at least once per workday by the user, you could run code automatically when the database is opened. Whether that is useful depends on you.

    What is the usage pattern of the database, and when would you like the check to occur?

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank Hans.

    The normal usage is daily around lunch and around office closing.. but it is not reliable that it will get used daily...or at any specific time.

    I think using the windows scheduler and the .bat to open the .mdb would work fine as trigger.

    Once we have the trigger triggering right...

    I have no clear idea on how to create the query that checks for null and sends an email if it finds null.

    Thanks

    Jason

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll need several steps.

    1. Write a VBA function.

    Create a new code module, and create a function in it:

    Code:
    Public Function SendMsg()
      If DCount("*", "tblData", "[DateField]=Date()") = 0 Then
        DoCmd.SendObject To:="me@somewhere.com", _
          Subject:="Record missing", _
          MessageText:="No record has been made on " & Date, _
          EditMessage:=False
      End If
      ' Optional: exit Access
      Application.Quit
    End Function
    Here, tblData is the name of the table in which a record should be created, and DateField the field that should contain the date.
    me@somewhere.com is the e-mail address the alert should be sent to.
    If you don't want Access to quit at the end of the code, remove or comment out the line Application.Quit.

    2. Create a macro.

    Create a new macro.
    Add a single RunCode action, and specify SendMsg() as function name (including the parentheses).
    Save the macro as DoMsg.

    3. Create a batch file.

    In Windows, create a new text file with extension .bat.
    Enter a line like this:

    "C:\Program Files\Microsoft Office\Office\MSAccess.exe" "H:\Databases\MyDatabase.mdb" /x DoMsg

    where C:\Program Files\Microsoft Office\Office\MSAccess.exe is the full path of the Access executable, and H:\Databases\MyDatabase.mdb is the full path of the Access database.

    4. Create a scheduled task.

    Select Start | (All) Programs | Accessories | System Tools | Scheduled Tasks to create or edit a scheduled task.

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans.

    Thank you. Awesome, as always.. Gonna take a bit to try this out. Ill post back when I do. Thanks again.

    Jason

Posting Permissions

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