Validate That a SSN isn't already on file

[JGravesNBS]

I have the following code that works, however when I acknowledge "the SSN already on file" message the focus goes to the next field to the right.

The record is not saved at this point so I can't use SetFocus, I want the focus to stay on SSN until a valid SSN is entered

===========================
Private db As DAO.Database
Private rst As DAO.Recordset

'09/28/15 temp new member table

Private Sub SSN_BeforeUpdate(Cancel As Integer)

Exit Sub

Dim strID As String
Dim strExpression As String
Dim strDomain As String
Dim strCriteria As String

strID = Me.[SSN] 'String ID
strExpression = "*"
strDomain = "[Member Info]"
strCriteria = "[SSN] = '" & strID & "'" 'String ID

If IsNull(Me.[SSN]) Or Me.[SSN] = "" Then
Cancel = True
End If

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT " & strExpression & _
" FROM " & strDomain & _
" WHERE " & strCriteria, _
dbOpenDynaset)
If rst.RecordCount = 0 Then

MsgBox "This SSN: " & " " & Me.[SSN] & " " & "has not been used"
Else
MsgBox "This SSN: " & " " & Me.[SSN] & " " & "has been used, review the SSN for correctness and reenter"
Me![SSN].Undo
Cancel = True
Exit Sub

End If

End Sub

 

[MarkK]

What happens if after this . . .

Me![SSN].Undo
Cancel = True

. . . you do . . .

Me.SSN.SetFocus

. . . ?

 

[JGravesNBS]

Me![SSN].Undo
Me.SSN.SetFocus
Cancel = True

Run-time error '2108'
You must save the field before SetFocus method

 

[arnelgp]

Private db As DAO.Database
Private rst As DAO.Recordset

'09/28/15 temp new member table

Private Sub SSN_BeforeUpdate(Cancel As Integer)

Exit Sub

Dim strID As String
Dim strExpression As String
Dim strDomain As String
Dim strCriteria As String

strID = Me.[SSN] 'String ID
strExpression = "*"
strDomain = "[Member Info]"
strCriteria = "[SSN] = '" & strID & "'" 'String ID

If IsNull(Me.[SSN]) Or Me.[SSN] = "" Then
Cancel = True
Exit Sub
End If

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT " & strExpression & _
" FROM " & strDomain & _
" WHERE " & strCriteria, _
dbOpenDynaset)
If rst.RecordCount = 0 Then

MsgBox "This SSN: " & " " & Me.[SSN] & " " & "has not been used"
Else
MsgBox "This SSN: " & " " & Me.[SSN] & " " & "has been used, review the SSN for correctness and reenter"
Me![SSN].Undo
Cancel = True
Exit Sub

End If

End Sub

 

[JGravesNBS]

All is OK, Thanks for you help