How can I make it easy to prevent Duplicate Contacts from being entered in a table

[JJordan]

"A Jeffery Jordan Already Exists. Continue Update ?"

Yes No

No -- gives a message "No Current Record" and Form is Erased !!!!!!!!



Yeeeee Haaaaa ! Thank You !!!!!!!!!!

I set up one for first and last name on the After Update for LastName and another for CompanyName after update. They seem to Work Great !!!!! Checks names after you enter Last Name and company after you enter company.

 

[Shep]

Ah, good. Glad to hear it. Congratulations.

You'll probably find you need to change a few things to suit the structure of your database.

Rich, yes it works fine to combine two fields, though I only know it because I had no idea such a thing was not possible. I simply tried it one time. I try all kinds of strange things. Most don't work. har har

On second thought, I may have seen an expression in Access Help that used two fields. A bit hazy on that, though.

Shep

 

[Rich]

Thanks Shep, I've added that one to the list.
The problem with validation of course is Human consistency on data entry.
Can it be relied on?

 

[gabo]

Problem....

I already avoid the error but now when I run the code nothing happens, even when the name that I'm trying to introduce is already in the table

help please here is my code:

Private Sub ApMat_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset
Dim strContact As String
If Not IsNull(Me.Nombre) And Not IsNull(Me.ApPat) Then
strContact = Me.Nombre & " " & Me.ApPat
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("FichaClinica", 2)
rst.FindFirst "[Nombre] & [ApPat] Like '*" & strContact & "*'"
With rst
If rst.NoMatch Then
.Close
GoTo Exit_ApMat_AfterUpdate
Else
If MsgBox("A " & !Nombre & " " & !ApPat & " already exists. Continue update?", 260) = vbYes Then
.Close
GoTo Exit_ApMat_AfterUpdate
Else
Cancel = True
Me.Undo
.Close
End If
End If
End With
End If

Exit_ApMat_AfterUpdate:
Set dbs = Nothing


End Sub

 

[belsha]

Adding a list box to this code

I used the post above to create the code below, which works fine for the user. What she would like, however, is to be able to look at the list box that resides on the form now (lstPatient) which contains a number of fields that could potentially identify a duplicate and see if the name is there BEFORE having to choose No, don't add the person, or Yes, go ahead. When the message box displays, you can't use the list box (of course), until after you have chosen to go on or not. Any suggestion? She does not want to look through a box prior to entry however, as someone else suggested.

Private Sub FName_BeforeUpdate(cancel As Integer)
Dim iAnswer As Integer
Dim pPrompt As String
pPrompt = "Check the patient name, it already exists in the database." & vbCrLf
pPrompt = pPrompt & "Would you like to continue adding this patient?"


If Not IsNull(DLookup("[FName]", "tblMain", "[FName]='" & Me![FName] & "' and [LName]= '" & Me![LName] & "'")) Then
iAnswer = MsgBox(pPrompt, vbYesNo + vbQuestion + vbDefaultButton1 + vbSystemModal, "Check Names")
If iAnswer = vbNo Then
'User doesn't want to add the name
Me.Undo
cancel = True
End If
End If
End Sub