Problem with INSERT INTO (SQL) statement in VBA

[ruchie]

I am getting a syntax error while running the below-mentioned query in
VBA (Access), and I am not sure why.
I am trying to insert a record in a table (tblEmp). i am pulling in
data through a form (AddUser).

DoCmd.RunSQL ("INSERT INTO tblEmp (empName, Group, LOCATION, Group
Name, Title, Start_Date) VALUES(Forms!Form_AddUser!fname + ' ' + Forms!
Form_AddUser!lname, Forms!Form_AddUser!group, loc, Forms!Form_AddUser!
GName, Forms!Form_AddUser!title, Forms!Form_AddUser!DTPicker1);")

loc is an integer variable, created within the VBA only. it is not
being pulled in from the form.
I am fairly new to the VBA. Could anyone please let me know where I am
going wrong?

 

[Douglas J. Steele]

Try:

DoCmd.RunSQL "INSERT INTO tblEmp " & _
"(empName, Group, LOCATION, [Group Name], Title, Start_Date) " & _
"VALUES(""" & Forms!Form_AddUser!fname & ' ' & _
Forms!Form_AddUser!lname & """, """ & _
Forms!Form_AddUser!group & """, " & loc & ", """ & _
Forms!Form_AddUser!GName & """, """ & _
Forms!Form_AddUser!title & """, " & _
Format(Forms!Form_AddUser!DTPicker1, "\#yyyy\-mm\-dd\#") & ")"

Those are three double quotes in a row (assuming empName, Group, Group Name
and Title are all text fields).

 

[Klatuu]

Access queries can reference form control, but they cannot reference memory
variables like loc as you describe it. A way to do this would be to create a
hidden control on your form and put the value of loc in the control. Then
change your query to reference the control.

 

[Ken Snell \(MVP\)]

You need to concatenate the actual value of the loc variable into your
string:

DoCmd.RunSQL "INSERT INTO tblEmp (empName, Group, LOCATION, Group
Name, Title, Start_Date) VALUES(Forms!Form_AddUser!fname + ' ' + Forms!
Form_AddUser!lname, Forms!Form_AddUser!group, " & loc & _
", Forms!Form_AddUser!
GName, Forms!Form_AddUser!title, Forms!Form_AddUser!DTPicker1);"


It's also a good idea to concatenate the actual values from the form's
controls into the SQL string; assuming that all those values are strings
except for the one from the date picker:

DoCmd.RunSQL "INSERT INTO tblEmp (empName, Group, LOCATION, Group
Name, Title, Start_Date) VALUES( '" & _
Forms!Form_AddUser!fname + " " + Forms!Form_AddUser!lname & "', " & _
"'" & Forms!Form_AddUser!group & "', " & loc & _
", '" & Forms!Form_AddUser!GName & "', '" & _
Forms!Form_AddUser!title & "', #" & _
Format(Forms!Form_AddUser!DTPicker1, "mm/dd/yyyy") & "# );"

 

[ruchie]

Ken/Douglas,

I tried your methods, but it is still giving me the same run-time
error (3134 - syntax error in INSERT INTO statement).
Also, i do want to mention that 'loc' is a numeric field.
please guide me how to make this work!
thanks a lot guys

 

[Ken Snell \(MVP\)]

Post the code that you now have; likely there is a typo still.

 

[Douglas J. Steele]

You might also use

Dim strSQL As String

strSQL = "INSERT INTO tblEmp " & _
"(empName, Group, LOCATION, [Group Name], Title, Start_Date) " & _
"VALUES(""" & Forms!Form_AddUser!fname & ' ' & _
Forms!Form_AddUser!lname & """, """ & _
Forms!Form_AddUser!group & """, " & loc & ", """ & _
Forms!Form_AddUser!GName & """, """ & _
Forms!Form_AddUser!title & """, " & _
Format(Forms!Form_AddUser!DTPicker1, "\#yyyy\-mm\-dd\#") & ")"
Debug.Print strSQL
DoCmd.RunSQL strSQL

Once the code runs (and fails), go to the Immediate Window (Ctrl-G) and post
back what appears there.

 

[LarryP]

I might suggest the following as an alternative -- a few very minor
modifications. I broke it into multiple lines just because I find it easier
to work with that way, but since SQL supposedly ignores whitespace you
(ruchie) should be able to copy-and-paste it in its entirety with at least
some chance it'll run.


DoCmd.RunSQL "INSERT INTO tblEmp (empName, Group, LOCATION, Group
Name, Title, Start_Date) VALUES( '" & Forms!Form_AddUser!fname

&
"', '"
&
Forms!Form_AddUser!lname
&
"', '"
&
Forms!Form_AddUser!group
&
"', "
&
loc
&
", '"
&
Forms!Form_AddUser!GName
&
"', '"
&
Forms!Form_AddUser!title
&
"', #"
&
Format(Forms!Form_AddUser!DTPicker1, "mm/dd/yyyy")
&
"#);"

 

[enpaksh]

I finally got it to work. it was simple, but i just dont understand
why it was needed to be done. i just had to put the field names
(empName, Group...) in brackets ([]) and thats it! it started working,
although im still confused why.
thanks a lot everyone for all the help!

 

[ruchie]

the message above is from me only. somehow i was logged in google
through my friend's ID (using his laptop). sorry for the mix-up!

 

[Ken Snell \(MVP\)]

You must enclose table, query, and field names (as well as control names) in
[ ] characters in SQL statements (and in VBA code) whenever the name
contains characters other than letters, numbers, and underscore. You have
one field name that appears to be "Group Name", which contains a space;
therefore, it needs the use of the [ ] characters.

Sorry -- overlooked that initially (line wrapping confused me, I think).

 

[ruchie]

i enclosed "Group Name" in [], but even then it wasnt working. i had
to enclose every field, even the ones having no spaces or special
characters, in [], and only then the statement worked! still clueless,
but atleast its working now!