Required fields (2003)

Topic

New Reply

Hi Folks,
Yesterday, while teaching an Access 2003 level 1 course, one student asked if she deleted information from a required field in a form and then closed it, if the information would be gone from the table. I told her that she would not be able to close the form or access another record until there was information in the required field. I told her to go ahead and try it. To my astonishment, she did exactly that. When she closed the form and opened the table, the required field was empty. I immediately checked the required property and it was set to yes. I can’t make this happen on my PC. Has anyone else ever experienced this?

Reply | Quote

Replies

If you have a text field with Required set to Yes and with Allow Zero Length also set to Yes, you can clear the field – it will contain an empty string “”. Since this is different from a Null value, you don’t get an error message. If you want to avoid this situation, set the Allow Zero Length property of the field to No.

Reply | Quote

Thanks Hans, didn’t notice that. However, when I change Allow zero length string back to no, I can still open and close both the table and form with no info in the required field (it’s a soc sec number field). This shouldn’t be allowed to happen should it, the field even has an input mask, I would think one or the other properties should take care of this, no?

Reply | Quote

Did you change anything in the record? If you visit a record then leave it without editing it, Access doesn’t perform any checks.

Reply | Quote

Hi Hans,
Wow, kind of a big hole isn’t it.

Reply | Quote

I don’t think it’s a bug. Under normal circumstances it’s only necessary to check for required fields etc. when the record has been modified. Performing these checks whenever the user exits a record, even if it hasn’t been changed, would be superfluous and have a negative effect on performance – scrolling through the records of a form would slow down significantly.

Reply | Quote

When I duplicate the creation of the practice table that I use in class, the allow zero length property is set to yes by default on all my text fields.
The help files say that no is the default setting (it always has been in all the classes that I’ve taught). I’m just wondering what changed and when it changed.
Does anyone know how I can change the default back to no?

Reply | Quote

The default for Allow Zero Length was No in Access 97, and it has been Yes in Access 2000 and all later versions. There is no way to change this default, you have to change it for each new text field.

Allen Browne has written code that will set Allow Zero Length to No for all text fields (except in the Switchboard Items table where this would cause other problems) – see Microsoft Access Flaws – Problem properties (contains other useful info too).

Reply | Quote

Thanks again Hans. You’re right as usual. I was fooled by the Access help files.

Reply | Quote