0

I'm trying to add a record to a table called Results. At the moment, when I run Err.Number and Err.Description I receive 3134 and "Syntax error in INSERT INTO statement." I've tried moving the "Values" syntax but no luck. Any ideas why this might be occurring? 

Private Sub addResult_Click()

Dim strSQL As String

strSQL = "INSERT INTO Results (Student_ID, Class_ID, Test_Type, 
Student_Score, Total_Score, Level) VALUES (" & Me.cboStudents.Column(0) & ", 
" & Me.cboTeacher.Column(0) & ",  '" & Me.cboTestType.Column(1) & "', " & 
Me.txtStudentScore.Value & ", " & Me.txtTotalScore.Value & ", '" & 
Me.cboTeacher.Column(2) & "')"

CurrentDb.Execute strSQL
Andre
  • 26,751
  • 7
  • 36
  • 80
Ben
  • 53
  • 1
  • 10
  • 1
    `Level` is in [Access 2007 reserved words and symbols](https://support.office.com/en-us/article/access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe). Escape it as `[Level]` – Flakes May 25 '18 at 02:43
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) -- running the SQL as query can give better error messages, or at least tell you *where* the problem is. – Andre May 25 '18 at 07:26
  • You need to get into the habit of using parameters and forget SQL concatenation. https://stackoverflow.com/questions/50102431/pass-string-variable-without-quotes-in-query-vba/50102644#50102644 – Kostas K. May 25 '18 at 08:13
  • Does any of these VALUES you are trying to insert contain a single quote? If yes, this may explain the error. Also, make sure you enclose all numeric fields in single quotes. Which fields data type are Numeric in the database? –  May 26 '18 at 21:15
  • Just saw the very first answer: yes, you must replace Level with [Level] in square brackets. This is most likely the issue. –  May 26 '18 at 21:18

0 Answers0