it gives an error when i run a SQL script-CodePudding

I'm trying to INSERT INTO with SQL in a table in VBA but it gives a error while running this script. why?

I was expecting it to add the values to the table but it gives an error instead, does anyone has the solution for this?

DoCmd.RunSQL "INSERT INTO tblScores (Student, Score, Date, ExamType, Lesson) VALUES (cbStudent.Value,txtScore.Value,txtDate.Value,cbExamType.value,cbLesson.Value);"

The error message:

this is the error it gives

CodePudding user response：

The form values need to be concatenated to the SQL string, not being enclosed to it.

For example:

"WHERE ID =" & IdControl.Value

But this way is not recommended for various reasons. Best to create a temporary query and pass the values as parameters. If you need to run it frequently, create a permanent query and just call it - it will be slightly faster.

Const SQL As String = "PARAMETERS [Student] Text (255), [Score] IEEESingle, [Date] DateTime, [ExamType] Text (255), [Lesson] Text (255); " & _
                      "INSERT INTO tblScores (Student, Score, [Date], ExamType, Lesson) " & _
                      "SELECT [Student] AS [Student], [Score] AS [Score], [Date] AS [Date], [ExamType] AS [ExamType], [Lesson] AS [Lesson];"
                      
Dim q As DAO.QueryDef
Set q = CurrentDb().CreateQueryDef("", SQL) 'temporary query 
    q.Parameters("[Student]").Value = cbStudent.Value
    q.Parameters("[Score]").Value = txtScore.Value
    q.Parameters("[Date]").Value = txtDate.Value
    q.Parameters("[ExamType]").Value = cbExamType.Value
    q.Parameters("[Lesson]").Value = cbLesson.Value
    q.Execute
    q.Close

Keep in mind I don't know the actual data-types of your table fields, so some of the params could be off - the score for example.

CodePudding user response：

Try this: Dim varStudent as String . varStudent = cbStudent . In SQL String: VALUES ( '" & varStudent & "', '" & varTxtScore & "'

'" = SingleQuote DoubleQuote