CODEWITHSUNDEEP
sqlms-accessvba
santaaimonce
santaaimonce

Reputation: 144

error 3075 syntax missing comma

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 & "'))"

Upvotes: 0

Views: 543

Answers (2)

user2290801
user2290801

Reputation: 61

Can you please provide the remainder of the VBA you're using in order to ask the user for input? As far as I see you're only assigning a string to the SQL variable. Where is the rest of your code?

And, by the way, I never like using:

   DoCmd.SetWarnings False

Instead use the module below, that way if you do get an error you can deal with it instead of hidding it:

Option Compare Database
Option Explicit

Public Function Run_Safe_SQL(strSQL)
On Error GoTo Error_Handler
Dim db As DAO.Database

   Set db = CurrentDb()
   db.Execute strSQL, dbFailOnError
   DBEngine.Idle dbRefreshCache
'   DoEvents

Exit_Here:
   'Cleanup
   Set db = Nothing
   strSQL = ""
   Exit Function

Error_Handler:
    MsgBox Err.Description & " " & Err.Number

End Function

Upvotes: 1

Linger
Linger

Reputation: 15058

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.

Upvotes: 2

Related Questions