Validation for empty fields not working for 3 of 7 controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.

Here's the code: (ProgramArea, Category, and Description are the ones not
working)
If IsNull(Me!cboInitiator) Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
ElseIf IsNull(Me!cboState) Then
MsgBox "Please select a location."
Me![cboState].SetFocus
ElseIf IsNull(Me!cboProgramArea) Then
MsgBox "Please select a program area."
Me![cboProgramArea].SetFocus
ElseIf IsNull(Me!cboCategory) Then
MsgBox "Please select a category."
Me![cboCategory].SetFocus
ElseIf IsNull(Me!cboDescription) Then
MsgBox "Please select a description."
Me![cboDescription].SetFocus
ElseIf IsNull(Me!cboGroup) Then
MsgBox "Please assign a group."
Me![cboGroup].SetFocus
ElseIf IsNull(Me!cboImpact) Then
MsgBox "Please identify the impact."
Me![cboImpact].SetFocus
End If
 
Hi Susan,

How do you know they are not working? The code is if ... then ... elseif
.... then ... end if. For example, if you are testing Me![cboState], then you
have to have Me![cboInitiator] filled in. So, to check Me![cboDescription],
you have to have Me![cboImpact], Me![cboState], Me![cboProgramArea], and
Me![cboCategory] to be non blank to testing Me![cboDescription].

I hope this answer your question.
 
Well, perhaps, as a novice, I've written the statements incorrectly. This is
how I know it's not working. I fill in cboInitiator and cboState. Then I save
the record. I then get a message to fill in cboGroup. There is no message to
fill cboProgramArea, etc.

What I want to do is to test whether any of these controls is null and
inform the user. Should I be using a different control structure like Case?

JL said:
Hi Susan,

How do you know they are not working? The code is if ... then ... elseif
... then ... end if. For example, if you are testing Me![cboState], then you
have to have Me![cboInitiator] filled in. So, to check Me![cboDescription],
you have to have Me![cboImpact], Me![cboState], Me![cboProgramArea], and
Me![cboCategory] to be non blank to testing Me![cboDescription].

I hope this answer your question.

Susan L said:
I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.

Here's the code: (ProgramArea, Category, and Description are the ones not
working)
If IsNull(Me!cboInitiator) Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
ElseIf IsNull(Me!cboState) Then
MsgBox "Please select a location."
Me![cboState].SetFocus
ElseIf IsNull(Me!cboProgramArea) Then
MsgBox "Please select a program area."
Me![cboProgramArea].SetFocus
ElseIf IsNull(Me!cboCategory) Then
MsgBox "Please select a category."
Me![cboCategory].SetFocus
ElseIf IsNull(Me!cboDescription) Then
MsgBox "Please select a description."
Me![cboDescription].SetFocus
ElseIf IsNull(Me!cboGroup) Then
MsgBox "Please assign a group."
Me![cboGroup].SetFocus
ElseIf IsNull(Me!cboImpact) Then
MsgBox "Please identify the impact."
Me![cboImpact].SetFocus
End If
Click to expand...
Click to expand...
 
Hi Susan,

Sorry, I do not mean anything about what I say. I just want to understand
more about what you have done to determine the code is not working. Because
the code is correct. The only thing that I done differently is to put extra
check like

If isnull(Me![cboInitiator]) or Me![cboInitiator] = "" then
....

End If

Sorry, if I have say something to offend you.


Susan L said:
Well, perhaps, as a novice, I've written the statements incorrectly. This is
how I know it's not working. I fill in cboInitiator and cboState. Then I save
the record. I then get a message to fill in cboGroup. There is no message to
fill cboProgramArea, etc.

What I want to do is to test whether any of these controls is null and
inform the user. Should I be using a different control structure like Case?

JL said:
Hi Susan,

How do you know they are not working? The code is if ... then ... elseif
... then ... end if. For example, if you are testing Me![cboState], then you
have to have Me![cboInitiator] filled in. So, to check Me![cboDescription],
you have to have Me![cboImpact], Me![cboState], Me![cboProgramArea], and
Me![cboCategory] to be non blank to testing Me![cboDescription].

I hope this answer your question.

Susan L said:
I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.

Here's the code: (ProgramArea, Category, and Description are the ones not
working)
If IsNull(Me!cboInitiator) Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
ElseIf IsNull(Me!cboState) Then
MsgBox "Please select a location."
Me![cboState].SetFocus
ElseIf IsNull(Me!cboProgramArea) Then
MsgBox "Please select a program area."
Me![cboProgramArea].SetFocus
ElseIf IsNull(Me!cboCategory) Then
MsgBox "Please select a category."
Me![cboCategory].SetFocus
ElseIf IsNull(Me!cboDescription) Then
MsgBox "Please select a description."
Me![cboDescription].SetFocus
ElseIf IsNull(Me!cboGroup) Then
MsgBox "Please assign a group."
Me![cboGroup].SetFocus
ElseIf IsNull(Me!cboImpact) Then
MsgBox "Please identify the impact."
Me![cboImpact].SetFocus
End If
Click to expand...
Click to expand...
Click to expand...
 
I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.
Click to expand...

