Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2019

    Replace a Calculated Field or Control with a Manual Entry (or Something to the Same Effect)

    I am trying to build a database in which I can track the progress of multiple employees in training. Trainees follow a schedule in which they complete a training manual, rotate through three manufacturing lines, and complete a certification assessment before being placed where needed. The whole process should take about 5 weeks. The challenge is, there is usually a difference between what is planned and what actually happens. I need a database that will help me see at a glance where each trainee currently is in their progress and projected dates for when they should move on to the next phases, and the projected dates must update dynamically as I record trainee progress.

    If we lived in an ideal world in which everything goes according to plan, all our trainees would follow the following timeline:

    1. StartDate (employee begins training manual)
    2. Rotation1 = DateAdd("d",14,[StartDate])
    3. Rotation2 = DateAdd("d",7,[Rotation1])
    4. Rotation3 = DateAdd("d,7,[Rotation2])
    5. Certification = DateAdd("d",3,[Rotation3])
    6. Placement = DateAdd("d",7,[Rotation3])

    This is the planned timeline that would be calculated from the trainee's start date. However, because things do not always go according to plan, there can be a difference between planned and actual dates. I need to be able dynamically update all future dates based upon actual dates. For example, if a trainee spends 3 weeks completing step 1 rather than 2 weeks, I need all future dates to recalculate based upon the actual (not planned) date.

    I've been racking my brain to figure out how to make this work. I've tried calculated fields in a query to calculate future dates, but I cannot replace the calculations with actual dates. I've also tried creating a form in which the default values for the date fields are based upon expressions, which could then be replaced with actual dates, but the default values don't appear in Form View. I've also tried using macros to alter the fields based upon values in calculated controls.

    Do you have any advice that could steer me in the right direction?


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Do calcs in code and save to field when actual date is input/changed. Bind textboxes to field to display value. Don't lock if you want to allow users to change manually.

    I don't use macros, only VBA but macro should be able to do that as well.

    If you prefer to use controls for the calcs, have hidden textboxes for the calcs but visible textboxes bound to field.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2019
    Thanks June7! For each of the relevant controls on the form, I created a series of SetValue macros to update the subsequent date controls. Works beautifully!

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

Similar Threads

  1. Replies: 3
    Last Post: 04-06-2018, 10:40 AM
  2. Checkbox effect entry of Combo Box
    By Ekhart in forum Forms
    Replies: 3
    Last Post: 08-04-2016, 02:42 AM
  3. Replies: 1
    Last Post: 03-27-2015, 09:31 PM
  4. Update Query for manual entry
    By armyofdux in forum Access
    Replies: 6
    Last Post: 11-24-2014, 05:36 PM
  5. Manual Primary Key Entry
    By in forum Access
    Replies: 1
    Last Post: 03-03-2014, 03:09 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 - Senior Forums