Solved

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

Posted on 2004-09-02
14
536 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now