Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99

    Hi @orange,

    There is a field in the table that holds the exact amount of days between calibrations for each tool. That field was created with the table back in 2000 with the original database. What I originally wanted to do was have the DueDate field automatically update when a tool is calibrated. This would be done by adding the number of days between calibrations to the old due date. The expression that June7 sent me does just that, but it only appears on the form; it does not store the new date in the table. I found a way around that, although I don't know how great of a solution it is (see my last post). That is the gist of what is going on

  2. #17
    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 don't know your table design, but it seems if you had a field in your table eg LastCalibratedDate, you could update that field when the calibration is completed. Then, you could query your "machines" tables and identify those with LastCalibratedDate that is equal or beyond the re-calibration frequency limit.

    Consider machineA that is supposed to be recalibrated every 90 days. You search for machines whose lastCalibratedDate is >=90 days from today. Or you could use a number like 80 days and identify machines to be re calibrated in the next 10 days ...
    Could you post a graphic of your tables and relationships as jpg/png?

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Well, buttons don't have a ControlSource.

    Again, the DueDate can be calculated when needed in textbox or in query. This calculation can be displayed on form or report. There is no need to save it. So my advice is still to eliminate Due_Date field in table and calculate this when needed.

    Can even calculate in table with a Calculated field type. Day is default unit for arithmetic with dates. Simply [Last_Cal_Date]+[Cal_Int] will provide same result as using DateAdd(). Bind textbox to this field. Many don't like Calculated type and I have never used but all my testing indicates calcs are correct.

    Saving the calculated result requires code and you have already experienced issues with that. Your checkbox fix goes back to relying on user. Instead, use form BeforeUpdate event to set field with the calculated value. Name due date textbox different from the field, like tbxDue. Then:

    Me!Due_Date = Me.tbxDue

    There is even a command available in macros to do this - SetValue. However, I never use macros, only VBA.

    Last edited by June7; 08-22-2019 at 12:56 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #19
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    @June7, you say it's unnecessary to save the DueDate automatically to the table because it can be calculated when necessary, but the premise of your solution to my initial problem was relying on the user as little as possible; why would I want to have the user calculate the due date every time they calibrate a tool if the main concern is decreasing user reliability?
    Using a calculated field type seems to be working perfectly! Thank you for introducing me to that feature, I did not know it existed. I appreciate all of your help!

  5. #20
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    @Orange, I seem to have found a better solution to my problem with June7's suggestion of using a calculated field type for the DueDate field. If I have any other concerns I will be sure to reach out and let you know. Thank you for your help!

  6. #21
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    ahuffman24,

    I have a calibration database that I designed and it works great. I have several tables and in the tblCalibrations I have fields for CalDate and another for NextCalDate and Term among others. NextCalDate is determined by a calculation of the CalDate + Term which is the time interval in months the tool requires Calibration again.

    Dave

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Users don't do calculation - I never said they would. You program it into queries or textboxes or table Calculated field type - all of which I already described.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Update Statement to update Date field
    By zephyr223 in forum Programming
    Replies: 6
    Last Post: 10-27-2016, 10:45 AM
  2. Replies: 3
    Last Post: 12-13-2012, 01:51 PM
  3. query to update a date based on another date
    By tkendell in forum Access
    Replies: 3
    Last Post: 06-29-2011, 09:32 PM
  4. Update Record button
    By Steven.Allman in forum Forms
    Replies: 1
    Last Post: 02-16-2011, 02:15 PM
  5. Update button
    By collen in forum Access
    Replies: 3
    Last Post: 07-14-2010, 10:03 AM

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