Problems with ' 1

[welshone]

hello,
I am using a combo box to select data on a form but if the value has a ' in it, then the code fails saying missing operator.

how can I get round this ?

Private Sub Combo82_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![Combo82] & "'"
Me.Bookmark = rs.Bookmark
End Sub

is the code.

thanks for your help.

 

[Rolliee]

Somewhere your ACCESS setup has a list of delimiters and in all probabliity, " ' " is on that list. I cannot think of a good 'work- around' for this. I hope someone else does and posts it.


Rollie E

 

[welshone]

cheers for the info rollie, however I've thought about it and its probably best t olink the combo box with a number rather than a string. I have changed my combo box to link to the primary key id now and all is fine.

thanks .

 

[Rolliee]

thanks, a better way to thank is to click the 'this post was helpful just above this post.

 

[jdegeorge]

welshone

I'd suggest stripping out the single quotes from the combo box data if you can. I always do that, as well as strip out commas from addresses and other fields.

Jim DeGeorge

 

[Maquis]

Ok, I'll jump into this one. Jim is right, if possible strip the single quotes out of the data. However, I've also run into this problem many times with Last Name fields like "O'Brien" and sometimes stripping out the quotes just isn't practical.

So, to answer the original question, here's the code I use to get around it.

Private Sub Combo82_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Const dquote = """" 'yep that's 4 double quotes.

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = " & & dquote & Me![Combo82] & dquote
Me.Bookmark = rs.Bookmark
End Sub



Maq
<insert witty signature here>

 

[welshone]

Nice one Mac, I'll have to save that code into my code directory to use on other databases !.
no doubt one day I'll need to compare strings instead of using ints. !
cheers.
J

 

[Stewartp]

I have had similar problems in the past - Double quote syndrome! Marquis's solution is very elegant and I use this method myself. So far it works every time for this particular problem. Here's a little additional info you might find useful.....

I also use the Instr funtion to determine if the value has a ' as a first or last character when passing values to a SQL string and strip them out if appropriate. This is because when you construct a long SQL string, debuging it with loads of &quot;&quot;&quot;&quot; is a nightmare!

Happy programming

 

[Imhoteb]

Another method to avoid this problem is the following

rs.FindFirst &quot;[Name] = '&quot; & Replace(Me![Combo82],&quot;'&quot;,&quot;''&quot & &quot;'&quot;