Update Due Date Button

[ahuffman24]

Hi,

I recently have been tasked with adding a form to a MS database that allows users to enter information for calibrating tools. The tools are calibrated every certain amount of days (the number of days depends on the tool), and when they calibrate a tool, they need to update the tool's due date. Currently, there is an "Update Due Date" button on another form in the database (I did not create this form nor did I code the button - these were created in 2002). When clicked, it asks if I want to update the records, but when I click "yes," it does not update the due date. I am not great with VBA, but to me, it looks as though the button is opening query called "UpdateDueDate" (see code below), and in the query, it's updating the due date field by adding the number of days between calibrations (Cal_Int) to the old due date (Last_Cal_Date). Shown below is the code on the button and the actions performed in the query:

Code:
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click
Dim stDocName As String
stDocName = "UpdateDueDate"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command63_Click:
Exit Sub
Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub








I have a feeling that the issue is because the code and query were created in 2002, and I am now trying to run it with MS Access 2016.

[June7]

Saving calculated data is often unnecessary and can even be dangerous (saved calc can get 'out of sync' with raw data).

The revised due date can be calculated when needed, no need to actually repeatedly save this moving target into record.

[ahuffman24]

@June7, what should I do to fix that?

[ahuffman24]

@June7, are you saying that the user should calculate it by hand instead? I really would like them to press a button and have it automatically update

[June7]

Absolutely not!! Why even rely on user to remember to push a button? More than one way to accomplish automated calc without having to save to record.

An aggregate (GROUP BY) query can pull the latest date for each item then join that query to the intervals table to do the DateAdd() calc.

Domain aggregate functions (DMax() and/or DLookup()) in textbox expression can pull the latest date for item and/or its associated interval and then use that in DateAdd() calc.

A custom VBA function using any of the above can be executed to return the calculated due date when called.

I don't know your db well enough to determine why your code fails. Perhaps first need to commit new record to table before running the SQL action.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ahuffman24]

@June7, thank you for the reply. An automatic update without even clicking a button would be even better, so if that is possible I will absolutely do that. I have attached a copy of the database. On the form that appears first, feel free to click the "Select" button next to any of the ID#'s (the ID #'s are specific for individual tools). When you select a tool, a form will appear with various fields into which the user enters information relevant to the calibration. It is on this form that you will see the "Update Due Date" button.

Another thing that's maybe worth mentioning is that there is a form called "Gage," and it is on this form that the original "Update Due Date" button was created back in 2002. If you open the Gage form, you can edit the dates for the tools, just like you can on the forms that open initially. If you update the date on the Gage form and click the "Update Due Date" button, the due date will not update unless you navigate to a different record and then go back to the original record you changed. Only then will the due date update. I just thought I would mention that because that puzzles me as well.

Gage_Updated2 Copy.zip

Thank you so much for your help!

[June7]

Advise not to set alias field name (Caption) in table. I would not use spaces nor punctuation/special characters even in alias names. I put whatever I want in labels on forms and reports.

Why not use a combobox for Gage_ID search?

Suggest you give more meaningful names to buttons than the defaults of Command14 and Command5, etc.

AfterUpdate event of SearchText is interfering with clicking Search button. Don't think it is needed.

Do you want history of calibrations or you only keep the most recent calibration?

So DueDate textbox on CalibrationRecord could have expression: =DateAdd("d", [Cal_Int], [Last_Cal_Date])

Or even do that calc in query.

No need for VBA or UPDATE action saving value to table. Solves a lot of issues you are experiencing.

Gage form fails because record edit must first be committed to table before running the UPDATE action. Record is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save.

[ahuffman24]

@June7, thank you so much for looking at my database and giving me tips! I will definitely look into all of these issues. I have a question about the DueDate textbox on the Calibration_Record form. Where do I put the expression if I am not putting it in a query? Would it be an event?

[ahuffman24]

Also, we do not need to keep a history of calibrations - we only keep the most recent

[June7]

No, the expression would go in textbox ControlSource property, just as I show in prior post.

No VBA needed.

So be sure to remove code that runs UPDATE query.

[ahuffman24]

@June7, thank you! Putting the expression in the control source of the DueDate text box did work. However, when the due date changes, it does not update the due date field in the table. Do you happen to know why it wouldn't update in the table?

[ahuffman24]

@June7, I figured out why. It's because the expression is the control source, not the DueDate field itself. Is there a way I can set the control source as the DueDate field and also keep the expression in the control source?

[June7]

No.

My advice is to NOT save into table. Calculate when needed.

Would need code to save calculated result. Which brings you back to dealing with issues originally encountered.

[ahuffman24]

We need to save it into the table because we need to track when the next calibration is due. Right now, the only solution i've come up with is to have a separate text box (not associated with a field in the table) and put the expression in its control source. I then have a check box programmed to make the actual DueDate field equal to the text box when the check box is clicked. I would have used a button, but Access will not let me set a button's control source as an expression. This is kind of an annoying extra step but it's the only thing I can come up with

[orange]

ahuffman24,

Just saw this thread. My first thought is you have determined HOW you want to do something, but you haven't identified WHAT exactly that something is. If machines/items are calibrated from time to time, what is that period of time, or what condition determines that Machine A needs/should be re-calibrated?
Every 45 days? Every 6 months? This needs to be understood before deciding on a calculated field or additional tables etc.
Get your requirements defined and vetted before working with physical database and/or defining new constructs/structures.

You might want to do some research on database and Access concepts --normalization, tables/queries, data model, testing....lots of good youtube videos out there.

Good luck with your project.