Cannot set focus on a Textbox Word VBA

[BobHunter]

I have a textbox on a userform which has the following trigger :

Private Sub txtLotId_AfterUpdate()
Lot_Id = frmMainForm.txtLotId.Text
If Lot_Id <> &quot;&quot; Then

SQLStmt = &quot;SELECT POS,PIT FROM PIT_TABLE WHERE LOT = '&quot; & Lot_Id & &quot;'&quot;


Set EmpDynaset = OraDatabase.DbCreateDynaset(SQLStmt, 0&)

PosName = EmpDynaset.Fields(&quot;Pos&quot.Value
PitName = EmpDynaset.Fields(&quot;Pit&quot.Value

If IsNull(PosName) Or IsNull(PitName) Then
Message = &quot;Lot &quot; & Lot_Id & &quot; is not active!&quot;
response = MsgBox(Message, vbOKOnly, &quot;Lot ID Does Not Exist&quot
frmMainForm.lblMessage.Caption = Message
frmMainForm.txtLotId.Text = &quot;&quot;
frmMainForm.txtLotId.SetFocus
Exit Sub
End If

frmMainForm.lblPosName.Caption = PosName
frmMainForm.lblPitName.Caption = PitName

End If

End Sub

The problem is it DOES NOT set the focus back to the textbox but to the button beside it. I've googled this and tried the solution to no avail. Tried the Change event but this does not trigger when the textbox loses focus.

Can anyone offer me a solution ?

Thanks in advance.

 

[BobHunter]

Okay, simple version - can anyone get the below to work ?

Private Sub txtLotId_AfterUpdate()

frmMainForm.txtLotId.Text = &quot;&quot;
frmMainForm.txtLotId.SetFocus
End Sub

 

[TonyJollans]

Hi BobHunter,

I don't think it is possible to overcome this, but why do you want to set the focus back to a field AFTER Update? In effect you are validating after the event, you should be doing it in the BEFORE Update event.

What happens is that the SetFocus works (check the ActiveControl after it to see this), but there are other things which happen after the event has finished - in particular setting the focus to wherever the user has moved to. I don't know the precise way it works but events due to the user's action are effectively stacked and processed in turn and the Enter and GotFocus events on the 'new' control (amongst others) happen after the AfterUpdate event on the 'old' control. You can stop these events firing by setting Cancel=True in the BeforeUpdate event.

Enjoy,
Tony

 

[BobHunter]

Thanks for that, after your advice I managed to HACK it !

I placed a button which would get focus after my textbox and hid this behind the textbox so it would not be visible.

I then placed the following trigger on it.

Private Sub btnReturnFocus_Enter()
frmMainForm.txtLotId.SetFocus
End Sub

Works a treat - but no doubt it'll cause bother in few months time

 

[TonyJollans]

Hi BobHunter,

Glad you found a solution - but will it work if the user moves away with the mouse?

Anyway - purely by chance I have just found a way to do it. See thread702-574856 where it is described as a bug in Access. I do not understand it and have asked if anyone else can explain but it looks like it works a treat for what you want.

Enjoy,
Tony