requiring non null field values in a form or query

[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

 

[Dale Fye]

Paul,

Use the controls BeforeUpdate() event. Air code below
checks to see whether the text property of the control has
a length of zero (NULL or zero length string).

If this control is a text box, you need to use the text
property rather than the value because

Private Sub yourControl_BeforeUpdate(Cancel as integer)

If LEN(me.yourControl & "") = 0 then
Cancel = True
msgbox "Control cannot be empty"
Exit sub
End if

End Sub

 

[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

 

[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

 

[Paul James]

Thanks for your reply, Dale.

I've got two questions about your reply.

1. You may have clicked the Send button before finishing the following
sentence in your message:

If this control is a text box, you need to use the text
property rather than the value because

What did you mean to put in the rest of that sentence? and

2. In that sentence above, I'm not sure what you mean by "use the text
property rather than the value." Could you say a few more words about that?

Paul

 

[Reggie]

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

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

 

[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

 

[Paul James]

Thanks, guys.

Paul