I've looked in many places on the internet trying to solve this problem but I can't seem to figure it out. Could someone just provide a second set of eyes and just check where I may have gone wrong with the syntax?
The error says RunTime error 3075, "Syntax error (comma) in query expression '('test',12/15/2014','Primary')'.
Originally it worked when I had this split into four different INSERT statements, but when I combined it, it stopped working
SQL = "INSERT INTO WeeksonCall([Employee], [Week], [Prim/Backup]) VALUES (('" & _ cboName.Column(1) & "','" & cboWeek.Column(1) & "','" & cboPrimBack.Value & _
"'),('" & cboName2.Column(1) & "','" & cboWeek.Column(1) & "','" & cboPrimBack2.Value & _
"'),('" & cboName.Column(1) & "','" & cboWeek2.Column(1) & "','" & cboPrimBack2.Value & _
"'),('" & cboName2.Column(1) & "','" & cboWeek2.Column(1) & "','" & cboPrimBack.Value & "'))"
That is not proper syntax in MS Access. You will have to insert the records individually like:
DoCmd.SetWarnings False
SQL = "INSERT INTO WeeksonCall(Employee, Week, [Prim/Backup]) " & _
"VALUES ('" & _ cboName.Column(1) & "','" & cboWeek.Column(1) & "','" & cboPrimBack.Value & "')"
SQL = "INSERT INTO WeeksonCall(Employee, Week, [Prim/Backup]) " & _
"VALUES ('" & cboName2.Column(1) & "','" & cboWeek.Column(1) & "','" & cboPrimBack2.Value & "')"
SQL = "INSERT INTO WeeksonCall(Employee, Week, [Prim/Backup]) " & _
"VALUES ('" & cboName.Column(1) & "','" & cboWeek2.Column(1) & "','" & cboPrimBack2.Value & "')"
SQL = "INSERT INTO WeeksonCall(Employee, Week, [Prim/Backup]) " & _
"VALUES ('" & cboName2.Column(1) & "','" & cboWeek2.Column(1) & "','" & cboPrimBack.Value & "')"
DoCmd.SetWarnings True
EDIT: You can stop the user prompts by using SetWarnings = False
before the inserts. Then turn the warnings back on after the inserts via DoCmd.SetWarnings True
.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments