Syntax Error in SQL Statement

[ColinWard]

Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" 'SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values ('" & TxtProductName. Text &
"');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Don Leverton]

Hi Colin

I like to assign a variable that represents the value contained by a form
control.
Then I totally seperate any variables from the textual portion of the SQL
string when doing this kind of thing.

Assigning a variable to a form's control also allows you to inspect it's
contents while in break mode.
Set a breakpoint in your code, at say the "StrSql = " line . Then run the
procedure, and you'll see what I mean.
"Hover" your mouse cursor over the "MyProductN ame" variable, and it's
contents will displayed in a "tooltip"

I'd also suggest using the .Execute method ( including the dbFailOnError)
rather than RunSQL.
The problem with using RunSQL is that some (but not all) records will be
changed by the action query, until such time that an error occurs.
If an error occurs with RunSQL, you have to look through the table and
determine which records have (or have not) been changed.
The dbFailOnError is an "all-or-nothing" approach to updating records. If an
error should occur, all changes are "rolled back".

All of the above is *my understanding* of how things work.
That being said, try it like this:

*************** *************** *************** ****
....
Dim MyDB as DAO.Database
Set MyDB = CurrentDb

Dim MyProductName As String
MyProductName = Me![TxtProductName]

StrSql = ""
SrtSql = StrSql & "INSERT INTO [Products] (ProductName) " 'You were missing
a space between [Products] and (ProductName)
SrtSql = StrSql & "VALUES ("
SrtSql = StrSql & MyProductName 'No quotes here, JUST the variable name.
SrtSql = StrSql & ");"

Debug.Print StrSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight the
offending portion

MyDB.Execute StrSql, dbFailOnEror

Set MyDB = Nothing
....
*************** *************** *************** ****

--
HTH,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==
"ColinWard" <je*********@ho tmail.com> wrote in message
news:40******** **************@ news.newsgroups .ws...

Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" 'SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values ('" & TxtProductName. Text &
"');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Thanks for your help don. I will try this on Monday. Of course you know
that this means that I will have to change all my runSQL statements
arrgh!!! LOL

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

*************** *************** *************** ***********
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*************** *************** ***************

thanks Don

Colin
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Don Leverton]

Hi Colin,

Sorry, I forgot to "wrap" the "MyProductN ame" variable in quotation marks
....
(notice that I did that using the Chr$(34) substitute for the ["] character
in the revised code below.)

I was getting:
INSERT INTO [Products] (ProductName) VALUES(Sunlight Dishwash Soap);

where it should have been:
INSERT INTO [Products] (ProductName) VALUES("Sunligh t Dishwash Soap");
*************** ****** Revised Code *************** **************
Private Sub cmdAddProduct_C lick()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]

strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & Chr$(34) & MyProductName & Chr$(34)
strSql = strSql & ");"

'Debug.Print strSql

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

End Sub
*************** ****** End Revised Code *************** ********

Regards,
Don
_______________ _______________ _______________ _______________ _

"ColinWard" <je*********@ho tmail.com> wrote in message
news:40******** **************@ news.newsgroups .ws...

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

*************** *************** *************** ***********
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*************** *************** ***************

thanks Don

Colin
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jim Allensworth]

On 05 Jul 2004 17:36:41 GMT, ColinWard <je*********@ho tmail.com>
wrote:

Hi Don,

This is what I currently have but I get a 'Mising Operator' error with
the last part of the SQL statement.

************** *************** *************** ************
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductNam e = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName 'No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight
the
'offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

************** *************** *************** *

thanks Don

Colin

Colin, this should be all you need.
=============== =
Dim strSql As String

strSql = "INSERT INTO [Products] (ProductName) VALUES('" _
& Me![TxtProductName] & "');"
'Still need to wrap a string with single quotes.

CurrentDb.Execu te strSql, dbFailOnError
=============== =

Notes:
You still need to wrap the string in single quotes, and if the Product
name has any quotes single or other, they will have to be managed.
BTW, the product usually has an ID number that is much easier to deal
with.

You don't really need an object variable to use the execute method -
just do it directly from CurrentDb - unless you need the object for
something else. Which you don't here.

For small strings like this I find that line continuation character is
much easier to read than continuous concatenating - and less typing.
My 0.02

- Jim

[Don Leverton]

Hi Jim,

That "_" line-continuation character issue is definitely a matter of
personal preference then ...
I for one loathe and despise that stupid thing! <grin>

