Check for duplicate records after inputing a value in the "last field".

Hi,
Fire this query in Zipcode textbox's validate event.

Dim strSQL As String
strSQL = "SELECT COUNT(1) As Cnt FROM GUEST WHERE " & _
            " [Guest First] = '" & txtGuestFName.Text & "'" & _
            " [Guest Last] = '" & txtGuestLName.Text & "'" & _
            " [Guest MI] = '" & txtGuestMI.Text & "'" & _
            " [Guest Address] = '" & txtGuestAdd.Text & "'" & _
            " [Guest Phone] = '" & txtGuestPhone.Text & "'"

If the record count (Cnt) is zero that means that the record not found open the next form in new mode and if the record count (Cnt) is greater than 0 then show the selection form and proceed with the new entry form.
Cheers!

Thank you for your response - but I'm not sure what "Zipcode textbook" is... can you clarify for me?

Thanks

Rob

In practice, instead of coding to check for duplicates its easier to make the fields you don't want dupes index fields and flag them as unique.

Then when you try to insert simply trap errors and disallow the add if it causes an error because of key violations.

less code means, less coding, less to break and less to maintain.

hi  rballheim

it s actually 'Zipcode Textbox', the text box which will contain the zipcode..as mentioned by KarcOrigin.

Ok, since we took our first stab at this, I have found another snippet of code that I would like you all to take a look at.

Again - here's the problem I'm having.

I have a table "tblGuest" that has the following columns
[FirstName]
[LastName]
[Zip]
[HomePhone]

I want to check values from the bound form with txt fields with the same names for duplicate records - but this time I just want to put the names of possible duplicates in a message box to the user and ask if this name would be a duplicate.

