When I am using ms access 2007 it works perfectly. I swtich to ms access 2010 and now it does not work.
DoCmd.RunSQL ("INSERT INTO Pending_Orders (Customer, ItemNumber, Description, Qty, [Order #], Temp, ShipDate) VALUES (" & _
"'" & Replace(rst!Customer, "'", "''") & "','" & rst![Item #] & "','" & rst!Description & "'," & rst!Qty & ",'" & rst![Order #] & "'," & NextTemp & ",#" & rst![Ship Date] & "#)")
I am getting this error now.
What it could be?
I would suggest you to replace the single quotes in the above code to double quotes, this is one common problem with names like Paul O'Connor. Also use CurrentDb.Execute over DoCmd.RunSQL. As using currentBD you can suppress the annoying messages and see many more information. Finally, always use a String to get the SQL query, so debugging could be a lot easier.
Try the following,
strSQL = "INSERT INTO Pending_Orders (Customer, ItemNumber, Description, Qty, [Order #], " & _
"Temp, ShipDate) VALUES (" & Chr(34) & rst!Customer & Chr(34) & _
", " & Chr(34) & rst![Item #] & Chr(34) & _
", " & Chr(34) & rst!Description & Chr(34) & _
", " & rst![Qty] & ", " & Chr(34) & rst![Order #] & Chr(34) & _
", " & NextTemp & ", " & Format(rst![Ship Date], "\#mm\/dd\/yyyy\#") & ")
CurrentDB.Execute strSQL
In addition to Paul Francis answer, I offer the EscapeDBstring function which solves the problem of the embedded quote in "Paul O'Connor" by escaping the embedded quote:
' dbEscapeString -- escape the single quote in a string that will be included in
' an SQL statement. The single quote is the database/SQL string delimiter.
Public Function dbEscapeString(ByVal s)
Dim i
i = 1
While (i <= Len(s))
If (Mid(s, i, 1) = "'") Then
'If (Mid(s, i + 1, 1) = "'") Then ' this would consider two single quotes to be escaped already
' i = i + 1
'Else
s = Mid(s, 1, i) + Mid(s, i)
i = i + 1
'End If
End If
i = i + 1
Wend
dbEscapeString = s
End Function
