Apostrophe Causing Run Time Error'!

[Debased]

I have a form based on a table. Form has unbound combo box set to find record. It works fine
except for those names with an apostrophe like "Women's Center" (quotes mine)

I discovered this just by process of elimination. The error description didn't help a bit.
If I delete the apostrophe, it works fine.
Is there anyway to have names using apostrophe's and have it function properly?

Here's the error: Run-time error '3077': Syntax error (missing operator) in expression.


Thanks

 

[Kodo]

if you're passing text to a query that contains an apostrophe you must replace it.

mytext="Jim's car"

Mytext=replace(mytext,"'","''")

then pass the variable to the query.

 

[Debased]

Thanks for the reply. I am just using the following code that was generated by the wizard for the combo box. Not being advanced coder I am not sure where to place your reply code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Lastname] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Kodo]

Try

Set rs = Me.Recordset.Clone
Dim MyLastName as string

MyLastName=me.combo31
MyLastName=replace(MyLastName,"'","''")

rs.FindFirst "[Lastname] = '" & MyLastName & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Debased]

Thanks Kodo...I will try it this weekend...gotta be gone now. Have a great weekend!

 

[Kodo]

6 of one half dozen of the other.

 

[Kodo]

maybe I'm missing something here, but isn't he passing that to a query?

the replace function I use is a well known function throughout the programming community. I use it regularly in my ASP apps with out failure of any kind.

 

[Kodo]

The apostrophe is the escape for SQL.

You're not looking at the total parsing of SQL statement.

If I use the replace function on all ' inside the variable then SQL will see two apostrophes together and treat them as a single apostrophe . It also understands that this doesn't signify the end of the string.

 

[Kodo]

Sorry for the confusion Pat. I should have made it more clear.

 

[Debased]

Thanks for your help!

Just now getting back to work.. I used your suggestion Kodo and it works great! Thanks again!

 

[Kodo]

you're quite welcome