requiring non null field values in a form or query

P

Paul James

Is there any way I can require that a field have a non-null value in a form
or query?

I know how to do this in a table definition by setting the Required property
to Yes, but I don't want to do this in the table definition. I need to do
it in either a form or query.

Any suggestions about this?

Thanks in advance,

Paul
 
L

Lori

In the before update event check for null then do a msg
box and set cancel = true.

Lori
 
R

Reggie

Paul, you could set the before update event of the form to check the field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyControl) Then
MsgBox "Can't be null"
Cancel = True
Me.MyControl.Setfocus
End If
End Sub
 
P

Paul James

Thanks for your help with this, Reggie.

A question: do I need to be concerned with zero-length strings, or will If
IsNull(Me.MyControl) work for those as well?

Paul
 
R

Reggie

Paul, If your field allows zero length strings change your statement to

If IsNull(Me.MyControl) Or Me.MyControl = ""
 
K

Ken Snell

A shorter way to check both zero-length string and Null is this:

If Len(Me.MyControl & "") = 0 Then
' the value is either Null or empty string

Else
' the value is not Null and not empty string

End If
 
Top