Trouble adding code on date fields

[Pat Hartman]

The Form's BeforeUpdate event ALWAYS fires when a record is dirtied. It is the last event to fire before a record gets saved. NOTHING alters that. The control's BeforeUpdate event might operate differently but as I tried to explain, it is not the best choice regardless.

Edit.
I just tried it and both the Before and After updates of the control fire when using the Access date picker. I can't say what happens with an active X control.

The FORM's BeforeUpdate event is the most important, most powerful tool in the entire panoply of form/control events. If you don't master that event, you will spend a ton of time writing code to stop Access from doing what it does naturally (save records) and probably fail anyway.

 

[isladogs]

@Pat Hartman
I intervened in this thread as I was surprised when Paul seemed to suggest that update events didn't occur when a date was selected with a date picker. My tests indicated that the form before update event didn't occur.

However, I apologise, as my testing was done incorrectly. I called this wrongly.

In fact, after redoing this, I can confirm your statement that the Form_BeforeUpdate does indeed occur.

For those that would prefer to see validation done at the control level, I can also confirm that the control BeforeUpdate event occurs using the built in date picker for both bound & unbound controls.

I didn't test with ActiveX controls

 

[Pat Hartman]

Performing validation in a control's BeforeUpdate event is possible but as I mentioned several times, If you need to ensure that a value is present OR you need to compare the values of two fields such as from date and thru date, then using the control events will be awkward at best and you will STILL require validation in the form's BeforeUpdate event to be thorough.

If the focus never enters a control, NO control level events would ever run. That means that if you want to prevent null from being saved, you need code in the form's BeforeUpdate event.

If you want to compare the date values of from and thru dates and you do it in control events, one of the controls is ALWAYS going to be null at some point and that causes a validation problem. The problem is not insurmountable. It just requires more code and code in multiple events whereas, if you just put the code into the form's BeforeUpdate event, you have three tests. Is dt1 a valid date, is dt2 a valid date, is dt1 < dt2. Clean, all together and three simple to understand error messages.

I use validation in a control's BeforeUpdate event in one and only one case. That case is if I need to check for duplicates and prevent the user from wasting his time with the rest of the data entry if he has entered a duplicate value. For example, if you need a SSN (Social Security Number) to enter an employee record, that SSN MUST be unique. If the entered SSN is not unique, then the employee already exists or there is some other problem and the employee record should not be entered. In the US, this simple test would go a long way toward not employing people who are not authorized to work in the US. All the rest of my validation goes into the Form's BeforeUpdate event.

 

[pbaldy]

Are you saying the update events fire as soon as you select a date, or once focus moves? In my experience they don't fire when a date is picked, they fire when you move focus. The OP (plus a client of mine) wanted to run a validation as soon as a selection was made.

 

[isladogs]

The control BeforeUpdate event is indeed triggered when you move focus.
The form BeforeUpdate event is triggered when you go to move to another record or close the form.

It was the lack of immediacy in both cases that led to me calling this wrongly in post #20

 

[Pat Hartman]

The control's BeforeUpdate event works this way because there is no way for Access to know when you are done doing data entry until you give it some indication by tabbing/clicking to a new control. In some situations you could use the Change event to recognize when the entry is complete and "auto" tab but this will be pretty complicated since people can enter a date as 1/1 and if they tab, Access will fill it with 2020 for the year but you couldn't use the change event to force the tab because you have no way of knowing that the user actually wants the year to be 2020..

 

[pbaldy]

I mentioned because of this:

I intervened in this thread as I was surprised when Paul seemed to suggest that update events didn't occur when a date was selected with a date picker.

You both seem to confirm that the act of selecting a date doesn't fire the events, which is all I was saying. I don't normally use the date picker, my users prefer an input mask (head down data entry people). For a client and the OP I found the change event could be used to trigger action after selection but before focus moved triggering the update events.

 

[Pat Hartman]

No,
we both confirm that using the Access date picker DOES fire both control level and form level BeforeUpdate events.

I never use an input mask since it always requires all the characters to be typed and I prefer to allow Access to use defaults. That doesn't interfere with heads down data entry. If I type 11/1 tab, Access fills in 20 as the year. With an input mask, I have to type 11/01/2020.

 

[pbaldy]

we both confirm that using the Access date picker DOES fire both control level and form level BeforeUpdate events.

Before moving focus? Nobody is arguing that they don't fire once focus moves. I've just tested again (A2010) and the act of selecting a date does not fire the update events. They fire when focus moves off the textbox. I'm just clicking on the date picker and selecting a date. Focus remains in the textbox at that point, so it's actually not surprising that the events don't fire at that point.

As to input masks, I'll simply say that other people have different preferences than you do.

 

[Pat Hartman]

Using an input mask is a preference so whatever your client prefers is "correct".

I don't understand why you think the BeforeUpdate event of the control should run while the control still has the focus. How does Access know you are done? I touched on the problem briefly in #25

The click event fires when you click into the control. It doesn't fire again when you click the date picker or when you pick the date.

 

[pbaldy]

I think I'm going to punt. I never said I thought the update events should run while the control had focus. I even said:

it's actually not surprising that the events don't fire at that point.

You say (and you say Colin agrees) that the control's update events run when selecting a date. I say they don't. Again, I specifically mean when you click on a date in the date picker but BEFORE moving focus off the textbox.

I don't recall mentioning the click event. I can't remember the last time I used it.

 

[Uncle Gizmo]

I think Paul has a valid point.

The issue for me was the user experience when selecting a date with the MS Access built-in date picker.

There are two scenarios where the MS Access date-picker doesn't give the user A good experience.

1) Age Calc
If the user entered someone's birth date adjacent to a text box displaying the person's age, then nothing happens when you enter the date with the Microsoft Access date picker. You have to do something else to trigger the age calculation and display it in the Age Text box.

2) Start Date / End Date
If the user enters a start date and end date, then again it would be nice if the actual entry of the dates with the Microsoft Access date-picker would trigger the calculation to tell you immediately if the date you have entered are in the correct order, in that the start date is before the end date and the end date is after the start date. Again this doesn't happen instantaneously when you select the date, you have to move away or do something to trigger this calculation. You don't really want the user to move away from the control and then be notified that there's a problem, it would be much better if they were notified immediately they selected the date. See YouTube below for more info....

11) - Class Module to Mimic AfterUpdate Event - Nifty Access​

The Nifty DatePicker along with the Call Called Class Module solve this problem by allowing you to run the calculations immediately the date is selected with the Nifty Date Picker...

Nifty Date Picker​

Nifty Date Picker - Nifty Access

Nifty Date Picker -Updated This page is about the new "Nifty Date Picker" which supersedes the old original "Nifty Date Picker". If you're looking for the original "Nifty Date Picker", just click THIS LINK and you will be taken to the webpage. The new "Nifty Date Picker" has some Nifty New...

www.niftyaccess.com