Syntax error 3075 when typing name with apostrophe in form

[eseng]

I am using this vba code to prevent double entry:
Private Sub BusinessName_AfterUpdate()
Dim NewBusinessName As String


Dim stlinkcriteria As String


NewBusinessName = Me.BusinessName.Value
stlinkcriteria = "[BusinessName] = " & "'" & NewBusinessName & "'"
If Me.BusinessName = DLookup("[BusinessName]", "Sheet1", stlinkcriteria) Then
MsgBox "" & NewBusinessName & " is already in the Database." _
& vbCr & vbCr & "Data Entry Denied!!!", vbInformation, "DUPLICATE ENTRY"
Me.Undo 'undo the process and clear all fields

The code works well to prevent double entry, unfortunately, if the name I typed includes an APOSTROPHE (like John's Cafe), syntax error 3075 appears.
Do you have any suggestions to correct the error?

[davegri]

Try

stlinkcriteria = "[BusinessName] = " & chr(34) & NewBusinessName & chr(34)

[Micron]

Those apostrophes will likely cause you problems from here on in - especially in code. For queries, one work around is to have a field with and a field without. Your criteria could be against the without field but all user need to know not to use them. Your forms/reports would show the other field. If you must use them then in code try concatenating with the ASCII character equivalent:

"John" & chr(39) & "s" for John's

[CJ_London]

use the replace function to replace a single quote with two single quotes

stlinkcriteria = "[BusinessName] = '" & replace(NewBusinessName,"'","''") & "'"

[ssanfu]

I had problems writing append and update queries when data had embedded single quotes.
Like Ajax, I used the replace function, but I wrote my own function

Public Function fnConvertQuotesSingle(InputVal)
   fnConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

Usage would be:

stlinkcriteria = "[BusinessName] = '" & fnConvertQuotesSingle(NewBusinessName) & "'"

[eseng]

Try

stlinkcriteria = "[BusinessName] = " & chr(34) & NewBusinessName & chr(34)

It worked! Thanks sir

[davegri]

Glad to help. I find this method SO much simpler than the confusing (single?, double?) quotes within quotes within more quotes.