RunSQL syntax

[Monty]

Can some one tell me what's wrong with this line?

DoCmd RunSQL "INSERT INTO Packages ([Package Name], [Serial Number])
VALUES (' " & Me.Package_name & " ',' " & Me.Serial_number & " ')"

(The single quotes deliberately have spaces inserted for clarity here)

I keep getting and "End of Line Expected" error after VALUES (' ", and
I can't find a good guide. This statement is similar to others I've
used before without problem, as far as I can tell.

Thanks

 

[Douglas J. Steele]

Is that supposed to be 1 line of text, or two? If it's two, that's the
problem.

DoCmd RunSQL "INSERT INTO Packages (" & _
"[Package Name], [Serial Number]) " & _
"VALUES ('" & Me.Package_name & "', " & _
"'" & Me.Serial_number & "')"

 

[Guest]

Assuming you have this all on one line you might just need to have a
semicolon at the end of the SQL ? - inside your final speech mark, so the
last bit is & " ');"

 

[Monty]

I'm afraid the semi-colon didn't do the trick. I know that's required
when sqlplus'd to the DB server in Oracle, for instance, but I've
never seen it necessary in Access, but I gave it a try nonetheless.

Also, my original post was one line, the correct representation of
which can be seen if you hit reply and then edit the two lines into
one. However, for the sake of completeness, I broke it into multiple
lines as suggested by Doug Steele, and it still errored, only now the
error comes on the first line. The error registers as soon as I hit
return at the end of the line, and it highlights the entire first
line, less the line continuation characters, as though it's expecting
something else there.

Could this have anything to do with the underlying form ("Me") not
having any source table or query attached?

 

[Douglas J. Steele]

I just went back and looked at your original post again.

You've missing the period between DoCmd and RunSQL:

DoCmd.RunSQL "INSERT INTO Packages (" & _
"[Package Name], [Serial Number]) " & _
"VALUES ('" & Me.Package_name & "', " & _
"'" & Me.Serial_number & "')"

 

[John W. Vinson]

Can some one tell me what's wrong with this line?

DoCmd RunSQL "INSERT INTO Packages ([Package Name], [Serial Number])
VALUES (' " & Me.Package_name & " ',' " & Me.Serial_number & " ')"

(The single quotes deliberately have spaces inserted for clarity here)

I keep getting and "End of Line Expected" error after VALUES (' ", and
I can't find a good guide. This statement is similar to others I've
used before without problem, as far as I can tell.

Thanks

Looks like you have some mismatched quotes and some extra blanks. Are
Package_Name and Serial_Number in fact Text fields? Are there controls on the
form with those names, and do they contain valid values for insertion into
the table? (If so, why are you using RunSQL rather than a bound form??)

Try

DoCmd.RunSQL "INSERT INTO Packages ([Package Name], [Serial Number])" _
& "VALUES ('" & Me.Package_name & "', '" & Me.Serial_number & "')"

If Package_Name is MyPackage and Serial_Number is 123123, this will generate a
string

INSERT INTO Packages ([Package Name], [Serial Number]) VALUES ('MyPackage',
'123123')

Note that the singlequote characters ' should be *inside* the string constants
delimited by ".

If the SerialNumber is a Number field (it probably should in fact be text),
then leave off the ' delimiters around its value.

John W. Vinson [MVP]

 

[George Nicholson]

Nothing appears wrong with your code, so to debug the problem I would create
a string variable to hold your SQL statement and then check it's value
before you try to execute it.

'***********
Dim strSQL as string

strSQL = "INSERT INTO Packages ([Package Name], [Serial Number]) "
strSQL = strSQL & "VALUES (' " & Me.Package_name & " ',' " &
Me.Serial_number & " ');"
DoCmd RunSQL strSQL
'***********

Put a breakpoint on the DoCmd line. Run your app/form/whatever. When the
breakpoint is reached, type
? strSQL
in the immediate window. Does the result look right? Can you copy & paste it
into the query designer & get it to run?

Could this have anything to do with the underlying form ("Me") not
having any source table or query attached?

No. Not as long as Me has controls named Package_name and Serial_number.

HTH,

 

[Pat Hartman \(MVP\)]

When I have trouble with a string such as this that requires embedded
quotes, I create a variable and place the string in the variable where it is
easier to examine. Also, I create a constant that I use in place of the
multiple quote marks to make it more obvious where the embedded quotes will
go. Every place you see the word QUOTE, a literal " will be inserted into
the string.

In a separate module where all public variables are defined:
Public Const QUOTE = """"

Your new string will be:
Dim strSQL as String
strSQL = "INSERT INTO Packages ([Package Name], [Serial Number]) VALUES (" &
QUOTE & Me.Package_name & QUOTE & ", " & QUOTE & Me.Serial_number & QUOTE &
");"
DoCmd.RunSQL strSQL

 

[Monty]

I just went back and looked at your original post again.

You've missing the period between DoCmd and RunSQL:

DoCmd.RunSQL "INSERT INTO Packages (" & _
"[Package Name], [Serial Number]) " & _
"VALUES ('" & Me.Package_name & "', " & _
"'" & Me.Serial_number & "')"

Doh!

I will change my sign-off to Homer Simpson forthwith.

Thanks! That worked. And thanks to all the others that took a look
at this one.

 

[Douglas J. Steele]

I just went back and looked at your original post again.

You've missing the period between DoCmd and RunSQL:

DoCmd.RunSQL "INSERT INTO Packages (" & _
"[Package Name], [Serial Number]) " & _
"VALUES ('" & Me.Package_name & "', " & _
"'" & Me.Serial_number & "')"

Doh!

I will change my sign-off to Homer Simpson forthwith.

Thanks! That worked. And thanks to all the others that took a look
at this one.

Don't feel too bad. I didn't notice it the first time around, and neither
did the others!

 

[Tony Toews [MVP]]

DoCmd RunSQL

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/