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 (ex. John's Cafe), syntax error 3075 appears.
Do you have any suggestions to correct the error?

[NeoPa]

Hi there, and welcome to Bytes.com.

Yes indeed. Interesting question :-)

This occurs because you're using the correct quote character for SQL strings. To avoid this being a problem you should always pass your string parameters - at least those that could ever contain such characters - via a filter function that doubles them up for you. For more on this see Quotes (') and Double-Quotes (") - Where and When to use them.

An example of such a function procedure is (from SQL Injection Attack) :

Public Function SafeSQL(strArg As String) As String
    SafeSQL = Replace(strArg, "'", "''")
End Function

Your line #5 would then be :

NewBusinessName = SafeSQL(Me.BusinessName.Value)

Note that, in most cases where no quotes are contained within the string passed, the value returned will be exactly the same as the original value. Only strings with embedded quotes will ever be changed to ensure they work correctly within SQL.

[NeoPa]

By the way, correct quotes often fall foul of this with names, but the incorrect ones (") - also allowed in Access & Jet/ACE - have a similar problem when dealing with measurements in inches (") or even seconds (") when dealing with parts of a degree as well as of an hour ;-) Thus it still makes sense to use the correct ones and just ensure you protect your code using this procedure.

[NeoPa]

Before I leave you I must just point out that, while it makes sense to catch such problems before trying to save the data, and thus end up with your user being subjected to a basic Access error message, it is also advisable to configure the underlying table field not to accept duplicates by setting the Indexed property to Yes (No duplicates). This will create an index on that field and disallow entry of any duplicate values.

[jameswalter]

"Runtime Error 3075: Syntax Error (missing operator) in query expression ID () and Link LIKE 'SET'"This error occurs when exporting a row format to a . tdb file.

This may help,
James