SQL Statement run from Button on Frome

[Fireman1143]

I hve placed the following code on the OnClick button on a form. The SQL statement is to append the training records for a particular training session based on the employee name having a checkbox marked. When I tried the query it worked fine EXCEPT I had to manually type in the training session ID [tsPRIID]. I copied and transferred the code to notepad and have built the following code -

Dim strSQL As String

strSQL = ""
strSQL = strSQL & "INSERT INTO tblEmpTraining ( trEmpPRIID, trTrainDate, trGroup, trTopic, trHours ),"
strSQL = strSQL & "SELECT tblEmployees.EmpPRIID, tblTrainingSession.tsSessionDate, tblTrainingSession.tsGroup, tblTrainingSession.tsSessionTopic, tblTrainingSession.tsSessionHrs,"
strSQL = strSQL & "FROM tblEmployees, tblTrainingSession,"
strSQL = strSQL & "WHERE (((tblEmployees.AppendCkBox)<>0) AND ((tblTrainingSession.tsPRIID)= " & Me.txt_tsPRIID & "));"

DoCmd.RunSQL strSQL

Now I get the following error message -
Syntax error in INTO INSERT statement.

 

[jrbarnett]

You have a comma on the end of the "Insert" line after the close bracket.
You also have a comma on the end of the last "Select" line (before the From clause)

Remove these and all should be fine.

John

 

[lespaul]

There's also an extra comma at the end of the FROM clause.

Dim strSQL As String

        strSQL = ""
        strSQL = strSQL & "INSERT INTO tblEmpTraining ( trEmpPRIID, trTrainDate, trGroup, trTopic, trHours )[COLOR=red],[/color]"
        strSQL = strSQL & "SELECT tblEmployees.EmpPRIID, tblTrainingSession.tsSessionDate, tblTrainingSession.tsGroup, tblTrainingSession.tsSessionTopic, tblTrainingSession.tsSessionHrs[COLOR=red],[/color]"
        strSQL = strSQL & "FROM tblEmployees, tblTrainingSession[COLOR=red],[/color]"
        strSQL = strSQL & "WHERE (((tblEmployees.AppendCkBox)<>0) AND ((tblTrainingSession.tsPRIID)= " & Me.txt_tsPRIID & "));"
    
    DoCmd.RunSQL strSQL

Additionally, you don't have any spaces between the end of one statement and the beginning of another, so when this is concatenated (and the extra commas are removed) it will be:

INSERT INTO tblEmpTraining ( trEmpPRIID, trTrainDate, trGroup, trTopic, trHours ),SELECT tblEmployees.EmpPRIID, tblTrainingSession.tsSessionDate, tblTrainingSession.tsGroup, tblTrainingSession.tsSessionTopic, [b]tblTrainingSession.tsSessionHrsFROM[/b] tblEmployees, [b]tblTrainingSessionWHERE[/b] (((tblEmployees.AppendCkBox)<>0) AND ((tblTrainingSession.tsPRIID)= IDNumber)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Fireman1143]

Thank you both very much. After removing the commas and putting a space where need, it works like a charm.

It's great to have such knowlegable people to call on in a time of need.