Susan, two suggestions: if you're doing validation, the AfterUpdate
event of the form is just a bit too late. It fires AFTER the record
has been written to disk. The BeforeUpdate event can be cancelled
(cancelling the update), and is a better event for form validation.

Also, your ElseIf clauses will only fire if the preceding events all
failed. If you're testing multiple controls, put each of them in its
own If... End If block. You may also want to protect yourself from
users entering data and backspacing over it - which puts a zero-length
non-null String into the control, rather than a NULL - as follows:

If Me!cboInitiator & "" = "" Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
Cancel = True
End If
If Me!cboState & "" = "" Then
MsgBox "Please select a location."
Me![cboState].SetFocus
Cancel = True
End If

<etc etc>

You can use the If...ElseIf syntax, but if you do, it will find only
one error each time the user tries to save.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Not offended at all. Just mystified. thanks.

JL said:
Hi Susan,

Sorry, I do not mean anything about what I say. I just want to understand
more about what you have done to determine the code is not working. Because
the code is correct. The only thing that I done differently is to put extra
check like

If isnull(Me![cboInitiator]) or Me![cboInitiator] = "" then
...

End If

Sorry, if I have say something to offend you.


Susan L said:
Well, perhaps, as a novice, I've written the statements incorrectly. This is
how I know it's not working. I fill in cboInitiator and cboState. Then I save
the record. I then get a message to fill in cboGroup. There is no message to
fill cboProgramArea, etc.

What I want to do is to test whether any of these controls is null and
inform the user. Should I be using a different control structure like Case?

JL said:
Hi Susan,

How do you know they are not working? The code is if ... then ... elseif
... then ... end if. For example, if you are testing Me![cboState], then you
have to have Me![cboInitiator] filled in. So, to check Me![cboDescription],
you have to have Me![cboImpact], Me![cboState], Me![cboProgramArea], and
Me![cboCategory] to be non blank to testing Me![cboDescription].

I hope this answer your question.

:

I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.

Here's the code: (ProgramArea, Category, and Description are the ones not
working)
If IsNull(Me!cboInitiator) Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
ElseIf IsNull(Me!cboState) Then
MsgBox "Please select a location."
Me![cboState].SetFocus
ElseIf IsNull(Me!cboProgramArea) Then
MsgBox "Please select a program area."
Me![cboProgramArea].SetFocus
ElseIf IsNull(Me!cboCategory) Then
MsgBox "Please select a category."
Me![cboCategory].SetFocus
ElseIf IsNull(Me!cboDescription) Then
MsgBox "Please select a description."
Me![cboDescription].SetFocus
ElseIf IsNull(Me!cboGroup) Then
MsgBox "Please assign a group."
Me![cboGroup].SetFocus
ElseIf IsNull(Me!cboImpact) Then
MsgBox "Please identify the impact."
Me![cboImpact].SetFocus
End If
Click to expand...
Click to expand...
Click to expand...
 
John: Thanks so much for the instructive advice. I will try out your
suggestions, both on the event of choice and the structure of the If
statement. Just so I understand what the syntax means (I'm learning):

The first set of quotes after the ampersand means the zero-length string?
And the second set means "empty" or "null"? And the Cancel = True cancels the
update until the user enters something in the control?

John Vinson said:
I've set up validation criteria for null values in the After Update event of
a form. The validation scheme works fine for 4 of the controls, but not for 3
of them. They are all combo boxes, so that isn't the problelm. I tried
putting the validation for these 3 in a different form event, then in the On
Enter and On Dirty events of a text box control. The references to the combos
contain the correct names of the controls.
Click to expand...

Susan, two suggestions: if you're doing validation, the AfterUpdate
event of the form is just a bit too late. It fires AFTER the record
has been written to disk. The BeforeUpdate event can be cancelled
(cancelling the update), and is a better event for form validation.

Also, your ElseIf clauses will only fire if the preceding events all
failed. If you're testing multiple controls, put each of them in its
own If... End If block. You may also want to protect yourself from
users entering data and backspacing over it - which puts a zero-length
non-null String into the control, rather than a NULL - as follows:

If Me!cboInitiator & "" = "" Then
MsgBox "Please select an initiator."
Me![cboImpact].SetFocus
Cancel = True
End If
If Me!cboState & "" = "" Then
MsgBox "Please select a location."
Me![cboState].SetFocus
Cancel = True
End If

<etc etc>

You can use the If...ElseIf syntax, but if you do, it will find only
one error each time the user tries to save.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
The first set of quotes after the ampersand means the zero-length string?
Yes.

And the second set means "empty" or "null"?
Click to expand...

Not exactly. What it's doing is concatenating the value in the control
- which might be NULL, a valid string, or a zero-length string - to
"". The result will be a zero-length string if the control contains
either NULL or a zero-length string; the comparison with "" will then
determine whether the control is "empty".

Normally, there won't be a ZLS but if the user enters something, and
then backspaces it away, the control will not be NULL but will contain
a ZLS; this test is safer than just checking to see if the control is
NULL to allow for this possibility.
And the Cancel = True cancels the
update until the user enters something in the control?
Click to expand...

Exactly. That's why you need a msgbox, otherwise you'll have a
thorougly mystified user!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top