 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / April 2008Find Command
I have a find button that I have set up to look in a particular text box. My problem with it is that It is always set to find the whole part of the box while I want it to look for any part of it. ie: I want to find a customer but am unsure as to how it is entered exactly in the form. Jenn, When I do this, I generally have an unbound textbox (txt_Find) in the forms header, and a command button (cmd_Find) right next to it. Assuming you are looking for a last name in the field [Last_Name], then my code might look like: Private sub cmd_Find_Click Dim strCriteria as string Dim rs as dao.recordset strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'" Dim rs as me.recordsetclone rs.findfirst strCriteria if rs.nomatch then msgbox "Could not find a last name similar to '" & me.txt_Find & "'" else me.bookmark = rs.bookmark endif rs.close set rs = nothing end sub You might want to add a find next in there too, so that you could search for another instance of the string (especially if the field you are searching on is not the sort order of the form). HTH Dale  SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > I have a find button that I have set up to look in a particular text box. My > problem with it is that It is always set to find the whole part of the box > while I want it to look for any part of it. ie: I want to find a customer but > am unsure as to how it is entered exactly in the form. Where would I put the find next > Jenn, > [quoted text clipped - 33 lines] > > while I want it to look for any part of it. ie: I want to find a customer but > > am unsure as to how it is entered exactly in the form. The easiest way would probably be to start out with code in the Change event of txt_Find, something like: Private Sub txt_Find_Change me.cmd_Find.Caption = "&Find" End Sub Then, modify the cmd_Find code to look like (this is untested): Private sub cmd_Find_Click Dim strCriteria as string Dim rs as dao.recordset strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'" Dim rs as me.recordsetclone if me.cmd_Find.Caption = "&Find" Then me.cmd_Find.Caption = "&Find Next" rs.findfirst strCriteria else rs.FindNext strCriteria endif if rs.nomatch then msgbox "Could not find a last name similar to '" & me.txt_Find & "'" else me.bookmark = rs.bookmark endif rs.close set rs = nothing end sub SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > Where would I put the find next > [quoted text clipped - 35 lines] > > > while I want it to look for any part of it. ie: I want to find a customer but > > > am unsure as to how it is entered exactly in the form. Good, Dale, but one problem. You don't want to use the Change event. It means what it says. It fires everytime you make any change to the value of the control which means it will fire after every keystroke. It really should be the After Update event and the test to change it should be after you do the find. Actually, now that I think about it, I would do it all in the Click event event: Private sub cmd_Find_Click Dim strCriteria As String strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*""" With Me.RecordsetClone If Me.cmd_Find.Caption = "&Find" Then me.cmd_Find.Caption = "&Find Next" .findfirst strCriteria If .NoMatch Then Msgbox "Could not find a last name similar to '" & me.txt_Find Else Me.Bookmark = .Bookmark Me.cmd_Find.Caption = "&Find Next" End If Else .FindNext strCriteria If .NoMatch Then Msgbox "No More Last Names Matching '" & me.txt_Find Me.cmd_Find.Caption = "&Find" End If End If End Sub And, to keep the Find/FindNext displaying correctly, put Me.cmd_Find.Caption = "&Find" in the form Current event and in the Got Focus event of txt_Find SignatureDave Hargis, Microsoft Access MVP > The easiest way would probably be to start out with code in the Change event > of txt_Find, something like: [quoted text clipped - 72 lines] > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > am unsure as to how it is entered exactly in the form. I got a compile error comming up at the Me.txt_Find with the .txt_find highlighted I can't figure out what it needs to be changed to. > Good, Dale, but one problem. > You don't want to use the Change event. It means what it says. It fires [quoted text clipped - 107 lines] > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > am unsure as to how it is entered exactly in the form. Do you have a control on your form named txt_Find? It should be the name of the text box control you want to do the search on. When asking about an error, it helps to point out the line and include the error number or message. SignatureDave Hargis, Microsoft Access MVP > I got a compile error comming up at the Me.txt_Find with the .txt_find > highlighted I can't figure out what it needs to be changed to. [quoted text clipped - 110 lines] > > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > > am unsure as to how it is entered exactly in the form. I also want to look up a number on a different form. I think this may be why I got the compile error. how do I change that to look for a number value? > Good, Dale, but one problem. > You don't want to use the Change event. It means what it says. It fires [quoted text clipped - 107 lines] > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > am unsure as to how it is entered exactly in the form. You would need to duplicate the code in the other form. To use a number, you need to change the syntax a bit Private sub cmd_Find_Click Dim strCriteria As String strCriteria = "[Number Field] = " & me.txt_Find With Me.RecordsetClone If Me.cmd_Find.Caption = "&Find" Then me.cmd_Find.Caption = "&Find Next" .findfirst strCriteria If .NoMatch Then Msgbox "Could not find a last name similar to '" & me.txt_Find Else Me.Bookmark = .Bookmark Me.cmd_Find.Caption = "&Find Next" End If Else .FindNext strCriteria If .NoMatch Then Msgbox "No More Last Names Matching '" & me.txt_Find Me.cmd_Find.Caption = "&Find" End If End If End Sub SignatureDave Hargis, Microsoft Access MVP > I also want to look up a number on a different form. I think this may be why > I got the compile error. how do I change that to look for a number value? [quoted text clipped - 110 lines] > > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > > am unsure as to how it is entered exactly in the form. Dave, Actually, I like the idea of changing it back to '&Find' in the GotFocus event of txt_Find; that makes more sense than the Change event. But if you do that in the forms Current event as well, won't that change it back to '&Find' every time you find a match? In which case, you would never get a '&FindNext', would you? SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > Good, Dale, but one problem. > You don't want to use the Change event. It means what it says. It fires [quoted text clipped - 107 lines] > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > am unsure as to how it is entered exactly in the form. You are correct. I forgot it is changing records. It should only do it in the text box Got Focus event. Good catch SignatureDave Hargis, Microsoft Access MVP > Dave, > [quoted text clipped - 115 lines] > > > > > > while I want it to look for any part of it. ie: I want to find a customer but > > > > > > am unsure as to how it is entered exactly in the form. |
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.