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 --------------------~--> 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/
