Results 1 to 10 of 10
  1. #1
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Past due notice automatically generating multiple emails.

    Hello all, I'm continuing to work on a task driven/work-flow type database. Basically, I'm tracking parts. There are several time sensitive tasks that needs to happen to each part within a given timeframe. The entire database is being designed to track when tasks are completed sending out notices when the task is not completed by the due date.



    All my tasks and due dates fields are located in one table, Tasktbl. My form's record source is a query from my Tasktbl and Partstbl. I am using the DateAdd function in my query to calculate my due date field. There is another field where the user will place the actual date the task was completed. For example, I have a field called Rec_Datedue (populated through DateAdd), another field is called Rec_Datecomp (which is the manual input field of when that task was actually completed).

    The dates in my query are being calculated as planned but I've run into a dilemma. The value of the DateAdd is not being transferred to the Tasktbl but remains visible on the form only. I think I need the value to be in the table so that I can complete the next, and final, step which is to send out emails to all who missed the due date. All the examples I've seen uses the two dates in a table i.e., received date and due date, to write code for auto emails to be sent. Do I need to have the value of DateAdd to be in the table? Or, will the value of the DateAdd field on the form be sufficient? Thanks

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post a jpg of your tables and relationships.

  3. #3
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    The jpg image comes through extremely small. How can I get it to you?
    Attached Thumbnails Attached Thumbnails Capture.jpg   Tables.jpg  
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm... You tables and relationships do not help since they are not all related. I think it would be helpful if you give us a clear description of your business that uses/will use the database you are developing.

    Can you lead us through a few tasks from the "event or process" that identifies the task and what it is for; what dates are relevant; what is to be accomplished within those dates; who is involved....? Keep it as simple and clear as possible and you should get some focused responses.

    If you can use some example data and show us sort of before and after certain dates, that may be helpful.

    The jpg was fine.

  5. #5
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks. I'll try briefly explain the structure of the database. Our business ships and receives several machine parts. Parts are shipped out as well as received from specific docks. At least once a month we have dock changes where parts are reassigned a delivery dock. This causes confusion because at least 5 departments are involved and they often miscommunicate "new" dock information. As a results parts end up going to an incorrect dock for shipping only to discover that the shipment dock has changed, bottom line its costing our business money every time shipment is delayed. This is where database steps in.

    My goal is to develop a database that will, 1). serve as a central point for communicating dock code changes, and 2). communicate when task deadlines are missed. 35 days before a dock change I will receive an excel spreadsheet of parts information such as model #, batch#, Lot #, Old dock#, new dock#, etc... I have placed these items in one table call Partstbl. All parts are related to tasks. There are 12-15 different tasks each with a due date (all tasks must be completed within 35 business days). For example one of the task items for one department would be "Verify updates", which must be on day 32, therefore I have a field in my task table called "Verify_Updates" which is a date field. Each task is a field in my task table and all 12-15 tasks are completed by 1 of 5 departments; therefore, I have a "Departmentstbl" which contains the names of each department. The Departmentstbl is related to the Tasktbl because one department can perform 1 or more tasks. Five departments will utilize the database to view what task(s) they are responsible for completing and when it should be completed. My problem is with the due dates which are calculated in a query using DatesAdd. I would like to be able to select one department via combo box and have all relevant tasks show along with due dates.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I see the following potential tables-based on your latest post/description

    Department
    Task
    ResponsibleDepartment
    Part
    Dock (whatever that is exactly)
    DockChange
    Deadline is also a big --especially missed Deadline

    Thoughts?

  7. #7
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    These are my tables:
    1. Headertbl (Event procedure to import the Excel spreadsheet and brings parts info into the database).
    2. Calendartbl (periods and dates of when dock codes change).
    3. Partstbl (All parts).
    4. Tasktbl (all tasks).
    5. Departmenttbl (list of departments).
    6. Usertbl (list of all users and their email address).

    I think these are all the tables I'll need. You are correct, missed deadlines is a huge factor, it's the main reason for the database. I have a field in the Calendar table called, "DCC_Date" which is the dock code change date. I would like for the results of the DateAdd function to go into that field. However, the DateAdd function is only working in the query displayed on a form.

  8. #8
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I resolved the problem by creating an Update query to get the DateAdd value on a form to a table. Now I can write code for missed due dates.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks, you guys have really been a big help.

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

Similar Threads

  1. Replies: 6
    Last Post: 04-26-2014, 08:54 PM
  2. Generating and sending emails through Access
    By crowegreg in forum Programming
    Replies: 9
    Last Post: 06-10-2013, 08:35 AM
  3. Send Emails Automatically
    By cbrsix in forum Programming
    Replies: 10
    Last Post: 10-19-2012, 10:52 AM
  4. Generating sequential numbers automatically
    By Malcolm41 in forum Access
    Replies: 2
    Last Post: 07-15-2012, 08:20 PM
  5. Generating emails with email-address in body
    By techexpressinc in forum Programming
    Replies: 1
    Last Post: 08-17-2011, 01:48 PM

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