 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2008Search help needed
Access 2003 search form frmSearchDonors will have 1 field "txtSearchPhones" it will also have one button that is meant to open a form with the donor record containing that phone number in any one of its 3 Phone fields (Lets assume for a minute that no phone number can have duplicate donors). Phone fields are: phone_1 phone_2 phone_3 This is what I have but it is not working Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmEditDonor" stLinkCriteria = "[phone_1]=" & "'" & Me![txtSearchPhones] & " ' " Or "[phone_2]=" & "'" & Me![txtSearchPhones] & "'" Or "[phone_3]=" & "'" & Me![txtSearchPhones] & " ' " DoCmd.OpenForm stDocName, , , stLinkCriteria willing to bet I got quote issues but I can't get it right Any help here will be appreciated. Thanks in advance dave Dave, try it like this "[phone_1]= '" & textWord & "'" & " Or [phone_2]='" & textWord & "'" & " Or [phone_3]='" & textWord & "'" Note how I have the Operator Or *inside the quotes Your version had the Operator Or *outside the quotes Jeanette Cunningham > Access 2003 > [quoted text clipped - 26 lines] > Thanks in advance > dave I knew it was a quote issue :) :) Thanks much dave > Dave, > try it like this [quoted text clipped - 39 lines] >> Thanks in advance >> dave OK - now I want to take it one step further and add a second potential search field: But this did not work - and this time I don't thik it is a quote error stLinkCriteria = "[phone_1]= '" & txtSearchPhones & "'" & " Or [phone_2]='" & txtSearchPhones & "'" & " Or [phone_3]='" & txtSearchPhones & " Or [last_name]='" & txtSearchLastName & "'" what have I done wrong? Thanks again dave > Access 2003 > [quoted text clipped - 26 lines] > Thanks in advance > dave Dave, the quotes look fine in this one, however the Me! in front of each textbox is missing stLinkCriteria = "[phone_1]= '" & Me![txtSearchPhones] & "'" & " Or [phone_2]='" & Me![txtSearchPhones] & "'" & " Or [phone_3]='" & Me![txtSearchPhones] & " Or [last_name]='" & Me![txtSearchLastName] & "'" If there is a possibility the any Last name might have an apostrophe in it, use "" & Me![txtSearchLastName] & """ expanded for clarity that is " " & Me![txtSearchLastName] & " " " Jeanette Cunningham > OK - now I want to take it one step further and add a second potential > search field: [quoted text clipped - 40 lines] >> Thanks in advance >> dave Jeanette, Thanks again for the reply but I am still doing something wrong. This is what I have (ignoring for a moment the possibility of an apostrophe in the last name) stLinkCriteria = "[phone_1]= '" & Me![txtSearchPhones] & "'" & " Or [phone_2]='" & Me![txtSearchPhones] & "'" & " Or [phone_3]='" & Me![txtSearchPhones] & " Or [last_name]='" & Me![txtSearchLastName] & "'" the Me![ ] did not seem to solve the issue dave > Dave, > the quotes look fine in this one, however the Me! in front of each textbox [quoted text clipped - 60 lines] >>> Thanks in advance >>> dave Dave, this small piece of code & "'" needs to be added between Me![txtSearchPhones] & " Or [last_name]= It is tricky to get it right - we all struggle with counting the quotes and matching them up. You can count them, a bit like counting If's and End If's. Add 1 for each quote in front of textbox reference and subtract 1 for each quote after a text box reference. If you get back to 0 at the end of each text box reference, you know you have paired them up correctly. Jeanette Cunningham > Jeanette, > [quoted text clipped - 75 lines] >>>> Thanks in advance >>>> dave Yep - that solved it. Thanks again. Now - if you want to hang with me one step further (I might not know much - but I do know to take coding, one step at a time)............. How should I deal with duplicates? Duplicate Last names Or Duplicate Phone numbers (maybe 2 room mates used the same phone) I am assuming instead of the search button taking me to the the form with the persons data I would first need to go to a "continuous form" That would list all the duplicates. I just can't get my head around how I would code that. Thanks very much - Again dave > Dave, > this small piece of code [quoted text clipped - 91 lines] >>>>> Thanks in advance >>>>> dave Dave, if this form allows data entry, you need to save the data before you allow them to open the next form. You would check for duplicates in the before update event of the form. This is where you can cancel the update if you find duplicates. To check for duplicates, use a DCount on the table where you store the last name. If DCount("[last_name]", "TheTable", "[last_name] = """ & me.txtSearchLastName & """") >0 then Cancel = True 'msgbox to tell user about the problem Else End if Be aware that you could have the situation where 2 different people do in fact have the same last name. If you do a search in the newsgroups on duplicate names, I am sure you will find many posts - this is a common problem. Use similar DCount code to check duplicates for the phone numbers. Jeanette Cunningham > Yep - that solved it. > Thanks again. [quoted text clipped - 113 lines] >>>>>> Thanks in advance >>>>>> dave Thanks again Jeanette dave > Dave, > if this form allows data entry, you need to save the data before you allow [quoted text clipped - 138 lines] >>>>>>> Thanks in advance >>>>>>> dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.