MS Access: Entering Record With Apostrophe Produces Error

Question

I have an MS Access form for entering song performances. It looks up titles from a table. If the title isn't there, it asks if you want to add it.

Private Sub SongTitle_NotInList(NewData As String, Response As Integer)

10        On Error GoTo SongTitle_NotInList_Error
      Dim strSQL1 As String
          Dim i As Integer
          Dim Msg As String

          'Exit this sub if the combo box is cleared
20        If NewData = "" Then Exit Sub

30        Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
40        Msg = Msg & "Do you want to add it?"

50        i = MsgBox(Msg, vbQuestion + vbYesNo, "This is a new Song Title...")
60        If i = vbYes Then
70                strSQL1 = "Insert Into tblClick ([SongTitle]) " & _
                       "values ('" & NewData & "');"

80            CurrentDb.Execute strSQL1, dbFailOnError
90            Response = acDataErrAdded
                 
100       End If

          
110       On Error GoTo 0
120       Exit Sub

SongTitle_NotInList_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SongTitle_NotInList, line " & Erl & "."

End Sub

Unfortunately, I created that a long time ago and I don't really remember what any of the code does anymore.

It works just fine, except if the title has an apostrophe ("We're Going On", for instance).

Error 3075 (Syntax error (missing operator) in query expression ''We're');'.) in procedure SongTitle_NotInList, line 80.

I'm sure it's something simple, like escaping the apostrophe or putting it double-quotes. I've forgotten pretty much everything I learned about VB, over the years.

@jmcilhinney I see the tag changed to VBA. I didn't realize that they were different. Thanks for the clarification. — doubleJ
– doubleJ, Commented Aug 29 at 14:29

BobS · Accepted Answer · 2025-08-28 22:09:08Z

1

Change your strSQL1 line so that it reads as follows:

70                strSQL1 = "Insert Into tblClick ([SongTitle]) " & _
                       "values ('" & Replace(NewData,"'","''") & "');"

answered Aug 28 at 22:09

BobS

8133 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

doubleJ Sep 15 at 18:18

That did the trick. I'm not sure if there are any more specific instances that it wouldn't fix, but I am able to add song titles with apostrophes, now. Is that replacing the apostrophe with an escaped apostrophe?

ValNik · Accepted Answer · 2025-08-28 22:42:34Z

You can use parametrized query.

See simple example for MS Access query with parameters;

    NewData = "Test' apostrophe"
    strSQL1 = "PARAMETERS NewData varchar;" _
            & "Insert Into tblClick ([SongTitle]) " _
            & " values (NewData);"
    With CurrentDb.CreateQueryDef("SongTitle_NotInList", strSQL1)
        .Parameters!NewData = NewData
        .Execute
    End With
    CurrentDb.QueryDefs.Delete ("SongTitle_NotInList")

Or with "permanent" query

Dim qdf As QueryDef
    strSQL1 = "PARAMETERS NewData varchar;" _
            & "Insert Into tblClick ([SongTitle]) " _
            & " values (NewData);"
    On Error Resume Next
    Set qdf = CurrentDb.QueryDefs("SongTitle_NotInList")
    If Err.Number = 3265 Then ' query not found
        Set qdf = CurrentDb.CreateQueryDef("SongTitle_NotInList", strSQL1)
    Else
        qdf.SQL = strSQL1
    End If
    On Error GoTo 0
    NewData = "Test'2 apostrophe"
    With qdf
        .Parameters!NewData = NewData
        .Execute
    End With

Collectives™ on Stack Overflow

MS Access: Entering Record With Apostrophe Produces Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related