Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error 3075 Syntax error (missing operator) 2

Status
Not open for further replies.

THWatson

Technical User
Apr 25, 2000
2,601
CA
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!

Code:
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

 
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
 
I should add, though, that the error 3075 (missing operator) was with this line in the code:
Code:
strSql = "Insert Into tblAwards ([Award]) values ('" & NewData & "')"

The error 3134 occurs when I add the ampersand operator to the code, making it:
Code:
strSql = "Insert Into tblAwards ([Award]) values  & ('" & NewData & "')"

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

In tinkering around, I found that the following would also work:
Code:
strSql = "INSERT INTO tblAwards (Award) " & "Select """ & NewData & """;"

Thanks!!

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

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

Duane
Hook'D on Access
MS Access MVP
 
Sure did. Worked like a charm. That's why I replied, "Yep, that works." And that's what I will use.

Thanks.

Tom
 
Duane
Yep, I get your point.

To clarify, I will use your code that works beautifully:
Code:
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
 
And the code will fail if NewData contains a double quote (try it) ...
A safe way:
Code:
strSql = "Insert Into tblAwards ([Award]) values ('" & Replace(NewData, "'", "''") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top