Checking for input in required fields

[Robert]

Hello. I am trying the following code to check for input in required fields
on a form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "R" Then
If LenB(Nz(ctl, vbNullString)) = 0 Then
MsgBox "Please enter a value for " & ctl.Name & "!"
Exit Sub
End If
End If
Next ctl

End Sub

Obviously required fields have an "R" in their tag. I need help with the
Exit Sub part. As it is, when a required field is left blank, the message
box appears (good) and then then the form action continues unchanged (not
good). Like if I'm going to the next record, it goes to the next record
without waiting for the required input. Or if I'm closing the form, it
closes the form without waiting for the required input. What I need to do
is prevent any further action until the missing field has been input. How
can I do that?

Robert

 

[Marshall Barton]

Hello. I am trying the following code to check for input in required fields
on a form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "R" Then
If LenB(Nz(ctl, vbNullString)) = 0 Then
MsgBox "Please enter a value for " & ctl.Name & "!"
Exit Sub
End If
End If
Next ctl

End Sub

Obviously required fields have an "R" in their tag. I need help with the
Exit Sub part. As it is, when a required field is left blank, the message
box appears (good) and then then the form action continues unchanged (not
good). Like if I'm going to the next record, it goes to the next record
without waiting for the required input. Or if I'm closing the form, it
closes the form without waiting for the required input. What I need to do
is prevent any further action until the missing field has been input. How
can I do that?

Set the procedure's Cancel argument to True. You may also
want to set the focus to the offending control.

Cancel = True
ctl.SetFocus

 

[Robert]

Thank you.

Set the procedure's Cancel argument to True. You may also
want to set the focus to the offending control.

Cancel = True
ctl.SetFocus