Compile error:Argument not optional

Question

Compile error:Argument not optional

I am getting the above error while executing the following query:

DoCmd.RunSQL = "insert into tblContract (Empid,testid,Start1,Finish1,Store1,Start2,Finish2,Store2 )values ('" & Me.txtEmpNo.Value & "','" & Me.txtAutoNumber.Value & "','" & Me.txtContSunStart1.Value & "', '" & Me.txtContSunFinish1.Value & "','" & Me.txtContSunStore1.Value & "','" & Me.txtContSunStart2.Value & "', '" & Me.txtContSunFinish2.Value & "','" & Me.txtContSunStore2.Value & "')"

Please help

Answer 1

Assuming your syntax is correct, you most likely have a field that requires a value but you've omitted from your target field list. That is a field in the table designer has the "Required" set to Yes in the General tab.

If you don't think that this is it, I'd probably need a little more info on the schema or perhaps the query.

Answer 2

Have you really got DoCmd.RunSQL = someString ? With that = in there? It won't like that...

Answer 3

This may not be the issue. But you may want to make sure your input does not contain a single quote. For example, if Me.txtContSunStore1 equals something like "Bob's Store", that line will complain. Though it would probably give you a different error.

I usually wrap all SQL Values with a function that cleans up the data based on the data type.

Answer 4

1. DoCmd.RunSQL is a method not a property. You don't do "DoCmd.RunSQL = SomeSQL" You do "DoCmd.RunSQL someSQL" (no equals sign).
2. DoCmd.RunSQL has very poor error handling and comes back with uninformative error messages. You will find it much easier to debug or trap errors using CurrentDB.Execute

Answer 5

Try add space before values.

As Oorang said remove the = sign

EDIT: Also please check if everyfields are text type. I would guess Empid and testid fields are numeric...