 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2006Combo box After Update chokes on apostrophes in the selected field
I've got a combo box in a form used to "find a record on my form based on a record I select in the combo box." I created this control with the Combo Box Wizard, and the desired record is selected with the line rs.FindFirst "[Applicant] = '" & Me![cboSelectApplicant2] & "'" The Bound Control is a text field, and it works fine until I try to select a string that has a single apostrophe in it, at which time I get the error message Run-time error '3077': Syntax error (missing operator) in expression. Is there anything I can do to overcome this problem? Thanks in advance, Paul Use the Replace function to double up the ' characters. Two ' characters in a row is interpreted as a single ' character in a text string. rs.FindFirst "[Applicant] = '" & Replace(Me![cboSelectApplicant2], "'", "''", 1, -1, vbText) & "'"  SignatureKen Snell <MS ACCESS MVP> > I've got a combo box in a form used to "find a record on my form based on > a record I select in the combo box." [quoted text clipped - 16 lines] > > Paul Thanks for the code, Ken. (Wow, only 5 minutes after I posted the question). However, I'm getting a Compile Error on the "vbText" argument. It says "Variable not defined." I'm using Access 2002. Any ideas? Paul > Use the Replace function to double up the ' characters. Two ' characters > in a row is interpreted as a single ' character in a text string. [quoted text clipped - 22 lines] >> >> Paul Well, one way to solve the problem is to use an Autonumber field as the bound column for the combo box, and then I don't have to handle apostrophes in the text string. Thanks for your suggestion in any event, Ken. > Thanks for the code, Ken. (Wow, only 5 minutes after I posted the > question). [quoted text clipped - 32 lines] >>> >>> Paul Sorry... memory lapse... Use vbTextCompare (it's value is 1). SignatureKen Snell <MS ACCESS MVP> > Thanks for the code, Ken. (Wow, only 5 minutes after I posted the > question). [quoted text clipped - 32 lines] >>> >>> Paul Ah! That did the trick. Thanks. Looking at the expression now, I have a question about it. I would have thought that you need to put the Replace() function on both sides of the equality for it to work. You have pointed out that two ' characters in a row is interpreted as a single ' character in a text string, but I would have thought that would apply to both sides of the equal sign. Why is it only necessary to use it on the one side Are you asking why you don't need to use the Replace function with the Applicant field name on the left side of the equality? That is because it is just the name of the field that contains the values that are to be searched, so there is no need for any modification of its contents/values. Also, VBA will not allow you to put functions, etc. on the left side of an equality, as the syntax is that you're setting the value of the left side to the result of some expression on the right side. SignatureKen Snell <MS ACCESS MVP> > Ah! That did the trick. > [quoted text clipped - 6 lines] > have thought that would apply to both sides of the equal sign. Why is it > only necessary to use it on the one side see answer in forms newsgroup. SignatureKen Snell <MS ACCESS MVP> > I've got a combo box in a form used to "find a record on my form based on > a record I select in the combo box." [quoted text clipped - 16 lines] > > Paul I'm having the same problem with the Error 3077 and I tried your fix. I'm not getting the error now, but it's not finding the record. Can you see what I'm doing wrong ... Private Sub Combo114_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[FileName] = '" & Replace(Me![Combo114], "'", "''", 1, -1, vbTextCompare) & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Thanks. > see answer in forms newsgroup. > [quoted text clipped - 18 lines] > > > > Paul |
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.