set focus not working

[xcao]

I have a form with two fields, one is registration num,
the other is association name.

If an invalid regNo is entered, I check it in the
underlying table, if there is no record, a message box
shown telling need to reenter a valid regno.
My code set the focus back to the regno, but actually after I
click message box OK, then the focus goes to the next
text fields, association name instead of regno field.


Here is the code:
Private Sub PrincipalNo_AfterUpdate()

SQLWhere$ = "([AssocRegistrationNo] like Forms!
[frmPrincipalCheckIn]![PrincipalNo])"

If IsNull(DLookup("[AssocName]", "tblAssociation",
SQLWhere$)) Then

MsgBox "Record not found, please enter a valid
regno"

Forms![frmPrincipalCheckIn]![PrincipalNo] = ""
Forms![frmPrincipalCheckIn]![PrincipalNo].SetFocus

Else
Me.txtPrincName =
DLookup"[AssocName]", "tblAssociation", SQLWhere$)
confirm.SetFocus

End If
.

Thanks for any ideas

 

[ghudson]

You need to use the BeforeUpdate event and Cancel the event if the user does not key a valid number. I would use a combo box instead and only let them select the numbers from the combo box [set the Limit to list = Yes] to prevent your problem before it starts. Search this site for Cascading Combo Boxes for some examples.

HTH