validation code failing

  • Thread starter Thread starter Andre C
  • Start date Start date
A

Andre C

I have the following code in the before update section

If Me.primarycarechoice = Null Then
Cancel = True
MsgBox "This child has no carer. Please correct."
Exit Sub

primarycarechoice refers to acombobox. For some reason this never
triggers. I put a stop on the code and checked to see the value of
Me.primarycarechoice which reports being NULL and yet this code does
not trigger.

I am guessing it is something to do with the control being a combobox
so how do I check it?
 
Due to its special nature, you cannot use = to check whether a value is
Null. You must use the IsNull function.

If IsNull(Me.primarycarechoice) Then
Cancel = True
MsgBox "This child has no carer. Please correct."
Exit Sub
 
Andre C said:
I have the following code in the before update section

If Me.primarycarechoice = Null Then
Cancel = True
MsgBox "This child has no carer. Please correct."
Exit Sub

primarycarechoice refers to acombobox. For some reason this never
triggers. I put a stop on the code and checked to see the value of
Me.primarycarechoice which reports being NULL and yet this code does
not trigger.

I am guessing it is something to do with the control being a combobox
so how do I check it?
Click to expand...

No, that's probably not it. The problem that I see is this line:
If Me.primarycarechoice = Null Then
Click to expand...

By definition, nothing is ever equal to Null. Not even Null is equal to
Null. That's because Null represents the concepts of "undefined,
unknown, inapplicable", and one unknown or undefined value can't be said
to be equal to another unknown or indefined value.

Your test should use the IsNull() function instead:

If IsNull(Me.primarycarechoice) Then

Or, if there's a chance that the field to be tested may contain a
zero-length string ("") instead of Null, you can test for them both at
once using code like this:

If Len(Me.primarycarechoice & vbNullString) = 0 Then

By concatenating a zero-length string (represented by the defined
constant, vbNullString) to the value, we convert Null to a zero-length
string.
 
Back
Top