Set Focus question

[Dartagnan]

I am using Access 2002 SP3 and I have the following code running behind the StaffID field in the After Update event.

If Me.StaffID = DLookup("[staffid]", "[staff]", "[staffid] = form![staffid]") Then
MsgBox ("The Staff ID you entered is already in use." & vbCrLf & "Please enter a new Staff ID."), vbExclamation
Me.StaffID.SetFocus
End If

For some reason the setfocus doesn't work. My prompt keeps moving to the next field down. I want to make sure they can't leave the field until they entere a valid staff id or they click the Cancel button. On the other hand I don't want the message to fire multiple times either. If I put the same code in the On Exit event the message box fires twice.

Any thoughts?

Thank you in advance.

 

[Norie]

I've one idea - use a combobox to list the existing staff IDs.

You could also use it's NotInList event if you want to create new staff IDs and add them to the table,

 

[Dartagnan]

Thanks for the feedback. Any thoughts as to why the setfocus doesn't work?

 

[Norie]

why the setfocus doesn't work?

Not really.

But then again I rarely use it because it's unreliable, as you've found out it seems.