Viewing duplicate entry . 1

[aaabuhalime]

Hi
I have the following code that enables me to prevent any duplicate entry,I would like to add an other button beside the yes and No, I want to add the option to view the existing record, how can I do this , so the user can determine wether to add or to cancel, I tried the second code for some reson it is not working , Any help will be apprecieted.

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

Private Sub LName_BeforeUpdate(Cancel As Integer)
 Dim strDocName As String
 Dim strWhere As String
 strDocName = "ContactsSearchScreen"
 stWhere = "[FName] = """ & Me.[FName] & """ And [LName]=""" & Me.LName & """"
 

    'If CheckDuplicates(FName,LName) Then
    If DCount("FName", "tblContacts", "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
DoCmd.OpenForm strDocName, acPreview, , strWhere
    Case vbNo
 Me.Undo
 Cancel = True
End Select
 
    End If
   
   End Sub

 

[PHV]

for some reson it is not working
Well, not descriptive enough.
What happens ?

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

 

[Andrzejek]

You already have a 'generic' message, just add to it some details:

MsgBox("This name already exists:" _[blue]
 & vbCrLf & "Elvis Presley from Graceland - 2 times" _[/blue]
 & vbCrLf & "Do you want to add this name anyway?" _
, vbYesNo Or vbQuestion Or vbDefaultButton2, "System Duplication Message")

Have fun.

---- Andy

 

[aaabuhalime]

Dear PHV,
Thanks for your reply, I tried this code below , I can get the system msg , but I can not view the the existing matching record, what I want to do is to have an other option "view" beside the yes and No, so the user can click on it to view the existing matching record, then decide wether to add a new record or not, I tried this code but I missing few things that I need help with , to get what I want, I hope it is more clear now. Any help will be appreciated

Private Sub LName_BeforeUpdate(Cancel As Integer)
 Dim strDocName As String
 Dim strWhere As String
 strDocName = "ContactsSearchScreen"
 stWhere = "[FName] = """ & Me.[FName] & """ And [LName]=""" & Me.LName & """"
 

    'If CheckDuplicates(FName,LName) Then
    If DCount("FName", "tblContacts", "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
DoCmd.OpenForm strDocName, acPreview, , strWhere
    Case vbNo
 Me.Undo
 Cancel = True
End Select
 
    End If
   
   End Sub

code

 

[Andrzejek]

view the existing matching record" what information do you want to display to the User?

Is that going to be enough?

Private Sub LName_BeforeUpdate(Cancel As Integer)
 Dim strDocName As String
 Dim strWhere As String[blue]
 Dim i As integer[/blue]
 strDocName = "ContactsSearchScreen"
 stWhere = "FName='" & Me.FName & "' AND LName='" & Me.LName & "'"
[blue]
i = DCount("FName", "tblContacts", stWhere)

If i > 0 Then[/blue]
 
    Select Case MsgBox("This name already exists[blue]" & i & " time(s)[/blue] " _
        & vbCrLf & [blue]Me.FName & " " & Me.LName[/blue] & _
        & vbCrLf & "Do you want to add this name anyway?" _
        , vbYesNo Or vbQuestion Or vbDefaultButton2, "System Duplication Message")

    Case vbYes
        DoCmd.OpenForm strDocName, acPreview, , strWhere
    Case vbNo
        Me.Undo
        Cancel = True
End Select
 
End If
   
End Sub

Have fun.

---- Andy

 

[aaabuhalime]

I am getting Compiler Syntax Error, I think this is enough, I am might add city in thier as well, plz help on why I am geeting the syntax Error.
it highlights the Select Statment.

 

[Andrzejek]

Eliminate & sign between "Me.LName" and "_":
[tt]
& vbCrLf & Me.FName & " " & Me.LName & _
[/tt]


Have fun.

---- Andy