OpenForm with single quote in criteria

[matrekz42]

Hello there! It's been a long time since I've reached out to the community for assistance, I thought I had this pretty much figured out! But, I've ran into a problem where I cant get the following to work, because in the Invention_Desc, one of the descriptions has a single quote in the string.

DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & Me.[Invention_Idea] & " And [Design]='" & Me.[Design] & "'" & " And Invention_Desc = '" & Invention_Desc & "'"

Any recommendations would be awesome! I've been trying to figure this out for hours with no success.

Thank you in advance!

[NeoPa]

It's a good idea always to protect such code by using a function such as SQLSafe().

'SQLSafe()  returns a value where all single-quotes (') are doubled up.
'27/03/2019 Updated to handle Variants - especially for Null values.
Public Function SQLSafe(ByVal varIn As Variant) As Variant
    SQLSafe = varIn
    If varIn > "" Then SQLSafe = Replace(CStr(varIn), "'", "''")
End Function

Thus, this particular line of code would be :

DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & SQLSafe(Me.[Invention_Idea]) & " And [Design]='" & SQLSafe(Me.[Design]) & "'" & " And Invention_Desc = '" & SQLSafe(Me.Invention_Desc) & "'"

[matrekz42]

Hi Neo, good to hear from you. I still received the same error. The name has the following: customer's and hence the reason I get the error because of the '.

[NeoPa]

Hi Matrekz. You too.

You shouldn't if it's done properly so I wonder exactly how you've implemented the suggestion. Care to share some details?

[twinnyfo]

This is more of a workaround, rather than a solution, but you can replace the straight quote "'" with a smart quote "’".

This prevents the issue, but does not "resolve" it.

[matrekz42]

Hi Neo, I created a module and entered the function. I then replaced the docmd.openform with

DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & SQLSafe(Me.[Invention_Idea]) & " And [Design]='" & SQLSafe(Me.[Design]) & "'" & " And Invention_Desc = '" & SQLSafe(Me.Invention_Desc) & "'"

[matrekz42]

thank you twinnyfo, I changed this in the meantime to keep working. I would ultimately like to get the SQLSafe working, but this definitely helped.

[NeoPa]

I was sloppy in my earlier post and just made the necessary changes without checking the rest was ok. Try this version :

DoCmd.OpenForm "Inventions", , , "([Invention_Idea]='" & SQLSafe(Me.Invention_Idea) & "') AND ([Design]='" & SQLSafe(Me.Design) & "') AND ([Invention_Desc]='" & SQLSafe(Me.Invention_Desc) & "')"

Or try the way I would do it in my own code :

Dim strSQL As String
 
strSQL = "([Invention_Idea]='%II') AND " _
       & "([Design]='%D') AND " _
       & "([Invention_Desc]='%ID')"
strSQL = Replace(strSQL, "%II", SQLSafe(Me.Invention_Idea))
strSQL = Replace(strSQL, "%D", SQLSafe(Me.Design))
strSQL = Replace(strSQL, "%ID", SQLSafe(Me.Invention_Desc))
Call DoCmd.OpenForm(FormName:="Inventions", WhereCondition:=strSQL)

That makes it more legible as well as giving you the opportunity to see the resolved string before you run it when debugging.

As a general rule I'm not a great fan of using other types of quotes, either in the code or in the data. It can often get you past bumps in the road but you tend to hit them again later when you're going faster.