• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 613
  • Last Modified:

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

I'm using MS Access 2003 VB 6.3.

I have a "Guest" from that has basic guest information in it like
Guest First
Guest Last
Guest MI
Guest Address
Gest Zip
Guest Phone

all field are text fields that are bound to the corresponding attribute in the Guest table.

After the user leaves the "GuestZip" field, I need to check the  "GuestFirst", "GuestLast", " Guest Zip", and  "Guest Phone" values in the form against values in the table and if there is a record with the same value in those four fields - I need to have an selection box that comes back and tells the user "this guest already exists" and then gives the user the options of "Enter Guest Stay" or "Enter Guest Feedback" - and once the appropraite selection is pushed - opens the form in add record mode.

I'm a bit of a "newby" so a detailed response would be greatly appreciated.

thanks!
0
rballheim
Asked:
rballheim
  • 5
  • 3
  • 2
  • +2
1 Solution
 
KarcOriginCommented:
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!
0
 
anvCommented:
dim rs as adodb.recordset

lets assume ur connection is already set..

then
strSql = strSQL = "SELECT * FROM GUEST WHERE " & _
            " [Guest First] = '" & txtGuestFName.Text & "'" & _
            " [Guest Last] = '" & txtGuestLName.Text & "'" & _
            " [Guest MI] = '" & txtGuestMI.Text & "'" & _
            " [Guest Address] = '" & txtGuestAdd.Text & "'" & _
            " [Guest Phone] = '" & txtGuestPhone.Text & "'"
rs.open strsql,cn,adoDynamic

if  rs.eof then
  'that mean their is no record corresponding to the
'record entered by the user
else
  'a corresponding record has been found in the database
'  give the  here the option to
'of "Enter Guest Stay" or "Enter Guest Feedback"
'after the selected option open
'load ur form in add mode
end if

my question here is u want to open the same form in Add mode?? where u r handling the above check??
0
 
rballheimAuthor Commented:
Thank you for your response - but I'm not sure what "Zipcode textbook" is... can you clarify for me?

Thanks

Rob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
InformativeCommented:
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.
0
 
anvCommented:
hi  rballheim

it s actually 'Zipcode Textbox', the text box which will contain the zipcode..as mentioned by KarcOrigin.
0
 
rballheimAuthor Commented:
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
0
 
anvCommented:
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
0
 
rballheimAuthor Commented:
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
0
 
Gruff82Commented:
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
0
 
rballheimAuthor Commented:
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
0
 
Gruff82Commented:
rballheim

try:

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



0
 
InformativeCommented:
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.
0
 
rballheimAuthor Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now