Can't get cursor where I want it

[mlkiser via AccessMonster.com]

Bah, VBA is becoming very frustrating. Simple taks seem anything but. I am
trying to do some very simple verification tasks when my users enter a social
security number. They enter the social in a text box called txtSSAN. If the
social is found, it autopopulates the name, email and phone number. Works
great. If it isn't, my code gives them an error message and clears the data.
That part works great. WHen it is done doing that, I simply want the cursor
to return to the txtSSAN box. But I can't get the darn thing to move there.
Here is the working code snipet:

Err_Null:
MsgBox "SSAN Not Found. Please ensure you did not enter dashes and try
again.", vbOKOnly + vbExclamation
Me.Undo
Exit Sub

I have tried adding the following after the Me.Undo statement and not one of
them has moved the cursor where I want it.

Me.txtSSAN.SetFocus
[txtSSAN].SetFocus
Forms("MyForm").txtSSAN.SetFocus

I don't understand why the cursor won't return to the right txt box. Any help
greatly appreciated.

 

[Klatuu]

It depends on where your code is and the state of the text box.
First, your validation code should be in one of two places, either in the
Before Update event of the SSAN text box or the Before Update event of the
form. I personllay prefer using the txt box.

Private Sub txtSSAN_BeforeUpdate(Cancel As Integer)

'Take out dashes if entered
Me.txtSSAN = Replace(Me.txtSSAN, "-","")

With Me.RecordsetClone
.FindFirst "[SSAN] = '" & Me.txtSSAN & "'"
If .NoMatch Then
MsgBox "SSAN " & Me.txtSSAN & " Not Found"
Cancel = True
Else
Me.BookMark = .BookMark
End If
End With

This code will make the record for the SSAN the current record if it exists.
If it does not, the Cancel = True will cancel the update of the field, empty
the text box, and leave the cursor in the control.

 

[missinglinq via AccessMonster.com]

FYI, I've run into situations where you can't set the focus on a textbox that
is open to receiving focus; the trick is to set the focus to another control
then immediately set the focus back on the desired textbox. Having said that,
Dave's code is a a nice little hack for this problem.

 

[mlkiser via AccessMonster.com]

I may try that. One more question though, why is it in the BeforeUpdate event?
I currently use it in the AfterUpdate.