Best way to check Data.

[ChampionDuy]

I have a form where a user enters in a SSN. What I am trying to accompolish is that when the user tabs out of this field or tries to click anywhere else on the form that the table is checked to see if that SSN already exists. If the SSN already exists I do not want the user to be able to leave this field untill he or she enters in a SSN that is valid.

I know the code on how to check to see if the SSN exists or not and I have put it on the Lost Focus event, Change event, After Update event. No matter where I put it, if the user clicks on anyother text box on the form the focus changes to that new text box. I have copied and pasted my code that I have put on the events to let you see what I am doing and see if you can show me my errors. Thanks


Private Sub T_SSN_AfterUpdate()
If Not IsNull(DLookup("T_SSN", "tblTrueAgent", "T_SSN = '" & Me!T_SSN & "'")) Then
MsgBox "Agent with that SSN already Exists, Please enter in a new SSN."
T_SSN.SetFocus


End If
End Sub

 

[Mile-O]

Use the BeforeUpdate() event.

 

[Mile-O]

Private Sub T_SSN_BeforeUpdate(Cancel As Integer) 
    If Not IsNull(DLookup("T_SSN", "tblTrueAgent", "T_SSN = '" & Me!T_SSN & "'")) Then 
        MsgBox "Agent with that SSN already Exists, Please enter in a new SSN." 
        Cancel = True
    End If 
End Sub

 

[ChampionDuy]

THANKS

I cant believe that I didnt try that. Thanks so much. I feel like an idiot now. Thanks again.

 

[missinglinq]

If the SSN already exists I do not want the user to be able to leave this field untill he or she enters a SSN that is valid.

Mile-O-Phile (as he often does) has provided the intelligent, logical answer to your question. The problem that I see, however, is that your question assumes that the user has entered an incorrect SSN. Unfortunately, making a mistake in entering your SSN is not a very common. You've not made any provision, however, for the possibility that the user has entered a valid SSN that has been previously entered.