Prevent Data Entry Duplicates 1

[aaabuhalime]

Hi,
I have a data entry form to add new customers, I have two filds in the form First Name, and Last name field, I am rying to prevent any duplicate entries, I am using the code below to prevent any duplicate entries for both names, it seems I am missing somthing , I have placed the code in the Beforeupdate Property for the LName field it is working fine, what I am trying to do I want he code to check both names then detrmine wether the it is a duplictae entry or not, I need help in what needs to be changed in the code, and where I need to place the code , on the form cutrrent property or on load please help !

any help will be higly appreciated.
Thanks

 Dim Answer1 As Variant
 Dim Answer2 As Variant
 Answer1 = DLookup("[FName]", "tblCustomers", "[FName] = '" & Me.FName & "'")
 Answer2 = DLookup("[LName]", "tblCustomers", "[LName] = '" & Me.LName & "'")
 If Not IsNull(Answer2) And Not IsNull(Answer2) Then
 MsgBox "Duplicate Name Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.FName.Undo
 Me.LName.Undo
 
 Else:
 End If
End Sub

 

[PHV]

If DCount("FName", "tblCustomers", "FName='" & Me.FName & "' AND LName='" & Me.LName & "'") > 0 Then

Anyway, why not simply create an unique index on (FName,LName) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aaabuhalime]

Thank you worked

 

[aaabuhalime]

Dear PHV,
Thank you very much for help, I am using the follwing code it is working perfectly, I am trying to change few things, now once I enter a name that alrady exists I get the duplication message, and does not let me add the name, what I need to change or add is to be able to add the name after I get the message I was able to do so by removing Me.Undo, is it possible to display the duplicate record along with warning message and then let the user decide wether to enter the record or Not if how this can done, what do I need to add to the code?

Thank you very much in advance for any help

I want to

Private Sub LName_BeforeUpdate(Cancel As Integer)
    'If CheckDuplicates(FName,LName) Then
    If DCount("FName", "tblCustomers", "FName='" & Me.FName & "' AND LName='" & Me.LName & "'") > 0 Then
 
        msg = msg & "This name already exists"
        MsgBox msg, vbExclamation, "System Duplication Message"
        Me.Undo
        Cancel = True
    End If
   
End Sub

 

[Andrzejek]

You may do something like this:

Private Sub LName_BeforeUpdate(Cancel As Integer)
    'If CheckDuplicates(FName,LName) Then
    If DCount("FName", "tblCustomers", "FName='" & Me.FName & "' AND LName='" & Me.LName & "'") > 0 Then
[blue]
Select Case MsgBox("This name already exists" _
                   & vbCrLf & "Do you want to add this name anyway?" _
                   , vbYesNo Or vbQuestion Or vbDefaultButton2, "System Duplication Message")

    Case vbYes

    Case vbNo

End Select
[/blue] 
    End If
   
End Sub

Have fun.

---- Andy

 

[aaabuhalime]

Thank you Andy for your reply,
I tried the following code I added Me.Undo in Casevbno, I noticed when I added the code when I go to form view the form doesn't open , I have to close the db then reopen it then the form opens, I tried to go to go to the design view, then back to the form viw the form doent open, I have to close the db in order to be bale to open it , is theier something causing this? I tried to remove the me.indo I am getting the same exact thing, while this did not happen in the older code! any help will be appreciated , bcz this code gives exactlu what I want.

Any help will be apprciated.
Thanks

 Private Sub LName_BeforeUpdate(Cancel As Integer)
    'If CheckDuplicates(FName,LName) Then
    If DCount("FName", "tblCustomers", "FName='" & Me.FName & "' AND LName='" & Me.LName & "'") > 0 Then

Select Case MsgBox("This name already exists" _
                   & vbCrLf & "Do you want to add this name anyway?" _
                   , vbYesNo Or vbQuestion Or vbDefaultButton2, "System Duplication Message")

    Case vbYes

    Case vbNo
Me.Undo
Cancel=True
End Select
 
    End If
   
End Sub