Mekenzie Buhr Mekenzie Buhr - 4 months ago 18
SQL Question

Insert into statement with an apostophe using VBA?

I have a form with textboxes. I am inserting what the user enters into the textbox into a table. If the user enters an apostrophe in the textbox labeled "Me.ProjectName", I get an error. My code is:

CurrentDb.Execute "INSERT INTO Table1(ProjectNumber, Title) " & _
" VALUES('" & ProjectNumber & "','" & Me.ProjectName & "')"

Answer

You should escape your strings possibly containing quotes by replacing a quote with 2 quotes:

  Dim SQL As String

  SQL = "INSERT INTO Table1(ProjectNumber, Title) " & _
    " VALUES('" & ProjectNumber & "','" & Replace(Me.ProjectName, "'", "''")  & "')"

  CurrentDb.Execute SQL