how do I alert user (ASAP) if record already exists?

[Guest]

I have a form with Security Data with SSN being the primary key (no
duplicates). This form is pretty busy with many fields, SSN being at the
top. I would like to alert the user with a message if the Record for a
certain SSN already exists before they populate the rest of the fields.
Currently there is a message that record exists but it doesn't show until you
try to save the record.

I would like to alert the user as soon as SSN is entered. Any ideas would
be appreciated. Thanks!

ynj

 

[Barry Gilbert]

In the BeforeUpdate event of the SSN control use the DCount function to
determine if the record exists. Something like this:

Private Sub txtSSN_BeforeUpdate(Cancel As Integer)
If DCount("SSN","MyTable","[SSN] = '" & Me.txtSSN & "'")>0 Then
Msgbox "SSN already exists."
Cancel=True
Endif
End Sub

If it finds a record with that SSN, it will display a messagebox and
not let the user exit the textbox until they correct it.

HTH,
Barry

 

[Guest]

Very easy to do. Use the Before Update event of the text box where you enter
the SSN. Use the DLookup to see if the SSN exists and if it does, cancel the
event and warn the user:

If Not IsNull(DLookUp("[SSN]", "TableName Here", "[SSN] = '" & Me.txtSSN &
"'")) Then
MsgBox "SSN " & Me.txtSSN & " Is Already in the Table"
Me.txtSSN = Null
Cancel = True
End If

In the code above, [SSN] is the field in your table where you store the SSN,
TableName Here should be the name of the table where the SSN is stored, and
Me.txtSSN is the name of the text box where you enter the SSN.
The DLookup function will return Null if the critera entered does not return
a match in the table, so if Null is returned, the SSN is not in the table and
you can proceed. If it does return a value, the message box is presented,
the SSN text box is cleared, and the event is canceled so the cursor stays in
the SSN field.

Now, the question is, is this a Data Entry form? I mean, is it for adding
new records only? If you want to find existing SSN records and make
modifications, the code above would not be the way to do it. You would need
to modify it to ask the user if they want to edit the record or try again.