Joe- I'm baffled. Can you compact and zip your database and post it to the Files section?
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 3:25 PM To: [email protected] Subject: [ms_access] Re: ? on Checking Duplicates.. Hi John, I tried compiling w/ no errors, did a compact & repair still getting the runtime error 3071, The expression is typed incorrectly or too complex to be evaulated? on the rst.Movenext command. Just so you know what I've done. I only changed the table name to client in : Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName FROM " & _ "client WHERE Soundex([LastName]) = '" & _ and added the modUtility to modules Am I supposed to add anything else? Thanks Joe --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Joe- > > There's nothing wrong with that line of code - in fact, you need it! > Uncomment it, compile your project (choose Compile from the Debug menu) - to > be sure there are no other code problems in your database, and try running > it again. If you get the error again, click Debug on the error message to > find the line of code causing the problem. > > 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 12:46 PM > To: [email protected] > Subject: [ms_access] Re: ? on Checking Duplicates.. > > 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 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/
