Hi John,
Here is the BeforeUpdate Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, strNames As String
' If on a new row,
If (Me.NewRecord = True) Then
' Check for similar name
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName,
FirstName FROM " & _
"client WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
' If got some similar names, issue warning message
Do Until rst.EOF
strNames = strNames & rst!LastName & ", " & rst!
FirstName & vbCrLf
'rst.MoveNext
Loop
' Done with the recordset
rst.Close
Set rst = Nothing
' See if we got some similar names
If Len(strNames) > 0 Then
' Yup, issue warning
If vbNo = MsgBox("Client Database found client(s)
with similar " & _
"last names already saved in the database: " &
vbCrLf & vbCrLf & _
strNames & vbCrLf & "Are you sure this contact is
not a duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2,
gstrAppTitle) Then
' Cancel the save
Cancel = True
End If
End If
End If
End If
End Sub
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Joe-
>
> What does the rest of the code look like?
>
> John Viescas, author
> "Building Microsoft Access Applications"
> "Microsoft Office Access 2003 Inside Out"
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf
> Of eliotchs
> Sent: Friday, December 09, 2005 12:52 PM
> To: [email protected]
> Subject: [ms_access] Re: ? on Checking Duplicates..
>
> Hi John,
>
> I think, I'm stuck in a loop..
>
> I added the module, and changed the line:
> Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName
FROM "
> & _"client WHERE Soundex([LastName]) = '" & _
> Soundex(Me.LastName) & "'")
>
> 'client is the name of the table
> 'lastname and firstname are the names of the fields on my form also.
> It runs, but I get the hourglass neverending?
>
>
> Joe
>
>
> --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
> >
> > Joe-
> >
> > 1. You need the modUtility module from my database - that
contains
> the
> > IsNothing custom function.
> >
> > 2. Note that the sample code in the form BeforeUpdate event
> assembles a list
> > of possible duplicate names into a string variable and then
> displays the
> > list using MsgBox. You could also use the SQL in the search
query
> as the
> > Row Source of a List Box or the Record Source of a Continuous
Form
> to
> > display the list of potential duplicates.
> >
> > John Viescas, author
> > "Building Microsoft Access Applications"
> > "Microsoft Office Access 2003 Inside Out"
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> >
> >
> > -----Original Message-----
> > From: [email protected]
[mailto:[EMAIL PROTECTED]
> On Behalf
> > Of eliotchs
> > Sent: Friday, December 09, 2005 12:11 PM
> > To: [email protected]
> > Subject: [ms_access] Re: ? on Checking Duplicates..
> >
> > Thanks John,
> >
> > There's alot of nice code in there to look at!
> > 2 questions...
> > 1. I get a compile error on the ISNothing statement, am I missing
a
> > file?
> > 2. Is there a way to show a list of possible duplicates?
> >
> > Thanks
> > Joe
> >
> > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]>
wrote:
> > >
> > > Joe-
> > >
> > > Check out the BeforeUpdate event in frmContacts in this sample
> > database:
> > >
> > > ftp://ftp.viescas.com/Download/Contacts2000.zip
> > >
> > > It performs a check for possible duplicate name using SoundEx.
> > >
> > > John Viescas, author
> > > "Building Microsoft Access Applications"
> > > "Microsoft Office Access 2003 Inside Out"
> > > "Running Microsoft Access 2000"
> > > "SQL Queries for Mere Mortals"
> > > http://www.viescas.com/
> > >
> > >
> > > -----Original Message-----
> > > From: [email protected]
> [mailto:[EMAIL PROTECTED]
> > On Behalf
> > > Of eliotchs
> > > Sent: Thursday, December 08, 2005 9:17 AM
> > > To: [email protected]
> > > Subject: [ms_access] ? on Checking Duplicates..
> > >
> > > Hi,
> > >
> > > I'm looking for some samples for checking for duplicates.
> > > I have an SSN field set to be unique, but sometimes do not get
> the
> > SSN
> > > I'd like to setup a listbox for show up with client of the same
> > name so
> > > users cab check to see if they are the same person they are
> trying
> > to
> > > enter..
> > >
> > > Thanks In advance
> > > Joe
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
1.2 million kids a year are victims of human trafficking. Stop slavery.
http://us.click.yahoo.com/.QUssC/izNLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
