Setting the Focus to a Control if it is empty

[Hoopster]

Hey guys,

I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....

Private Sub EmployeeName_Exit(Cancel As Integer)

Dim varEmployee As Variant

If Not IsNull(Me.[EmployeeName]) Then
varEmployee = DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")
End If

If IsNull(Me.EmployeeName) Then
MsgBox ("You must choose an Employee Name!")

'Forms!EmployeeProformance!EmployeeName.SetFocus
End If

End Sub

 

[Jeanette Cunningham]

Making sure that a user enters a value in a required field is best done in
the Before Update event of the form, assuming that you are using a bound
form.
The Before Update event of the form has a cancel argument, if the control is
empty, you set Cancel = True and pop up the message box.
You can do it in the before update event of the Employee_Name control, but
it won't prevent the form saving while the employee name is blank.

Jeanette cunningham

 

[Bill]

Your SetFocus statement is made to be a comment.

See: 'Forms!EmployeeProformance!EmployeeName.SetFocus

Bill

 

[SDCT]

Try setting the focus before the msgbox. You can also simplify this
into one IF statement.


If Not IsNull(Me.[EmployeeName]) Then
varEmployee = DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")
Else

Forms!EmployeeProformance!EmployeeName.SetFocus
MsgBox ("You must choose an Employee Name!")

End If

 

[Klatuu]

Your code should be in the control's Before Update event. It can be canceled
and if it is, the focus stays on the control. The exit is too late because
the update is already done. In your case, it may not matter, but if their is
a constraint on the field the control is bound to that prevents nulls, it
will error out:

Private Sub EmployeeName_BeforeUpdate(Cancel As Integer)
Dim varEmployee As Variant

If IsNull(Me.[EmployeeName]) Then
MsgBox ("You must choose an Employee Name!")
Cancel = True
Else
If IsNUll(DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")) Then
MsgBox "Employee Not Found"
Cancel = Trhe
End If
End If

End Sub

 

[John W. Vinson]

Hey guys,

I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....

The setfocus statement is commented out; is it failing if you remove the ' at
the start of the line? Why are you setting varEmployee at all, given that
you're not using it?

Try simplifying the code a bit:


Private Sub EmployeeName_Exit(Cancel As Integer)

If IsNull(Me.[EmployeeName]) Then
MsgBox ("You must choose an Employee Name!")
Me!EmployeeName.SetFocus
End If

End Sub

John W. Vinson [MVP]

 

[Hoopster]

Klayuu,

Sorry guy but this don't work at all. Now it just goes to the next
Control and I don't even get the Error Message.

Your code should be in the control's Before Update event. It can be canceled
and if it is, the focus stays on the control. The exit is too late because
the update is already done. In your case, it may not matter, but if their is
a constraint on the field the control is bound to that prevents nulls, it
will error out:

Private Sub EmployeeName_BeforeUpdate(Cancel As Integer)
Dim varEmployee As Variant

If IsNull(Me.[EmployeeName]) Then
MsgBox ("You must choose an Employee Name!")
Cancel = True
Else
If IsNUll(DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")) Then
MsgBox "Employee Not Found"
Cancel = Trhe
End If
End If

End Sub

--
Dave Hargis, Microsoft Access MVP

Hey guys,

I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....

Private Sub EmployeeName_Exit(Cancel As Integer)

Dim varEmployee As Variant

If Not IsNull(Me.[EmployeeName]) Then
varEmployee = DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")
End If

If IsNull(Me.EmployeeName) Then
MsgBox ("You must choose an Employee Name!")

'Forms!EmployeeProformance!EmployeeName.SetFocus
End If

End Sub

 

[Klatuu]

Should not be happening. This is a fairly common thing to do and I have used
similar code many times.

For the first part to show the message, Me.EmployeeName has to be a Null
Value. It will not catch it if it is a null string. To check for that, you
can use this:
If Trim(Nz(Me.[EmployeeName], "")) = vbNullString Then

For the second part, it should show the message when an employee name is
entered that is not in the EmployeeList table.

Have you run the code in Debug mode to see what the values are and what is
happening?
--
Dave Hargis, Microsoft Access MVP

Klayuu,

Sorry guy but this don't work at all. Now it just goes to the next
Control and I don't even get the Error Message.

Your code should be in the control's Before Update event. It can be canceled
and if it is, the focus stays on the control. The exit is too late because
the update is already done. In your case, it may not matter, but if their is
a constraint on the field the control is bound to that prevents nulls, it
will error out:

Private Sub EmployeeName_BeforeUpdate(Cancel As Integer)
Dim varEmployee As Variant

If IsNull(Me.[EmployeeName]) Then
MsgBox ("You must choose an Employee Name!")
Cancel = True
Else
If IsNUll(DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")) Then
MsgBox "Employee Not Found"
Cancel = Trhe
End If
End If

End Sub

--
Dave Hargis, Microsoft Access MVP

Hey guys,

I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....

Private Sub EmployeeName_Exit(Cancel As Integer)

Dim varEmployee As Variant

If Not IsNull(Me.[EmployeeName]) Then
varEmployee = DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '" & _
Me.[EmployeeName] & "'")
End If

If IsNull(Me.EmployeeName) Then
MsgBox ("You must choose an Employee Name!")

'Forms!EmployeeProformance!EmployeeName.SetFocus
End If

End Sub

 

[Hoopster]

Hey guys,

I have tried all of these recommended solutions. Best I can get is to
display the error box. After that, the cursor jumps to the next Control. The
EmployeeName Control is an unbound Control. I was more interesed in makeing
it easy for the user to select the Employee Name and use their choice to fill
in the EmployeeID field.
Since the EmployeeName Control is an unbound Control, will this prevent
the SetFocus from working?

Hey guys,

I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....

The setfocus statement is commented out; is it failing if you remove the ' at
the start of the line? Why are you setting varEmployee at all, given that
you're not using it?

Try simplifying the code a bit:


Private Sub EmployeeName_Exit(Cancel As Integer)

If IsNull(Me.[EmployeeName]) Then
MsgBox ("You must choose an Employee Name!")
Me!EmployeeName.SetFocus
End If

End Sub

John W. Vinson [MVP]

 

[John W. Vinson]

I have tried all of these recommended solutions. Best I can get is to
display the error box. After that, the cursor jumps to the next Control. The
EmployeeName Control is an unbound Control. I was more interesed in makeing
it easy for the user to select the Employee Name and use their choice to fill
in the EmployeeID field.

UM....!?

Why the rigamarole with two controls and VBA code? If that's all you want give
the user a Combo Box displaying the employee name and storing the EmployeeID.

John W. Vinson [MVP]

 

[J_Goddard via AccessMonster.com]

Instead of using SetFocus, try setting Cancel = True, to cancel the Exit.

John

Hey guys,

I have tried all of these recommended solutions. Best I can get is to
display the error box. After that, the cursor jumps to the next Control. The
EmployeeName Control is an unbound Control. I was more interesed in makeing
it easy for the user to select the Employee Name and use their choice to fill
in the EmployeeID field.
Since the EmployeeName Control is an unbound Control, will this prevent
the SetFocus from working?
[quoted text clipped - 20 lines]

John W. Vinson [MVP]