Force data entry of a field on a form

B

Bob B

How can I be sure data is entered in a field on a form. I have a field that
has list values and if the wrong value is enterd it will detect the error. It
does not prevent the operator from tabbing over the field thus not entering
anything
 
J

Jon Ley

Use the Before Update event for the form to check that the required control
has been populated. If it hasn't alert the user and cancel the event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(MyControl) Then
MsgBox "Please enter data"
MyControl.SetFocus
Cancel = True
End If

End Sub
 
S

Steve Schapel

Bob,

In design view of the table, set the Validation Rule property to Is Not
Null.
 
B

Bob B

This doesn't work. My code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull("SEX") Then
MsgBox "Please Enter Data"
SEX.SetFocus
Cancel = True
End If
End Sub
 
J

Jon Ley

This doesn't work because "SEX" is a literal string, so this will never be
null. Assuming SEX is the name of the control on the form then you should
omit the quotes:

If IsNull(SEX) Then

Alternatively you could use Steve's solution which gets away from having to
write any code at all. And because the rule is at the table definition level
it can never be broken, whereas with my offered solution you have to remember
to guard against possible null entry every time you include this field on a
different form.

The reason I instictively came up with the solution that I did is that I
normally use unbound forms. This can give a safer level of control over data
vaildation before writing back to live tables. If you were using this
approach, you would not use the form's BeforeUpdate event. Instead there
would be a 'Save' button that the user would click, and any final validation
would be in this button's Click event code (you might also have validation on
individual controls' AfterUpdate or BeforeUpdate events). Anyway, the whole
bound/unbound question is probably an issue too far for this discussion!

Hope you get something to useful out of all this.

Jon.
 
D

Douglas J. Steele

Get rid of the quotes.

Assuming your control is named Sex, try:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SEX) Then
MsgBox "Please Enter Data"
Me.SEX.SetFocus
Cancel = True
End If
End Sub
 
Top