RunSQL - Syntax Error (1 Viewer)

durdle

Registered User.
Local time
Today, 23:22
Joined
May 7, 2002
Messages
130
I want to apologize for my ignorance because I am new to this type of statement. But when I use:

Private Sub cmdsubmit_Click()

DoCmd.RunSQL "INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (forms!frmmain2!cbotrainer, forms!frmmain2!date, forms!frm main2!cboweek, forms!frmmain2!cboans1, forms!frmmain2!Comments1, forms!frmmain2!cboans2, forms!frmmain2!Comments2, forms!frmmain2!cboans3, forms!frmmain2!Comments3);"

End Sub

I get a "Syntax Error in Insert INTO Statement".


I have this statement under a command button with no other code. Just wondering if anyone can see any problems with this code or do I have to put more code under the button.

Note: I use Access 2000
Thanks
________
honda cbr1100xx
 
Last edited:

pdx_man

Just trying to help
Local time
Today, 16:22
Joined
Jan 23, 2001
Messages
1,347
It is being seen as trying to put the actual text forms!frmmain2!cbotrainer into Trainer, not the contents of the cbotrainer on your form. You will need to something like:

DoCmd.RunSQL "INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (" & forms!frmmain2!cbotrainer & "," & forms!frmmain2!date & "," & ...
 

durdle

Registered User.
Local time
Today, 23:22
Joined
May 7, 2002
Messages
130
Hey pdx_man,

Thanks for the reply, but I am getting the same error message.
The code I used is

DoCmd.RunSQL "INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (" & Forms!frmmain2!cbotrainer & ", " & Forms!frmmain2!Date & "," & Forms!frmmain2!cboweek & ", " & Forms!frmmain2!cboans1 & ", " & Forms!frmmain2!Comments1 & ", " & Forms!frmmain2!cboans2 & ", " & Forms!frmmain2!Comments2 & ", " & Forms!frmmain2!cboans3 & ", " & Forms!frmmain2!Comments3 & ");"

Note: The Comments and date fields are text boxes and the rest is combo boxes.

Any other suggustions.

durdle
________
roor bong pictures
 
Last edited:

pdx_man

Just trying to help
Local time
Today, 16:22
Joined
Jan 23, 2001
Messages
1,347
Try this:

Dim SqlStr as String

SqlStr = "INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (" & Forms!frmmain2!cbotrainer & ", " & Forms!frmmain2!Date & "," & Forms!frmmain2!cboweek & ", " & Forms!frmmain2!cboans1 & ", " & Forms!frmmain2!Comments1 & ", " & Forms!frmmain2!cboans2 & ", " & Forms!frmmain2!Comments2 & ", " & Forms!frmmain2!cboans3 & ", " & Forms!frmmain2!Comments3 & ")"

Debug.Print SqlStr

Then, press Ctrl + G to view the debug window and take a look at what is REALLY trying to be run. You may have some funky value.
 

durdle

Registered User.
Local time
Today, 23:22
Joined
May 7, 2002
Messages
130
Hi Again,

I hate to keep on this but I can't see any issues with the data being appended. Below is the data:

INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (1, 6/1/04,3, 1, Test1, 5, Test2, 4, Test3)

Note: I have the combo boxes linked to tables. Not sure if that makes any difference.
Any other idea's.

durdle
________
lexus gx specifications
 
Last edited:

pdx_man

Just trying to help
Local time
Today, 16:22
Joined
Jan 23, 2001
Messages
1,347
Because of the date field, you will probably have to use:

"INSERT INTO tblmain (Trainer, Date, Week, A1_1, Comments1, A2_1, Comments2, A3_1, Comments3) Values (" & Forms!frmmain2!cbotrainer & ", #" & Forms!frmmain2!Date & "#," & Forms!frmmain2!cboweek & ", " & Forms!frmmain2!cboans1 & ", " & Forms!frmmain2!Comments1 & ", " & Forms!frmmain2!cboans2 & ", " & Forms!frmmain2!Comments2 & ", " & Forms!frmmain2!cboans3 & ", " & Forms!frmmain2!Comments3 & ")"

Also, Date and Week are reserved words and not recommended to use as field names. You can have other issues when referencing them in forms and code. Highly recommend changing them.
 
R

Rich

Guest
Are the values you're trying to append based on the Bound columns of the combo box?
 

durdle

Registered User.
Local time
Today, 23:22
Joined
May 7, 2002
Messages
130
Rich,

These combo boxes are bound to tables.
Could this be my problem?

PDX,

I used that code and I still get the syntax error.


durdle
________
toyota corolla history
 
Last edited:

GrahamB

Registered User.
Local time
Today, 23:22
Joined
Aug 13, 2003
Messages
22
PDX is correct Date and Week are possibly creating your problems...

If you must use them then you should surround them with [] so [Date] :D
 

Users who are viewing this thread

Top Bottom