Syntax error into insert statement 1

[maupiti]

Access 2003

The query below work, when it is in a saved query, but when I put it in VBA it gives me an error.

Syntaxt error into insert statement.

//////////////////////////////////////////////

Private Sub Input_Training_Records_Click()
Dim Dbs As DAO.Database
Set Dbs = CurrentDb

Dbs.Execute "INSERT INTO Table_Employee_Training_History
(Course_Name, Type_Of_Training, From_Date_Training,
To_Date_Training, SSN, Employee_First_Name,
Employee_Last_Name ) " & _
"SELECT '" & Forms!Training_Input!Course_Name_Combo_Box
& "' AS Course_Name, " & _
" '" & Forms!Training_Input!Type_Of_Training_Combo_Box_2
& "' AS Training_Input, " & _
" '" & Forms!Training_Input!From_Date & "' AS
From_Date_Training, " & _
" '" & Forms!Training_Input!To_Date & "' AS
To_Date_Training, " & _
"How_Many_Employees_Does_A_Suprvisor_Have.SSN, " & _
"How_Many_Employees_Does_A_Suprvisor_Have.[First Name], " & _
"How_Many_Employees_Does_A_Suprvisor_Have.[Last Name], " & _
" FROM How_Many_Employees_Does_A_Supervisor_Have);"

Dbs.Close
End Sub

 

[rjoubert]

Try adding .Value after your textbox and combobox references...

Forms!Training_Input!Course_Name_Combo_Box.Value
Forms!Training_Input!From_Date.Value

Also, in the SELECT portion of your SQL statement, I don't think you need the "AS" clauses.

 

[PHV]

Are From_Date_Training and To_Date_Training Dates or Strings ?
If dates you have to use # instead of ' !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

And in addition to those observations ...

Private Sub Input_Training_Records_Click()
 Dim Dbs As DAO.Database
 Set Dbs = CurrentDb

 Dbs.Execute "INSERT INTO Table_Employee_Training_History 
 (Course_Name, Type_Of_Training, From_Date_Training,
  To_Date_Training, SSN, Employee_First_Name,
  Employee_Last_Name ) " & _
 "SELECT '" & Forms!Training_Input!Course_Name_Combo_Box
 & "' AS Course_Name, " & _
 "  '" & Forms!Training_Input!Type_Of_Training_Combo_Box_2
 & "'  AS Training_Input, " & _
 "  '" & Forms!Training_Input!From_Date & "'  AS
 From_Date_Training, " & _
 "  '" & Forms!Training_Input!To_Date & "'  AS
 To_Date_Training, " & _
 "How_Many_Employees_Does_A_Suprvisor_Have.SSN, " & _
 "How_Many_Employees_Does_A_Suprvisor_Have.[First Name], " & _
 "How_Many_Employees_Does_A_Suprvisor_Have.[Last Name][COLOR=red yellow],[/color] " & _
 " FROM How_Many_Employees_Does_A_Supervisor_Have[COLOR=red yellow])[/color];"

  Dbs.Close
End Sub

The indicated [COLOR=red yellow],[/color] and [COLOR=red yellow])[/color] shouldn't be there.