I admit that I must just be a very methodical thinker, and can only deal
with "sequential steps" while writing code.
For me, the way that the SQL strings "line up" with each other make it
easier for me to follow.
It also allows me to quicky and easily "inspect" the value of a variable (in
break mode), right inside of the SQL string.
It also all but eliminates those annoying "double-quote, single-quote,
double-quote" and the "Forms!MainForm .SubForm.Form!C ontrols.Control Name"
syntax nightmares.

As far as the "less typing" aspect goes, I have a little utility program
called SQL2VAR.EXE that does 99% of that repetitious typing for me!
I just paste in a sample SQL string from the SQL view of a query that I have
designed and tested, then hit the [Generate] button! (For short strings like
this one, though, I generally just "Copy/Paste" MySQL = MySQL & "" 4 or 5
times and then "fill in the blanks".)
After pasting the generated string inside my code window, I simply replace
the "literal" values with variables, and I'm "good to go".

I will agree with what you said about the CurrentDB thing, though. It saves
having to "Set" and "UnSet" the object. I'll likely adopt that method.
That's what I like about this forum ... you get to see a lot of alternative
methods, and learn a lot doing so.

There's my $0.02 worth. <grin>
Have a great day,
Don
"Jim Allensworth" <Ji****@NOTdata centricsolution s.com> wrote in message
news:40******** *******@netnews .comcast.net...

On 05 Jul 2004 17:36:41 GMT, ColinWard <je*********@ho tmail.com>
For small strings like this I find that line continuation character is
much easier to read than continuous concatenating - and less typing.
My 0.02

- Jim

[ColinWard]

Hi Don. This works great. I am now trying to modify it so that I can add
a second value (ProductDescrip tion) to the same record but when I do it
as below I get an error saying that the number of values and the number
of destination fields are not the same.

Thanks Don

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Hi Don I forgot to post the code tht i am trying to use for two values:
******CODE START********** *************** *************
Private Sub cmdAddProduct_C lick()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim strSql As String
Dim MyProductName As String
Dim MyProductDescri ption As String
MyProductName = Me![TxtProductName]
MyProductDescri ption = Me![txtProductDescr iption]

strSql = ""
strSql = strSql & "INSERT INTO [Products]
(ProductName,Pr oductDescriptio n)"
strSql = strSql & "VALUES("
strSql = strSql & Chr$(34) & MyProductName & Chr$(34)
strSql = strSql & Chr$(34) & MyProductDescri ption & Chr$(34)
strSql = strSql & ");"
'Debug.Print strSql
MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing
End Sub
*******CODE END************ *************** **************

Thanks Don

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jim Allensworth]

Hi Don,

On Mon, 05 Jul 2004 19:56:15 GMT, "Don Leverton"
<le************ ****@telusplane t.net> wrote:

Hi Jim,

That "_" line-continuation character issue is definitely a matter of
personal preference then ...
I for one loathe and despise that stupid thing! <grin>
Personal preference, absolutely. So much of this stuff is.

I admit that I must just be a very methodical thinker, and can only deal
with "sequential steps" while writing code.
For me, the way that the SQL strings "line up" with each other make it
easier for me to follow. I use the ampersand to "line up" the continued string. When I see that
at the beginning of a line I immediately know is continued.
It also allows me to quicky and easily "inspect" the value of a variable (in
break mode), right inside of the SQL string. That can also be done with continuation.
It also all but eliminates those annoying "double-quote, single-quote,
double-quote" and the "Forms!MainForm .SubForm.Form!C ontrols.Control Name"
syntax nightmares.
AFAICS, those have to go somewhere. And if the text value has single
or double quotes then you still have to deal with those.
As far as the "less typing" aspect goes, I have a little utility program
called SQL2VAR.EXE that does 99% of that repetitious typing for me!
I just paste in a sample SQL string from the SQL view of a query that I have
designed and tested, then hit the [Generate] button! (For short strings like
this one, though, I generally just "Copy/Paste" MySQL = MySQL & "" 4 or 5
times and then "fill in the blanks".)
After pasting the generated string inside my code window, I simply replace
the "literal" values with variables, and I'm "good to go".

I will agree with what you said about the CurrentDB thing, though. It saves
having to "Set" and "UnSet" the object. I'll likely adopt that method.
That's what I like about this forum ... you get to see a lot of alternative
methods, and learn a lot doing so.
Absolutely! The exchange of ideas is invaluable.

There's my $0.02 worth. <grin>
Have a great day,
Don

I hope your day is good also,
- Jim