sql UPDATE syntax error

A

Ali

Hi -
I am new to VBA so please bear with me. I am trying to update a table
through a sub-form and am trying to use a sql statement to do this for
me.

When I run this code --
updatesql = "UPDATE tblMultipleCounties SET tblMultipleCounties.Cnty =
""" & Me!CountyName & """, tblMultipleCounties.County = Me!CountyCode,
tblMultipleCounties.[Updated?] = Yes WHERE (((tblMultipleCounties.[Log
Number])=Me.Parent.[LogNo]));"

I get a runtime error 3061 "Too few parameters. Expected 1." Anyone
know how I should fix this?

Thanks!
 
D

Dan Artuso

Hi,
How come you have concatenated the varaiable Me!CountyName on onto the string
but you have ignored the others?

updatesql = "UPDATE tblMultipleCounties SET tblMultipleCounties.Cnty = """ & Me!CountyName & _
""", tblMultipleCounties.County = """ & Me!CountyCode & _
""", tblMultipleCounties.[Updated?] = Yes " & _
"WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "));"

the rules are:
delimit stings with quotes
delimit dates with #
numbers require no delimiters
 
A

Ali

How come you have concatenated the varaiable Me!CountyName on onto the string
but you have ignored the others?

updatesql = "UPDATE tblMultipleCounties SET tblMultipleCounties.Cnty = """ & Me!CountyName & _
""", tblMultipleCounties.County = """ & Me!CountyCode & _
""", tblMultipleCounties.[Updated?] = Yes " & _
"WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "));"

the rules are:
delimit stings with quotes
delimit dates with #
numbers require no delimiters
Click to expand...

Thanks for your repsonse Dan - I should have told you what kind of
variables these are.

Me!CountyName is a string
Me!CountyCode is a number
[Updated?] is a boolean (so should that be delimited with quotes?)
Me.Parent.[LogNo] is a number.

So is this the correct syntax?

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = Me!CountyCode,
tblMultipleCounties.[Updated?] = 1,
WHERE (((tblMultipleCounties.[LogNumber])= Me.Parent.[LogNo]));"

I'm still getting a syntax error....
 
D

Douglas J. Steele

Ali said:
How come you have concatenated the varaiable Me!CountyName on onto the string
but you have ignored the others?

updatesql = "UPDATE tblMultipleCounties SET tblMultipleCounties.Cnty = """ & Me!CountyName & _
""", tblMultipleCounties.County = """ & Me!CountyCode & _
""", tblMultipleCounties.[Updated?] = Yes " & _
"WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "));"

the rules are:
delimit stings with quotes
delimit dates with #
numbers require no delimiters
Click to expand...

Thanks for your repsonse Dan - I should have told you what kind of
variables these are.

Me!CountyName is a string
Me!CountyCode is a number
[Updated?] is a boolean (so should that be delimited with quotes?)
Me.Parent.[LogNo] is a number.

So is this the correct syntax?

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = Me!CountyCode,
tblMultipleCounties.[Updated?] = 1,
WHERE (((tblMultipleCounties.[LogNumber])= Me.Parent.[LogNo]));"

I'm still getting a syntax error....
Click to expand...


You seem to have ignored Dan's comment about taking all references to
controls outside of the string:

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = -1,
WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "))"

or

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = True,
WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "))"


Of course, it does seem odd to me that you're storing both CountyName and
CountCode in the second table. Typically you'd only store the primary key of
the County table and link your tables together.
 
D

Douglas J. Steele

Douglas J. Steele said:
=
""" & Me!CountyName & _ &
_
""", tblMultipleCounties.[Updated?] = Yes " & _
"WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "));"

the rules are:
delimit stings with quotes
delimit dates with #
numbers require no delimiters
Click to expand...

Thanks for your repsonse Dan - I should have told you what kind of
variables these are.

Me!CountyName is a string
Me!CountyCode is a number
[Updated?] is a boolean (so should that be delimited with quotes?)
Me.Parent.[LogNo] is a number.

So is this the correct syntax?

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = Me!CountyCode,
tblMultipleCounties.[Updated?] = 1,
WHERE (((tblMultipleCounties.[LogNumber])= Me.Parent.[LogNo]));"

I'm still getting a syntax error....
Click to expand...


You seem to have ignored Dan's comment about taking all references to
controls outside of the string:

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = -1,
WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "))"

or

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = True,
WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "))"


Of course, it does seem odd to me that you're storing both CountyName and
CountCode in the second table. Typically you'd only store the primary key of
the County table and link your tables together.
Click to expand...

Oops. Just noticed you also have an unwanted comma before the word WHERE:

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = """ & Me!CountyName & """,
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = -1
WHERE (((tblMultipleCounties.[LogNumber])= " & Me.Parent.[LogNo] & "))"
 
A

Ali

Thanks for all the help! I just need more practice with quotes,
double quotes, single quotes and whatnot. The code that works -

updatesql = "UPDATE tblMultipleCounties
SET tblMultipleCounties.Cnty = '" & Me!CountyName & "',
tblMultipleCounties.County = " & Me!CountyCode & ",
tblMultipleCounties.[Updated?] = True
WHERE (((tblMultipleCounties.[Log Number])= " & Me.Parent.[LogNo] &
"));"

Ali
 
Top