Set focus to a control if it's null

[Guest]

I have a form where there are two controls I want users to fill in but don't
want to set the property to required. I want the form to not be able to
close if they haven't filled in these two fields and if they tab past the
field, it should give you a message box saying you must fill something in and
stay in that control until something is chosen from the drop down boxes. The
set focus and docmd.gotocontrol are not really working, they can still close
the form and tab past that control.

 

[Allen Browne]

Use the BeforeUpdate event procedure of the *form* (not the control's
events) to test if the field IsNull(). Cancel the event if you are not
happy.

If the user attempts to close the form (using the X at the right end of the
form's title bar), they will be notified the record cannot saved, and asked
whether to close anyway (giving up on entering this record), or to stay with
the form and fix the entry. You do NOT want to override this approach: if
you do, a frustrated user will just switch off the computer or crash out of
Access (Ctrl+Alt+Del.) That's a good way to corrupt the database, so you
don't want to lock your users into scenarios where that's the only choice
they think they have to get out fast.

 

[Guest]

It's not working and I don't know what I'm doing wrong. Is it
Do.cmd.CancelEvent or am I not dimensioning my variables? I tried Cancel =
True but it doesn't like that. Here is what I wrote:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If [EPM Attention] = "Choose One" Then
MsgBox "You must enter a name from the menu even if it is Unknown",
vbCritical, "Name Selection Entry"
DoCmd.GoToControl "EPM Attention"
Me.EPM_Attention.SetFocus
DoCmd.CancelEvent
End If

If [Building] = "Pick One" Then
MsgBox "You must enter a building number", vbCritical, "Name
Selection Entry"
DoCmd.GoToControl "Building"
Me.Building.SetFocus
End If

DoCmd.Close

End Sub

 

[Allen Browne]

If:
- this form is bound to a table, and
- the [EMP Attention] control is bound to a field, and
- you want to test if the user left it blank

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(me.[EPM Attention]) Then
Cancel = True
MsgBox "You must enter [EPM Attention]."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It's not working and I don't know what I'm doing wrong. Is it
Do.cmd.CancelEvent or am I not dimensioning my variables? I tried Cancel
=
True but it doesn't like that. Here is what I wrote:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If [EPM Attention] = "Choose One" Then
MsgBox "You must enter a name from the menu even if it is Unknown",
vbCritical, "Name Selection Entry"
DoCmd.GoToControl "EPM Attention"
Me.EPM_Attention.SetFocus
DoCmd.CancelEvent
End If

If [Building] = "Pick One" Then
MsgBox "You must enter a building number", vbCritical, "Name
Selection Entry"
DoCmd.GoToControl "Building"
Me.Building.SetFocus
End If

DoCmd.Close

End Sub

Use the BeforeUpdate event procedure of the *form* (not the control's
events) to test if the field IsNull(). Cancel the event if you are not
happy.

If the user attempts to close the form (using the X at the right end of
the
form's title bar), they will be notified the record cannot saved, and
asked
whether to close anyway (giving up on entering this record), or to stay
with
the form and fix the entry. You do NOT want to override this approach: if
you do, a frustrated user will just switch off the computer or crash out
of
Access (Ctrl+Alt+Del.) That's a good way to corrupt the database, so you
don't want to lock your users into scenarios where that's the only choice
they think they have to get out fast.