Hi John, After spending sometime with your routine, and "paying attention to detail" The listbox works really nice!!
Thanks for all of your help Joe Of course I have more questions.. But I'll start a new thread.... --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Joe- > > Helps to pay attention to details. Where's the NZ and Soundex? Like this: > > SELECT Clientlookup.ClientID, Clientlookup.FirstName, > Clientlookup.LastName, Clientlookup.SSN, Clientlookup.DOB > FROM Clientlookup > WHERE Soundex(NZ(Clientlookup.LastName, ' '))='" & > Soundex(Forms!mrmhClientForm! > LastName) & "'; > > 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 16, 2005 5:03 PM > To: [email protected] > Subject: [ms_access] Re: ? on Using John V's Soundex ?? > > Hi John, > I'm getting closer.. > I put this statement of the dataentry form(mrmhclientform): > If vbNo Then > DoCmd.OpenForm "FORM2", acNormal, , , , acDialog > it opens form2 but I get no data: > The RowSource looks like: > SELECT Clientlookup.ClientID, Clientlookup.FirstName, > Clientlookup.LastName, Clientlookup.SSN, Clientlookup.DOB > FROM Clientlookup > WHERE (((Clientlookup.LastName)='" & Soundex(Forms!mrmhClientForm! > LastName) & "')); > > I get noithing back in the listbox? > > Am I suppossed to pass a value in the Docmd? > > > Thanks > Joe > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > Joe- > > > > Set the Row Source of the List Box to: > > > > SELECT LastName, FirstName, ssn, location,ID FROM clientlookup WHERE > > Soundex(NZ([LastName], ' ')) = '" & Soundex(Forms!ClientForm! > LastName) & "'" > > > > > > .. where "ClientForm" is the name of the original data entry form > that is > > doing the validation check. > > > > 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 16, 2005 9:56 AM > > To: [email protected] > > Subject: [ms_access] Re: ? on Using John V's Soundex ?? > > > > Hi John, > > I "sort of" understand... > > What I am confused on is the listbox source. > > I have the query on the add client form, but what I'd like is the > > same data in the listbox as the msgbox> Just not sure what to use > to > > get that data. > > > > Thanks in Advance > > > > Joe > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > > > > Joe- > > > > > > Well, you already have the query - the SQL you opened with > > OpenRecordset. > > > You could open a dialog form that has a list box on it that uses > > that SQl as > > > the Row Source for a list box. You need to open the form > > > WindowMode:=acDialog to halt this code until the window closes. > > Code in the > > > dialog form could open another form to review the selected record > > and > > > possibly set a Public variable to tell your BeforeUpdate code > > whether to > > > cancel the update or not. > > > > > > 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 15, 2005 3:59 PM > > > To: [email protected] > > > Subject: [ms_access] ? on Using John V's Soundex ?? > > > > > > Hi, > > > > > > This question is really to John V. since this is his code.. > > > John, > > > after going thru the code, and it works great, I've been trying > to > > > figure out a way to change the msgbox to a listbox, is this > > possible? > > > Here is my reasoning, I changed: > > > Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName, > ssn, > > > location,ID FROM " & _ > > > "clientlookup WHERE Soundex(NZ([LastName], ' ')) = '" & _ > > > Soundex(Me.LastName) & "'") > > > > > > to use query clientlookup, > > > if the listbox would popup, then a person could choose a client > > from > > > the list and another form would open verifing the client and/or > > > changing the clients location . > > > > > > I think in my case the listbox would work out better. > > > If a listbox is not possible, is there a way to pass the soundex > to > > a > > > query showing the names? > > > > > > Thanks > > > Joe > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > Yahoo! Groups Links > ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income homes are not online. Make a difference this holiday season! http://us.click.yahoo.com/5UeCyC/BWHMAA/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/