I'm trying the code below but getting the following error:
"Compile error: Sub or function not defined" and the debugger highlights the 2nd SOUNDEX
--------------------------------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
    If (Me.NewRecord = True) Then
        If Not IsNull(Me.LastName) Then
            Set rst = CurrentDb.OpenRecordset("Select LastName, First Name FROM " & _
                "tblGuest WHERE Soundex([LastName],[FirstName]) = '" & _
                Soundex(Me.LastName), (Me.FirstName) & "'")
            Do Until rst.EOF
                strNames = strNames & rst!LastName & ", " & rstFirstName & vbCrLf
                rst.MoveNext
            Loop
            rst.Close
            Set rst = Nothing
                If Len(strNames) > 0 Then
                    If vbNo = MsgBox("HRD Perfect stay found guests with similar " & _
                    "names already in the guest database: " & vbCrLf & vbCrLf & _
                    strNames & vbCrLf & _
                    "Is the guest or entering a duplicate record?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
                    Cancel = True
                End If
            End If
        End If
    End If
   
End Sub

Thank you in advance for a detailed response.

Rob

hi rballheim

probably the line 'Soundex(Me.LastName), (Me.FirstName)' should have been like this

Soundex((Me.LastName), (Me.FirstName))
 try doing this and check if ur still getting the error

ANV, thanks for the help. I corrected the statement per your suggestion but now I'm getting the following error.

'Complie Error: Sub or function does not exist.'

And the debugger is now highlighting  .LastName after the second soundex...

I'm sure this is somethign basic that I'mmissing just because I'm such a newby - and I - again- appreciate your help in advance.

Rob

Code:
----------------------------------------------------------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As DAO.Recordset, strNames As String
   If (Me.NewRecord = True) Then
        If Not IsNull(Me.LastName) And Not IsNull(Me.FirstName) Then
            Set rst = CurrentDb.OpenRecordset("Select FirstName, LastName FROM " & _
                "tblGuest WHERE Soundex([FirstName],[LastName]) = '" & _
                Soundex((Me.FirstName), (Me.LastName)) & "'")
            Do Until rst.EOF
                strNames = strNames & rst!LastName & ", " & rstFirstName & vbCrLf
                rst.MoveNext
            Loop
            rst.Close
            Set rst = Nothing
                If Len(strNames) > 0 Then
                    If vbNo = MsgBox("HRD Perfect stay found guests with similar " & _
                      "names already in the guest database: " & vbCrLf & vbCrLf & _
                    strNames & vbCrLf & _
                    "Is the guest or entering a duplicate record?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
                    Cancel = True
                End If
            End If
        End If
    End If
   
End Sub

The reason you are getting this error is because SoundEx is not a visual basic function
but a databas function.

Try re-writing your code like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As DAO.Recordset, strNames As String
   If (Me.NewRecord = True) Then
        If Not IsNull(Me.LastName) And Not IsNull(Me.FirstName) Then
            Set rst = CurrentDb.OpenRecordset("Select FirstName, LastName FROM " & _
                "tblGuest WHERE Soundex([FirstName],[LastName]) = " & _      ' Don't open quote as using DB function
                "Soundex(" & me.FirstName & "," & me.LastName & "))"          

' This way you are supplying the data in me.firstname and me.lastname to the DB soundex function
' and should eliminate your problem.

            Do Until rst.EOF
                strNames = strNames & rst!LastName & ", " & rstFirstName & vbCrLf
                rst.MoveNext
            Loop
            rst.Close
            Set rst = Nothing
                If Len(strNames) > 0 Then
                    If vbNo = MsgBox("HRD Perfect stay found guests with similar " & _
                      "names already in the guest database: " & vbCrLf & vbCrLf & _
                    strNames & vbCrLf & _
                    "Is the guest or entering a duplicate record?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
                    Cancel = True
                End If
            End If
        End If
    End If
   
End Sub

Gruff,

Ok - did what you recommended and now I am getting an error
'Syntax error (comma) in query expression '([FirstName],[LastName])
=Soundex('Paul,McAlister')'.

----------------------------------------------------------------------
Code exatly as is- THANKS in advance
----------------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As DAO.Recordset, strNames As String
   If (Me.NewRecord = True) Then
        If Not IsNull(Me.LastName) And Not IsNull(Me.FirstName) Then
            Set rst = CurrentDb.OpenRecordset("Select FirstName,LastName FROM " & _
                "tblGuest WHERE([FirstName],[LastName]) = " & _
                "Soundex('" & Me.FirstName & "," & Me.LastName & "')")
            Do Until rst.EOF
                strNames = strNames & rst!FirstName & "," & rst!LastName & vbCrLf
                rst.MoveNext
            Loop
            rst.Close
            Set rst = Nothing
                If Len(strNames) > 0 Then
                    If vbNo = MsgBox("HRD Perfect stay found guests with similar " & _
                      "names already in the guest database: " & vbCrLf & vbCrLf & _
                    strNames & vbCrLf & _
                    "Is the guest or entering a duplicate record?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
                    Cancel = True
                End If
           End If
        End If
   End If
   
End Sub

rballheim

try:

"WHERE [FirstName] = Soundex('" & me.FirstName & "') AND
[LastName] = Soundex('" & Me.LastName & "')"

I've hit that often you can also try replaceing the single quote apostrophys around the name with

double quotes inserted into contructed command string like this

    & chr(34) & 

or

single quotes inserted into contructed command string like this

   & chr$(39) & 

This can be a bit tricky but worth learning how to do if you plan to work with VBA constructed SQL strings.

being a newby - I assigned points for the solution that started me out on the right path - although no one really nailed "Soundex" which I found out doesnt work at all with this code in any fashion.

Again the problem was to find duplicates in the "Guest" table on [FirstName] and [LastName] after exiting the last name field - and return a message box with all the records that were the same.

Thank you everyone for your assistance - this one wasn't easy in my mind and you all were a great help.

This was the code that finally worked
----------------------------------------------
Private Sub LastName_LostFocus()

Dim rst As DAO.Recordset, strNames As String
   If (Me.NewRecord = True) Then
        If Not IsNull(Me.LastName) And Not IsNull(Me.FirstName) Then
            Set rst = CurrentDb.OpenRecordset("Select FirstName, LastName, MI, CompanyName, GoldPassport, Address, " & _
                " City, StateOrProvince, ZipPostalCode, HomePhoneNumber, WorkPhoneNumber, EmailAddress FROM " & _
                "tblGuest WHERE [FirstName] = ('" & Me.FirstName & "') AND [LastName] = ('" & Me.LastName & "')")
                Do Until rst.EOF
                strNames = strNames & rst!FirstName & " " & rst!LastName & ", " & rst!MI & "  " & rst!GoldPassport & "  " & rst!CompanyName & "    " & rst!Address & " - " & rst!City & ", " & rst!StateOrProvince & ". " & rst!ZipPostalCode & "  " & rst!HomePhoneNumber & "  " & rst!WorkPhoneNumber & "      EMail: " & rst!EmailAddress & vbCrLf
                rst.MoveNext
            Loop
            rst.Close
            Set rst = Nothing
                If Len(strNames) > 0 Then
                    If vbYes = MsgBox("HRD Perfect stay found guests with similar " & _
                      "names already in the guest database: " & vbCrLf & vbCrLf & _
                    strNames & vbCrLf & _
                    "Is the guest you are entering a duplicate?", _
                    vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
                    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
                    Me.FirstName.SetFocus
                    End If
                End If
            End If
        End If
End Sub