Duplicate Names

[hkimpact]

Hi everyone -

I have a form right now which has a First name and Last Name fields (PFirstName and PLastName). I just want to be able to determine if a name possibly exists in one of my tables. The following code that I have is not working yet. Not sure what I am missing.

I have 3880 records in my table and its setting RecordCount to 3880, which causes my messagebox to show the first person of the table and not a related record. Any ideas?

' Determine Duplicate Names
Private Sub txtLastName_LostFocus()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iAns As Integer
If Me.NewRecord Then ' Only check for new additions
    strSQL = "[PLastName] = """ & Me.txtLastName _
       & """ And [PFirstName] = """ & Me!txtFirstName & """"
    Set rs = Me.RecordsetClone  ' Get the form's recordset
    rs.FindFirst strSQL ' Find this person's name
 
  If rs.RecordCount > 0 Then
    iAns = MsgBox("There is a participant with the name " _
        & rs!PFirstName & " " & rs!PLastName & " in the " _
        & "database. Please make sure you aren't creating a " _
        & "duplicate participant!" & vbCrLf & vbCrLf _
        & "Select OK to add record anyway or CANCEL to erase " _
        & "and start over:", vbOKCancel)
 
    Select Case iAns
        Case vbOK
            ' Do Nothing
        Case vbCancel
            ' Cancel Record
            Cancel = True
            Me.Undo
    End Select
  End If
End If
 
End Sub

 

[thechazm]

Instead of what you currently have try this. Changes in red

' Determine Duplicate Names
Private Sub txtLastName_LostFocus()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iAns As Integer
If Me.NewRecord Then ' Only check for new additions
    strSQL = "[PLastName] = """ & Me.txtLastName _
       & """ And [PFirstName] = """ & Me!txtFirstName & """"
    Set rs = Me.RecordsetClone  ' Get the form's recordset
    rs.FindFirst strSQL ' Find this person's name
 
  [COLOR=red]if rs.nomatch = false Then[/COLOR]
    iAns = MsgBox("There is a participant with the name " _
        & rs!PFirstName & " " & rs!PLastName & " in the " _
        & "database. Please make sure you aren't creating a " _
        & "duplicate participant!" & vbCrLf & vbCrLf _
        & "Select OK to add record anyway or CANCEL to erase " _
        & "and start over:", vbOKCancel)
 
    Select Case iAns
        Case vbOK
            ' Do Nothing
        Case vbCancel
            ' Cancel Record
            Cancel = True
            Me.Undo
    End Select
  End If
End If
 
End Sub

 

[hkimpact]

I knew it was something to do with that IF statement! I just ran and tested it and it worked like a champ.

Thank you!!

My next step is to display all users with that name, their DOB, and their SS#, and Address. You may be asking why a user just doesn't do a search by SS# within my DB. Well they have the option, but not all participants have a Social when the data is being entered. Life would be easier if that were the case haha.

 

[thechazm]

No worries and good luck