Results 1 to 11 of 11
  1. #1
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99

    DLookup function with date add

    Hi,

    I posted a question earlier this week about a database tracking tool calibrations. That database did not keep track of previous calibrations, so I have been asked to revise the database to track previous calibrations. I have two tables, one holding each individual tool and the information about each tool (Tools table), and another with the fields that will be updated when each tool is calibrated (ToolCalibrationInformation table). These tables are linked by a one to many relationship, with the parent table as Tools and the ToolCalibrationInformation table as the child table. Two of the fields in the ToolCalibrationInformation table are Last_Cal_Date and Due_Date, and there is a field in the Tools table called Cal_Int, which is the interval of time between calibrations. I have a form based on the Tools table with a subform based on the ToolCalibrationInformation for the calibrators to use. I need the Due_Date field in the subform to automatically update when the Last_Cal_Date is changed by adding the Cal_Int field value from the Tools table to the new date value in the Last_Cal_Date field. I tried making the Due_Date field a calculated field and putting the expression [Last_Cal_Date]+[Cal_Int] on it, but this won't work because Cal_Int is not in the same table as Due_Date. I also looked into using the DLookup function to look up the Cal_Int value, but it seems that I can't use that function in an expression on a calculated field. I also cannot use an update query because I need to keep track of every calibration, including each Last_Cal_Date and Due_Date. Any suggestions? I have included pictures of my form and tables below for reference.

    Tools Table

    Click image for larger version. 

Name:	Tools.jpg 
Views:	19 
Size:	184.7 KB 
ID:	39547

    ToolCalibrationInformation Table



    Click image for larger version. 

Name:	ToolCalibrationInfo.jpg 
Views:	19 
Size:	239.6 KB 
ID:	39548

    Form

    Click image for larger version. 

Name:	ToolsFOrm.jpg 
Views:	19 
Size:	62.3 KB 
ID:	39549

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you are going about this the wrong way. In 99.598% of cases, it is ill advised to store calculations. That's what forms and reports are for. These should be adding the interval to the last calibration date and displaying the due date. All you need for this part is the tool info and a table of calibration records. You can get next date by adding the interval to the Max of the last date for a tool and displaying the calc on a form or report. By storing the date, you are just making things more difficult and unreliable.
    another with the fields that will be updated when each tool is calibrated
    I could take this to mean that you over-write (update) the last date rather than append a new calibration record for a tool, but the rest of your description suggests that is not the case. Updating records means you change what is there. Appending is what you do when you add a new records. For most db situations (and this is one of them) you should avoid deleting records; rather, you flag them with some sort of "archiving" field - often a date.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    I apologize if I was unclear, I am not updating records; I am appending them each time a tool is calibrated. The subfrom allows users to do this because it will keep track of each calibration (including the date of calibration and the next due date), and it will allow them to add new calibrations. We need to keep track of each individual calibration because if we are to ever have a customer call us and ask us how/when a part was calibrated, we would need to be able to give them that information.

    A few other people have already told me what you are saying about not keeping a record of the due date in a table, but I need to keep a record of it because of our company policy. Do you have any other suggestions?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As Micron said you shouldn't save calculation as it is easy enough to get your Due Date on demand. That being settled to get what you want you simply need to add some code to the AfterUpdate event of the Last_Cal_Date control on the subform:

    Me.txtDue_Date=Me.txtLast_Cal_Date + Me.Parent.Form.COntrols("[txtCalibrationInteval]") (replace with the names of your controls)

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Do you have any other suggestions?
    Yes, but you won't like it - change the policy. There is no way anyone should be poking around in tables, which is the only place you can really make use of a stored calculation that would support storing it in the first place. In other words, if no one is poking around in tables, then why on earth would anyone notice the difference between a stored date on a form or report (which is subject to being incorrectly calculated) versus one that is calculated on the fly and is not stored? However, if anyone is poking around in tables, it's just another faux pas - the first being to store the date. If the person who set this policy is an accomplished database designer, I would be surprised. If that person is really interested in accuracy and reliability, they should abandon that policy. Research the internet and find me one db designer worth their salt who says it is OK to store this type of calculation and I'll be amazed. Best thing you can do IMHO is educate someone on the dangers of a failed update to a calculation (which can happen for more than one reason), which might even have a cascading effect. That is the most prevalent reason for not doing it in the first place. In respect of this basic db design commandment, I wouldn't even suggest an approach if I had one.

  6. #6
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Thank you Micron! Could you please clarify what it is that I need to change? This is what I thought you mean by replacing with the names of the controls:
    Me.txtDue_Date=Me.txtLast_Cal_Date + Me.
    Tools
    .Form.COntrols("[
    txtCalInt
    ]")

    but when I add a new date in the Last_Cal_Date field of the subform, I am getting this error:
    Click image for larger version. 

