Cant set focus back to textbox on exit

[Guest]

I am trying to set clear the contents of a textbox and set the focus back to
it if a condition exists. It clears the box, but icant set the focus back to
it. What am i doing wrong?

Private Sub txtEmpNo_Exit(Cancel As Integer)
Dim strEmpNo As String

On Error GoTo txtEmpNo_Exit_Error

strEmpNo = Me.txtEmpNo.Text

If IsNull(DLookup("[empno]", "tblEmployee", "[empno] = '" & strEmpNo &
"'")) Then
MsgBox "Not a valid employee number", vbInformation, "Info"
Me.txtEmpNo.Text = ""
Me.txtEmpNo.OnDirty = False
Me.txtEmpNo.SetFocus
Else
lblEmpName.Caption = DLookup("[emplname]", "tblEmployee", "[empno] =
'" & strEmpNo & "'")
End If

On Error GoTo 0
Exit Sub

txtEmpNo_Exit_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
txtEmpNo_Exit of VBA Document Form_frmTimekeeping"

End Sub

 

[Allen Browne]

Timing issue: focus hasn't left yet, so your SetFocus does nothing, and
after the event comples, the focus moves on.

To prevent the focus leaving the box, set the Cancel argument to True, i.e.:
Cancel = True

It might be better to use the BeforeUpdate event of the text box, so the
event is only fired when something has been entered. The Exit event fires
even if they user did not change anything.

 

[Guest]

thanks for the reply allen. just one more thing...

I changed to the before update as you suggested, but i can clear the
contents of the textbox and get an error message indicating that a macro is
preventing the datbase from savingt eh data in the field???

Private Sub txtEmpNo_BeforeUpdate(Cancel As Integer)

Dim strEmpNo As String

On Error GoTo txtEmpNo_Exit_Error

strEmpNo = Me.txtEmpNo.Text
Debug.Print strEmpNo

If IsNull(DLookup("[empno]", "tblEmployee", "[empno] = '" & strEmpNo &
"'")) Then
MsgBox "Not a valid employee number", vbInformation, "Info"
Cancel = True
Me.txtEmpNo.Text = "" 'cant change the contexts of the control
Else
lblEmpName.Caption = DLookup("[emplname]", "tblEmployee", "[empno] =
'" & strEmpNo & "'")
Me.txtEmpNo.SetFocus
End If

On Error GoTo 0
Exit Sub

txtEmpNo_Exit_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
txtEmpNo_Exit of VBA Document Form_frmTimekeeping"

End Sub

 

[Allen Browne]

Yes, the control's BeforeUpdate is awkward like that.

Instead of setting the value to something else, try undoing the control,
i.e.:
Me.txtEmpNo.Undo

If you still can't let the user out of the box and move on, you can put the
Undo into the AfterUpdate event procedure instead.