TextBox On Lost Focus()

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Will_B

Board Regular
Joined
Mar 4, 2009
Messages
79
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.
 
Last edited:

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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!
 
Last edited:

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,857
Messages
5,766,788
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top