SetFocus on a userform Does Not Work

[Don]

I have a fairly simple userform with a frame and two
textboxes inside the frame. If a user makes an invalid
entry I want to display a message, clear the entry, and
set the focus back to the same field.

The mesgbox displays perfectly, and the entry is cleared
fine. However, the focus keeps going to the next textbox
in the frame. I am getting frustrated! I even tried
setting the focus back to the frame first, and then to the
field. I also tried using the Exit event with no success.

I am using the AfterUpdate event and the name of my
textbox is txtClientNumber. My code appears below.

Any ideas would be apprecaited. Thanks so much in advance.


Private Sub txtClientNumber_AfterUpdate()

Dim StrClientName As String
Dim rstClientName As ADODB.Recordset
Set rstClientName = New ADODB.Recordset

StrClientName = "SELECT client_name FROM hbm_client
WHERE client_code = '" & txtClientNumber.Value & "'"

rstClientName.Open StrClientName, Main.cnn

If rstClientName.EOF = True Then
MsgBox "That is an Invalid Client Number"
txtClientNumber.Value = Null
frmMain.fme1.SetFocus
txtClientNumber.SetFocus '***PROBLEM HERE
Else
rstClientName.MoveFirst
txtClientName.Value = rstClientName
("client_name").Value
End If

End Sub

 

[Jay Freedman]

Hi Don,

You're using the wrong event, that's all. Remove all the code from the
txtClientNumber_AfterUpdate handler and put it into the
txtClientNumber_Exit handler. That procedure has a boolean Cancel
argument. When the entry is invalid, set Cancel to True. That will
prevent the cursor from leaving the txtClientNumber control.