Close form without saving record? (1 Viewer)

[directormac]

There is only one case where I use the BeforeUpdate event of a control and that is if I want to stop the user early in a long data entry process. For example, if SSN is a required field and it must be unique, then I add a BeforeUpdate procedure to ensure the SSN is unique so I can give the user a heads up that he isn't going to be able to actually save the record if the SSN isn't present or isn't unique.

@Pat Hartman : question on best practice, only tangentially related. In the above example, if ALL you needed to do was make sure the value for a given field is unique, is there a benefit to using a custom code in the BeforeUpdate (or even the form level ValidationRule property) vs. setting that field to Indexed / NoDuplicates at the table level? I recall that indexing comes with a performance cost... but if you don't need the scale of 100k or even 10k records, is that overhead acceptable? (still working SLOWLY on the "animals" project, the EAV tips were VERY helpful...)

 

[The_Doc_Man]

In case Pat doesn't catch this one right away...

This is a quite good question because it emphasizes that there are various ways to approach a problem in Access.

Here is the (perhaps undesirable) side-effect of having non-unique data entry attempted to a field that should be unique - error trapping. The result of the attempt to enter a duplicate value to a table would be a Constraint Error/Violation, which is a run-time error. If you didn't build a good error handling routine, you would crash the form. In such a case, you could have a complex error cleanup. And you don't DARE allow the error to not be handled because unhandled errors lead you to the Access "Last Chance" handler that gives you the dreaded "DEBUG/RESET" dialog box. And a RESET stops everything cold (and broken).

IF you are comfortable with error handling, then indexing the field with a uniqueness constraint is a quite decent way to handle the problem. If you are not comfortable with error handling, you will think "Why, oh WHY didn't I realize that the can I just opened was labeled 'Worms'?" You just have to understand "the cost of doing business."

It is all a matter of what makes you comfortable. What do you THINK you can do? Answer THAT question and you will know how to handle the initial question happily.

 

[directormac]

In case Pat doesn't catch this one right away...

This is a quite good question because it emphasizes that there are various ways to approach a problem in Access.

Here is the (perhaps undesirable) side-effect of having non-unique data entry attempted to a field that should be unique - error trapping. The result of the attempt to enter a duplicate value to a table would be a Constraint Error/Violation, which is a run-time error. If you didn't build a good error handling routine, you would crash the form. In such a case, you could have a complex error cleanup. And you don't DARE allow the error to not be handled because unhandled errors lead you to the Access "Last Chance" handler that gives you the dreaded "DEBUG/RESET" dialog box. And a RESET stops everything cold (and broken).

IF you are comfortable with error handling, then indexing the field with a uniqueness constraint is a quite decent way to handle the problem. If you are not comfortable with error handling, you will think "Why, oh WHY didn't I realize that the can I just opened was labeled 'Worms'?" You just have to understand "the cost of doing business."

It is all a matter of what makes you comfortable. What do you THINK you can do? Answer THAT question and you will know how to handle the initial question happily.

Thanks Doc Man! GREAT breakdown.

 

[Pat Hartman]

Regardless of whether or not I elect to validate ahead of time, I ALWAYS use RI whenever it applies.

I picked SSN specifically because a record requires a SSN to be entered into payroll. You don't want the user to get all the way through the data entry and then not let him save. So for this instance, I validate immediately. People are always unhappy if you let them enter a lot of data but then won't save it.

If your business rules allow the record to be saved with the field in question empty, then you can allow Access to give you the error message or trap the error yourself. I prefer to not allow users to see error messages from Access if I can help it.

The three options
1. use a domain function in the control's BeforeUpdate event or the Form's BeforeUpdate event to determine if a value is unique.
2. Do nothing and allow Access to present an error
3. Trap data errors in the Error event and display a custom message.