Error 3075 Syntax error (missing operator) 2

[THWatson]

The following code is supposed to enable to user to add new items to a combo box. It worked fine in Access 2007, but results in an error in Access 2010.

The offer is made to add a new item, but then when you attempt to do so a message comes "Error 3075 (Syntax error (missing operator) in query expression). I assume it's in the line
strSql = "Insert Into tblAwards ([Award]) values ('" & NewData & "')"

Can anyone advise how to rectify this? Thanks!

Private Sub Award_NotInList(NewData As String, Response As Integer)
Dim strSql As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
   On Error GoTo Award_NotInList_Error

   On Error GoTo cboIns_NotInList_Error

If NewData = "" Then Exit Sub

Msg = """" & NewData & """ is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add this Award?"


i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Name...")
    If i = vbYes Then
      strSql = "Insert Into tblAwards ([Award]) values ('" & NewData & "')"
           DoCmd.SetWarnings False
           CurrentDb.Execute strSql
            Response = acDataErrAdded
           DoCmd.SetWarnings True
        Else
            Response = acDataErrContinue
    End If

   On Error GoTo 0
   Exit Sub

cboIns_NotInList_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboIns_NotInList of VBA Document Form_frmMembers"

   On Error GoTo 0
   Exit Sub

Award_NotInList_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Award_NotInList of VBA Document Form_fsubAwardsGiven"
End Sub

 

[dhookom]

What do you see when you debug your code? Is Award text or numeric? What is the value of NewData when the code fails?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Nothing shows up when I debug the code.

The award is Text. The attempt is to add, through the Combo Box, an item that is not in the combo box list to an existing table called tblAwards.

The item trying to be added, i.e. the NewData is Governor's Pin.

I see that for some reason one time a value of "& New Data &" was added to the list. I removed that and now I get a different error #. The error now is Error 3134 (Syntax error in Insert Into statement...")

Tom

 

[dhookom]

So your value has an apostrophe in it? If so, try:

 strSql = "Insert Into tblAwards ([Award]) values (""" & NewData & """)"

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

I should add, though, that the error 3075 (missing operator) was with this line in the code:

strSql = "Insert Into tblAwards ([Award]) values ('" & NewData & "')"

The error 3134 occurs when I add the ampersand operator to the code, making it:

strSql = "Insert Into tblAwards ([Award]) values  & ('" & NewData & "')"

Tom

 

[THWatson]

Duane
Yep, that works. It was likely because of the apostrophe.

In tinkering around, I found that the following would also work:

strSql = "INSERT INTO tblAwards (Award) " & "Select """ & NewData & """;"

Thanks!!

Tom

 

[dhookom]

Did you try my suggestion?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Your tinkering code wastes two perfectly good quotes. You could potentially use the following but it would waste even more quotes.

strSql = "INSERT "  & "INTO"  & " tblAwards"  & " (Award) " & "Select """ & NewData & """;"

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Sure did. Worked like a charm. That's why I replied, "Yep, that works." And that's what I will use.

Thanks.

Tom

 

[THWatson]

Duane
Yep, I get your point.

To clarify, I will use your code that works beautifully:

strSql = "Insert Into tblAwards ([Award]) values (""" & NewData & """)"

And it is apparent that the apostrophe in the new Award was what was causing the problem, given the way the code line was originally constructed.

Tom

 

[PHV]

And the code will fail if NewData contains a double quote (try it) ...
A safe way:

strSql = "Insert Into tblAwards ([Award]) values ('" & Replace(NewData, "'", "''") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Right you are. Thanks.

Tom