Solved

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

Posted on 2004-09-02
14
559 Views
Last Modified: 2013-12-25
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
Comment
Question by:rballheim
  • 5
  • 3
  • 2
  • +2
14 Comments
 
LVL 5

Expert Comment

by:KarcOrigin
ID: 11969722
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
 
LVL 10

Accepted Solution

by:
anv earned 500 total points
ID: 11970629
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
 

Author Comment

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

Thanks

Rob
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 3

Expert Comment

by:Informative
ID: 11998048
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
 
LVL 10

Expert Comment

by:anv
ID: 12003222
hi  rballheim

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

Author Comment

by:rballheim
ID: 12031288
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
 
LVL 10

Expert Comment

by:anv
ID: 12032565
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
 

Author Comment

by:rballheim
ID: 12035551
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
 
LVL 4

Expert Comment

by:Gruff82
ID: 12141894
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
 

Author Comment

by:rballheim
ID: 12264497
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
 
LVL 4

Expert Comment

by:Gruff82
ID: 12274436
rballheim

try:

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



0
 
LVL 3

Expert Comment

by:Informative
ID: 12276727
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
 

Author Comment

by:rballheim
ID: 12281199
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question