runtime error 3134

[TheLazyEngineer]

Hey, I'm new here and new to access. I'm trying to set up a simple database for my company that I can keep track of the programs we write, and the customer we write them for, etc...
I'm having this recurring error with my code and I have no idea why it's happening, I have looked at pretty much every resource to no avail.

The error is
"Run-time error '3134':
Syntax error in INSERT INTO statement."

Here is my code, any help would be very appreciated, thanks!
(I attempted to highlight the code that is giving me the error)
---------------------------------------------------------------------------------------
Private Sub cmdAdd_Click()
'add data to table
CurrentDb.Execute "INSERT INTO PROGRAM(programid, programnum, robot, box, options, origrom, date, disk, customer) VALUES (" & Me.txtID & ",'" & Me.txtRobot & "','" & _ Me.txtBox & "'.'" & Me.txtOptions & "'.'" & Me.txtRom & "'.'" & Me.txtDate & _ "'.'" & Me.txtDisk & "'.'" & Me.txtCustomer & "')"
'refresh data in list on form
frmProgramSub.Form.Requery

End Sub

Private Sub cmdClear_Click()
Me.txtNumber = ""
Me.txtRobot
Me.txtBox
Me.txtOptions
Me.txtRom
Me.txtDisk
Me.txtCustomer
Me.txtDate

Me.txtID.SetFocus
End Sub

Private Sub cmdClose_Click()
DoCmd.Close

End Sub

Private Sub txtID_Click()

End Sub
---------------------------------------------------------------------------------------

 

[TJPoorman]

There are a couple of things that look wrong with your syntax.
1. You have too many fields in your Fields portion of syntax with not correct matching Values (ie. programid). You do not include the field unless you are specifying a corresponding value.
2. For some reason you switched from commas to periods to separate your Values

CurrentDb.Execute "INSERT INTO PROGRAM(programnum, robot, box, options, origrom, date, disk, customer) VALUES (" & Me.txtID & ",'" & Me.txtRobot & "','" & _
Me.txtBox & "', '" & Me.txtOptions & "', '" & Me.txtRom & "', '" & Me.txtDate & _
"', '" & Me.txtDisk & "', '" & Me.txtCustomer & "')"

If you still receive an error with the above code you need to look at your data types. When using VBA-SQL syntax, use single quotes when the field is text (''), hashtags when the field is date (##), and nothing when the field is a number.

 

[TheLazyEngineer]

Thanks for the reply, I tried using your edited code and it is still giving me the same error, your wording confuses me and I'm still unsure exactly what you said I should check/change. I have attached the file in hopes that mabye someone could take a look at it, to help me get a better understanding at what I am doing. If you do get to change/fix it, I would greatly appreciate it if you told me what you did, as that I would like to know what the hell I have been messing up on this whole time.

Thanks.

 

[gemma-the-husky]

try this change

an error handler will make it easy to see the problem, and at least you will be able to see exactly what your assembled statement looks like.

but just glancing, you have a lot of periods, instead of commas as separators in the values list towards the end ....

note that for numeric, you don't need to wrap in "" chars.
dates need to be wrapped in ## chars, and also need coercing to UK dates, or they will be treated as US dates (mm/dd/yyyy) where possible

 dim s as string
  
 [COLOR=#ff0000]s = "INSERT INTO PROGRAM(programid, programnum, robot, box, options, origrom, date, disk, customer) VALUES (" & Me.txtID & ",'" & Me.txtRobot & "','" & _ Me.txtBox & "'.'" & Me.txtOptions & "'.'" & Me.txtRom & "'.'" & Me.txtDate & _ "'.'" & Me.txtDisk & "'.'" & Me.txtCustomer & "')"[/COLOR]
  
 [COLOR=#ff0000]onerror goto fail[/COLOR]
 [COLOR=#ff0000]currentdb.exectute s, dbfailonerror[/COLOR]
 [COLOR=#ff0000]..[/COLOR]
 [COLOR=#ff0000]...[/COLOR]
 [COLOR=#ff0000]exit sub[/COLOR]
  
  
 [COLOR=#ff0000]fail:[/COLOR]
 [COLOR=#ff0000]   msgbox "Error: " & err & "  Desc: " & err.description & vbcrlf & vbcrlf & _[/COLOR]
 [COLOR=#ff0000]   "statement was:  & s[/COLOR]

 

[CJ_London]

date is also a reserved word, either change it or put it in square brackets