INSERT INTO code getting errors

Question

Good morning. 

I am also having a problem getting my INSERT INTO code to work.  Here is what I currently have...

Private Sub Command29_Click()

    INSERT INTO [tbl_EmpTrainingData] (EmpID, EmpTrainingDate, EmpTrainingLocation, EmpHours,EmpModule)

    VALUES (me.Emp_ID, me.frm_input_TrainingDate, me.frm_imput_Location, me.frm_input_Hours, me.frm_input_Module_cbo);

End Sub

I'm still kinda new to Access SQL, and would be grateful if I can get some education here.

Thanks

Chip

Answer 1

"This all depends on the data types of your fields." is EmpModule a text field? If so you need to wrap it in quotes.

Notice I added the debug.print? Did you open the debug window to see the values of strSQL?

strSQL = "INSERT INTO [tbl_EmpTrainingData] (EmpID, EmpTrainingDate, EmpTrainingLocation, EmpHours,EmpModule) " & _

"VALUES (" & Me.Emp_ID & ", #" & Me.frm_input_TrainingDate & "# , """ & _

Me.frm_imput_Location & """, " & Me.frm_input_Hours & ", """ & Me.frm_input_Module_cbo & """)"

If your code still errors, reply back with the data types as well as the value in the debug window.

Also, always add Option Explicit in the general declarations of your module and compile all of your code.

Answer 2

Do you have a combobobox on your form with that name?

And, can I ask why you are using An INSERT INTO statement instead of just binding the form to the table? 

Finally, I think you need one more table. You need a table of the Training modules.

Answer 3

Thank you for your quick response!!

This particular database has only 2 tables.  One table holds driver information, the other holds training module records, one for each module completed.  The "frm_input" parts of the control names help me remember which are inputs on the form.

I just copied this code into my code and am now getting a syntax error

regarding the combo box  Me.frm_input_Module_cbo

Answer 4

I'd recommend one amendment to Duane's code:

"VALUES (" & Me.Emp_ID & ", #" & Format(Me.frm_input_TrainingDate,"yyyy-mm-dd") & "# , """ & _

By formatting the date literal in the ISO standard for date notation of YYYY-MM-DD, the code will be executed correctly regardless of the regional date format set for the system.

Answer 5

Hi Chip,

I believe you need something like:

Private Sub Command29_Click()

Dim strSQL As String

strSQL = "INSERT INTO [tbl\_EmpTrainingData] (EmpID, EmpTrainingDate, EmpTrainingLocation, EmpHours,EmpModule) " & \_

    "VALUES (" & Me.Emp\_ID & ", #" & Me.frm\_input\_TrainingDate & "# , """ & \_

     Me.frm\_imput\_Location & """, " & Me.frm\_input\_Hours & ", " & Me.frm\_input\_Module\_cbo & ")"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

End Sub

This all depends on the data types of your fields. I assume some are text, date/time, and numbers. Do your control names really begin with "frm_input"? Is "frm_imput_location" a spelling error?