Run-time error 3134 :Syntax error in INSERT INTO statement (1 Viewer)

[santhosh23]

Hi

I am creating a table in access database for my company which reads the data from a subform and store in a table. I have 5 values in which 2 of them are combo box and 3 values are time values (start time , end time and work time)..I executed the below insert into statement which throws runtime error .

CurrentDb.Execute "INSERT INTO sample(toolCare Job#,Worked On Activity,Start Time, End Time, Total Time)" &_
"VALUES('" & Me.cbotoolCare_Job & "','" & Me.cboWorked_On_Activity & "','" & Me.txtStartTime & "','" & Me.txtEndTime & "','" & Me.txtWorkTime & "')"

I also executed various queries reading forums but nothing worked for me..

any help could be appreciated ..

thank you

 

[plog]

You have poorly named field names:

toolCare Job#

You should only use alphanumeric and underscores in names. When you don't do that you must use brackets to tell the SQL engine where your field names are:

[toolCare Job#]

Also, I'd add spaces before and after your keywords and table names:

sample(...)VALUES(

sample (...) VALUES (

 

[CJ_London]

Although Plog has covered it, I would point out that you have spaces in your names - e.g. Worked On Activity

these also need to be in square brackets

 

[santhosh23]

Hi

Thanks for the reply. I executed this below query but I am still getting runtime error.

CurrentDb.Execute "INSERT INTO sample ([toolCare Job#],Worked On Activity,Start Time, End Time, Total Time)" & _
"VALUES ('" & Me.cbotoolCare_Job & "','" & Me.cboWorked_On_Activity & "','" & Me.txtStartTime & "','" & Me.txtEndTime & "','" & Me.txtWorkTime & "')"

Can you give me some inputs.. Is this query correct because when i searched in forums in few they have asked hashtags before the time field ..

 

[plog]

Again, when you have non-alphanumeric characters (like a space) you need brackets. You need to fully bracket all your fields.

And why are you not using a bound form? Why are you doing this via VBA?

 

[santhosh23]

Hi ..I am able to record values in tables for all fields except toolcare job ..I think the field names are problem and I am changing it ..but my subform doesnt show table data ..it just shows like #Name? for all fields ..can you provide some info.. and I am just using access for 3 days so I didnt know about bound form ..I will check that..thank you

 

[santhosh23]

@Plog , @CJ_Johnson ..

thank you so much for your help

 

[santhosh23]

Hi ..

I am trying to update information for 7 fields now from the form to the table ...but I am not able to get for one field "Me.txtbarcode"..any help is appreciated ..

CurrentDb.Execute "INSERT INTO sample ([User],[Barcode],[toolCare Job],[Worked On Activity],[Start Time], [End Time], [WorkTime])" & _
"VALUES ('" & Me.txtUser & "','" & Me.txtbarcode & "','" & Me.cbotoolCare_Job & "','" & Me.cboWorked_On_Activity & "','" & Me.txtStartTime & "','" & Me.txtEndTime & "','" & Me.txtWorkTime & "')"

thanks in advance

 

[CJ_London]

not sure what you mean by

but I am not able to get for one field "Me.txtbarcode"

please clarify

 

[santhosh23]

Hi..I created a navigation form which has the subform I am giving values for 7 fields and these 7 fields are then written to a table..I am able to record data's for 6 field but one of the fields I am not able to record data...Barcode field which gets value from barcode scanner.. this is the query i am using for saving data..

CurrentDb.Execute "INSERT INTO sample ([User],[Barcode],[toolCare Job],[Worked On Activity],[Start Time], [End Time], [WorkTime])" & _
"VALUES ('" & Me.txtUser & "','" & Me.txtbarcode & "','" & Me.cbotoolCare_Job & "','" & Me.cboWorked_On_Activity & "','" & Me.txtStartTime & "','" & Me.txtEndTime & "','" & Me.txtWorkTime & "')"

any help is appreciated ..

 

[CJ_London]

I'm still confused - is the problem with your query or with populating the barcode field from the scanner?

Are you getting any errors?

barcode readings are usually text - is your field in your sample table also text and of sufficient length?

You are only showing the execute statement - have you disabled error messages? If so, temporarily enable them until the problem is resolved

 

[santhosh23]

Hi ..I think there is no problem in populating it works fine..I read barcode values using scanner and the values are updated in barcode field and I have a before update event procedure that works fine ..the code is below..

BarcodefrmTable = DLookup("Barcode", "Barcode_Table", "toolCareJob= '" & Me.cbotoolCare_Job & "'")
BarcodefrmScanner = Me.txtbarcode.Value

If BarcodefrmTable = BarcodefrmScanner Then

MsgBox "Barcode Match with ToolcareJob Go ahead fill start time"
DoCmd.Save

Else

MsgBox "Barcode and ToolCareJob Not Matching"

these 2 condns are checked ..and I get the message..but after this when I save the data ..all the other fields are updated whereas the barcode field is not updated in my subform..i am wondering do I need to give control source for this barcode..thats empty now..

and I didnt disable the error message