Moving cursor to field on error

[magmike]

I use the following sub to warn about fields that should not be left blank:
--------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyCompany) Then
Cancel = True
MsgBox "You forgot 'My Company'!"
End If
End Sub

 

[Klatuu]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyCompany) Then
Cancel = True
MsgBox "You forgot 'My Company'!"
Me.MyCompany.SetFocus
End If
End Sub

However, I would suggest you put your original code in the Before Update
event of the control rather than in the Form's Before Update. That way, the
focus stays in the control when Cancel = True.

 

[magmike]

I'm not getting it to work right when doing it through the control. If I
have never entered that control, it still doesn't seem to go back to the
control, however, if I do it through the form properties, adding the line
you suggested, it works fine.

HOWEVER, if I want to add other field checks to the sub, it works good, but
displays one message box right after the other. How could I have the sub
cancel after the first IF was ran and found to be Null?

 

[Klatuu]

You are correct. If you do not enter any data in a control, it's Before
Update event does not fire. If you want to check one control at a time in
the Form Before Update, you can add an exit tag to your sub and go to it the
first time a control fails the test:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyCompany) Then
Cancel = True
MsgBox "You forgot 'My Company'!"
Me.MyCompany.SetFocus
GoTo Exit_FormBeforeUpdate
End If

If IsNull(Me.AnotherControl) Then
Cancel = True
MsgBox "You forgot 'AnotherControl'!"
Me.AnotherControl.SetFocus
GoTo Exit_FormBeforeUpdate
End If

Exit_FormBeforeUPdate:
End Sub

 

[magmike]

Wow! Great...thanks for that.

I am getting an error, though. I get it when I test not filling out the
right form fields, and then clicking on a command button (as if I forgot to
fill out the other fields). I get the following:
----------------------
Run-time error '2107':

The value entered doesn't meet the validation rule defined for the field or
control.
----------------------

Before the error, I get the corresponding message box. I get this error
after hitting "okay" on the message box. When I hit "Debug" it brings me to
the command button's sub. It only seems to happen on command buttons that
change the recordsource of the form.

Any ideas?

 

[Klatuu]

It would be difficult to tell without having it in front of me and knowing
exactly what the code is and on which line it is failing. Based on the
message, I think you have left a validation rule in place somewhere. If you
are using the method I suggested, then you should delete the validation rules.

Also, if you are changing recordsets and the validation rule is at the
control level, the new recordset may not meet that rule or the new recordset
has a rule imposed that conflicts with the current data in the controls.

 

[magmike]

You're right. I still had validation rules at the table level. When I
cleared those, the errors went away. Thanks for all your help.