BeforeUpdate event not triggered in MS Access 2003

[Jurjen]

I have developed an application in MS Access 2002 that administers members and donations for a charity organisation. In one of the forms the so called gift aid declarations are maintained. A gift aid declaration consists basically of a from date and an until date. The from date is mandatory, the until date can be Null, meaning that the declaration is open. There may only be one open declaration per person.
To enforce this restriction i wrote a BeforeUpdate trigger on the until date.
Recently i was unpleasantly surprised to discover that in the office that uses my application some serious problems have arised because some persons have more than one open gift aid declaration! How could that be possible? I tested it really thoroughly. After some investigation i found the reason ...

In MS Access 2003, when creating a new record, the BeforeUpdate event on a field is not triggered when the field keeps its default value, Null in this case. But in MS Access 2002 it is! Have the developers of MS Access ever heard of backward compatibility?
First of all, it defies all logic: BeforeUpdate events are meant for validations (why else would there be a Cancel parameter?). For existing records that means indeed that the event should only be triggered on a change, but for new records it should of course always be triggered.
Secondly this has really created serious problems in the administration.
Last but not least, how should i validate field values of newly created records? I would have to use the OnExit event in this case, which is not very appropriate. It is a rather complex validation, and i want to avoid performing it more than necessary.

Does anyone have the same experience? Does anyone know if this change in MS Access 2003 is documented?

 

[RuralGuy]

I haven't tested it but I am surprised that a default value generates a BeforeUpdate event in a control on a new record in acXP. I would subscribe to the logic that the BeforeUpdate event is to validate *user* input and not programmer input. I would think that default values are already validated and acceptable. I've always used the BeforeUpdate event of the *Form* to do the type of validation you are describing. I understand your pain but I for one side with Microsoft on this issue.

 

[boblarson]

I have to agree with RG, use the FORM's before update event to validate. And, if the default on the control fired the before update of the control in A2K2 then I would consider THAT a bug and it is now fixed.

 

[Jurjen]

I haven't tested it but I am surprised that a default value generates a BeforeUpdate event in a control on a new record in acXP. I would subscribe to the logic that the BeforeUpdate event is to validate *user* input and not programmer input. I would think that default values are already validated and acceptable. I've always used the BeforeUpdate event of the *Form* to do the type of validation you are describing. I understand your pain but I for one side with Microsoft on this issue.

Hi, thanks for your answer
First of all i must admit that i made a mistake in my analysis: the BeforeUpdate event is also NOT triggered in Ms Access 2002 when the field keeps its default value. It is triggered though when you first enter a value, move to another field, then return to the field and enter the default value. In my testing this is what i did, because both the dates are subject to other validations as well (they may not overlap with other gift aid declaration period).

What do you mean with "programmer input", by the way? Data validation is data validation ...

I do not agree that default values should always be acceptable and therefore need no validation. Each gift aid declaration record defines a specific period. These periods may not overlap. So if i make the dates empty or set them to the current date by default, they will always have to be validated.

Concerning the logic: i still think that this event should be triggered, even in case of a default value. A value is a value, default or not. The criterium is: did something change? In case of a new record, everything changed, because everything is new. The principle of validation is that we want to restrict the data in our database. I already explained that default values are not necessarily right: that depends on the situation.

By the way, i discovered that the forms BeforeUpdate event also does not fire, if the values in all fields are left to their default. Actually, no record is created at all!

 

[RuralGuy]

This experience is making you wiser by the minute.