TextBox On Lost Focus()

[Chexpeare]

I'm using the following code within a form and it is working fine, except for one very minor issue. A user enters data into a textbox ("old_employeeID"). When the control loses focus, it runs the code and (1) checks to make sure that the data entered is not longer than 10 characters, and (2) makes sure that the employee ID being changed matches the current ID in the database. If the data entry doesn't meet the criteria, then the control is cleared with old_employeeID = Null. So far so good, but when I try to reset the focus using old_employeeID.SetFocus the cursor continues on to the next control. I tried a macro to cancel the On Lost Focus event (macCancelEvent), but it doesn't have any effect. I've also tried and adapted the same code for OnExit and it works, but the OK and Cancel buttons on the form stop working--Tab also stops working. Am I missing something about events? Why would buttons stop functioning during the On Exit event? Any advice on getting around this issue? Thanks


--------------------------------------------------------------------
Private Sub old_employeeID_LostFocus()

On Error GoTo Err_old_employeeID_LostFocus

Dim strmsg As String
Dim CurrentID As String

Const max_chars = 10

If IsNull(Forms!frm01b_Change_employeeID!old_employeeID) Then
DoCmd.RunMacro "macCancelEvent"
old_employeeID = Null
Else
CurrentID = DLookup("[employeeID]", "tblUsersList", "User_ID=forms!frm01b_Change_employeeID.User_ID")
If CurrentID = Forms!frm01b_Change_employeeID!old_employeeID Then

If Len(Me!old_employeeID) > max_chars Then

DoCmd.RunMacro "macCancelEvent"

old_employeeID = Null

strmsg = " The EmployeeID you entered exceeds a maximum of " & max_chars & " characters" & Space(20) & _
vbCrLf & " Please correct and Try Again"
MsgBox strmsg, vbCritical, "PTS: EmployeeID"

End If

Else
DoCmd.RunMacro "macCancelEvent"
MsgBox (" EmployeeID is incorrect. Please try again. ")
old_employeeID = Null

End If
End If
old_employeeID.SetFocus

Exit_old_employeeID_LostFocus:
Exit Sub

Err_old_employeeID_LostFocus:
MsgBox Err.Description
Resume Exit_old_employeeID_LostFocus

End Sub

 

[Will_B]

Instead of executing this code on the LostFocus event, use the AfterUpdate event. I've never encountered your problem because I've always used the "AfterUpdate" event for this sort of data test. The "AfterUpdate" event occurs only after the cursor has left the field, so from the user's perspective it is the same as the LostFocus event. However, from the nanosecond perspective of Access, I think it gets confused because at the moment the code runs the focus is not on anything. It is, in effect, unfocused.

Try AfterUpdate and let us know if it runs OK.

 

[Chexpeare]

Instead of executing this code on the LostFocus event, use the AfterUpdate event. I've never encountered your problem because I've always used the "AfterUpdate" event for this sort of data test. The "AfterUpdate" event occurs only after the cursor has left the field, so from the user's perspective it is the same as the LostFocus event. However, from the nanosecond perspective of Access, I think it gets confused because at the moment the code runs the focus is not on anything. It is, in effect, unfocused.

Try AfterUpdate and let us know if it runs OK.

Thanks, Will. As an FYI, the AfterUpdate event didn't work either. It yielded the same results as the LostFocus event. However, I found a solution using the On Enter event in the next control in the tab sequence; it simply checks the first control for null a value and resets the focus back to the first control.

If IsNull(Forms!frm01b_Change_employeeID!old_employeeID) Then
DoCmd.RunMacro "macCancelEvent"
old_employeeID.SetFocus
End If

Thank you for your suggestion!

 

[boblarson]

Actually you should be using the control's BEFORE UPDATE event (not the After Update and not the On Enter of another control. If you use the control's BEFORE UPDATE event and your validation fails you can issue a
Cancel = True
which will effectively keep the user in that control until it passes validation.