Hi John Thanks for getting back. I just remembered, I commentted it out because i was getting a runtime error 3071, The expression is typed incorrectly or too complex to be evaulated?
Thanks Joe --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Why did you comment out the rst.Movenext? That is what is causing your > unending loop. > > 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: Monday, December 12, 2005 9:01 AM > To: [email protected] > Subject: [ms_access] Re: ? on Checking Duplicates.. > > 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 Links > ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/KIlPFB/vlQLAA/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/