Name:	dateerror.jpg 
Views:	16 
Size:	117.4 KB 
ID:	39552

  7. #7
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    Im sorry, please ignore my last response. I meant to reply to @Gicu, and when I posted the response it screwed up the vba expression. Here is what I was trying to say:

    Thank you @Gicu! Could you please clarify what it is that I need to change? This is what I thought you mean by replacing with the names of the controls:
    Me.txtDue_Date=Me.txtLast_Cal_Date + Me.
    Tools
    .Form.COntrols("[
    txtCalInt
    ]")


    but when I add a new date in the Last_Cal_Date field of the subform, I am getting this error:

  8. #8
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    It still is not allowing me to type the expression on the same line for some reason, I apologize for the confusion. i tried to highlight the fields I changed in red.

  9. #9
    ahuffman24 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    99
    @Micron, this company has been keeping records of calibrations on paper for 20+ years. I was given the task of transferring their paper process to a database to eliminate the need to store hundreds of paper records. On paper, every "Due Date" is obviously kept track of because it is literally written on paper; with this database, I want to store everything exactly they way they have been but also make it more efficient for them to store and find records.

    I am no expert with MS Access either, and I believe that everything you're saying is true. But, the company policy was not written with the understanding that a database would be used. It was written according to what they deem is necessary regarding potential issues/defects with the parts that we make and sell.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I hear you. FWIW, I was an ISO 9000, ISO 14001 coordinator for a time, ISO 14001 compliance auditor and had to know a bit about TS 16949, all of which encompassed some degree of calibration and inspection requirements, so I know a bit about what you're doing. If my boss said "you must store the calculation" I would proceed to tell him/her why that should not be done. If they still said, "you must store the calculation" I'd say "you're the boss" and do it. However, as an auditor you can bet your bippy that I would be looking for a 'next inspection date' that was out of sync with the last + the frequency.

    By integrating knowledge of basic db principles with quality systems requirements I feel comfortable in my position.
    You probably need a calculated field in a query that adds the 2 values and performs the update or append as needed. I see no reason why a DLookup can't work if it's looking up the frequency of the calibration as long as you're providing the tool record PK value as criteria to the lookup.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to leave the Me.Parent in and only replace the name of the three controls (text boxes) to match yours, two from the subform and one from the main (=PARENT) form:
    Me.txtDue_Date=Me.txtLast_Cal_Date + Me.Parent.Form.Controls("[txtCalibrationInteval]")

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. DLookUp Function
    By IanDJDavies in forum Programming
    Replies: 4
    Last Post: 04-01-2018, 06:15 PM
  2. Dlookup Function
    By balajigade in forum Access
    Replies: 2
    Last Post: 09-10-2015, 01:55 AM
  3. DLookup Function
    By Alex Motilal in forum Programming
    Replies: 8
    Last Post: 08-14-2014, 01:15 PM
  4. Dlookup Function
    By MarkHenderson in forum Queries
    Replies: 1
    Last Post: 11-19-2012, 03:00 PM
  5. DLOOKUP function
    By tariq1 in forum Programming
    Replies: 5
    Last Post: 07-17-2012, 04:22 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