Help with Blank, Null, and Zero length entries in forms

[DominicGreco via AccessMonster.com]

I want to make it so that the user is required to fill in each text/combo/or
list box on the form before moving to the next.

I figure this is pretty easy. In the Table properties, I set the "Required"
property to YES and the Allow Zero length to NO. In the control's property
box I set the Validation Rule to "<> = Null" or IS NOT NULL.

So I can't have a zero length entry, and I can't have a Null one.

HOWEVER, when I start the form the first Combobox I must fill is the Employee
Name. If I "TAB" over it, and input info into the next combobox, it WORKS! I
can even save the record!

Now I know for a fact that the "<> = Null" works because if I try to put a
"SPACE" in (using the space bar) and then tab to the next combo box, I get a
msgbox with my Validation text in it.

What am I missing here?

 

[Ken Snell \(MVP\)]

Validation rules, etc. are triggered by an entry or deletion in the control,
not by just tabbing past the control. You'd need to use code for the Exit
event to not allow the user to leave the control without typing in a value
or selecting a value (NOTE: Users can get very impatient with a form that
enforces this on the Exit event -- and users are then known to crash ACCESS
in order to get out of the loop -- note also that this code can prevent you
from cancelling the form without entering a value) --

Or, even better, to use the form's BeforeUpdate event to test that each
control has a value in it.

 

[John W. Vinson]

I want to make it so that the user is required to fill in each text/combo/or
list box on the form before moving to the next.

Well... that really goes against the whole Windows philosophy of using
interactive forms.

Is it in fact really critical that the controls must be entered *in a specific
order*? Or don't you actually just need them to all be entered before the
record is saved?

You can do the latter easily by checking for non-Null values in all controls
in the Form's BeforeUpdate event, and setting Cancel to true with a MsgBox
warning if they've left something blank.

 

[DominicGreco via AccessMonster.com]

Well... that really goes against the whole Windows philosophy of using
interactive forms.

I realize that. But this is a timesheet program and I need to structure it
along predictated lines (not mine)

You can do the latter easily by checking for non-Null values in all controls
in the Form's BeforeUpdate event, and setting Cancel to true with a MsgBox
warning if they've left something blank.

This was pretty much the way I have been proceeding. I was using the Form's
BeforeUpdate Event to house a small VB App that would check for Null or blank
entries. However, this is a blanket program which checks ALL the controls.
Even the ones I don't want checked. And it causes an error. Therefore I was
going to try a more "focused" approach by structing an IF statement using the
actual name of the control(s) I need to check.

Your thoughts?

 

[John W. Vinson]

This was pretty much the way I have been proceeding. I was using the Form's
BeforeUpdate Event to house a small VB App that would check for Null or blank
entries. However, this is a blanket program which checks ALL the controls.
Even the ones I don't want checked. And it causes an error. Therefore I was
going to try a more "focused" approach by structing an IF statement using the
actual name of the control(s) I need to check.

One way to do this is to use the controls' Tag property. For instance you
could set the Tag property to be "NonNull" (or to contain that string) for
each control which is required; you can then loop through the form's Controls
collection and only check those controls with "NonNull" in the Tag property
(before checking the control's Value, since many kinds of controls don't have
a Value).

 

[David W. Fenton]

I realize that. But this is a timesheet program and I need to
structure it along predictated lines (not mine)

Then use a wizard type interface. This can be done easily with a tab
control with the tabs hidden. You'd enable the NEXT>> button only
when the fields on that tab page have been properly filled out.