Update Syntax Error on SET

[Les]

I am trying to use the following code to update firld "numQuantity" but
although it runs through and I am picking up the correct value in
"intTotalQty" it does not change the table values.

Any advise please?

Les


Dim strsql As String
Dim Totalit As String

If DCount("[strProduct_Code]", "tblOrder_details", "[strProduct_Code] = '" &
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity" = intTotalQty & _
"WHERE strProduct_Code = strProduct"

 

[Mattias Jonsson]

Les,
You have to execute the SQL string to have it be reflected in the database

Add a row that says something like:
currentdb.execute strsql

And add error handling. Hope this helps.

Thanks,
Mattias Jonsson

 

[Les]

Mattias thanks for that.

I am now using the folloeing and getting a "Too Few Perameters Error
(Expected 2)" message any ideas?

strsql = "UPDATE tblOrder_details SET numQuantity = intTotalQty WHERE
strProduct_Code = strProduct"

CurrentDb.Execute strsql, dbFailOnError

Les,
You have to execute the SQL string to have it be reflected in the database

Add a row that says something like:
currentdb.execute strsql

And add error handling. Hope this helps.

Thanks,
Mattias Jonsson

I am trying to use the following code to update firld "numQuantity" but
although it runs through and I am picking up the correct value in
"intTotalQty" it does not change the table values.

Any advise please?

Les


Dim strsql As String
Dim Totalit As String

If DCount("[strProduct_Code]", "tblOrder_details", "[strProduct_Code] =

'"

&
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity" = intTotalQty & _
"WHERE strProduct_Code = strProduct"

 

[Douglas J. Steele]

Assuming intTotalQty and strProduct are variables in your routine (and that
intTotalQty is numeric, while strProduct is text), you need to have them
outside of the quotes when you're creating strsql:

strsql = "UPDATE tblOrder_details SET numQuantity = " & intTotalQty & "
WHERE
strProduct_Code = '" & strProduct & "'"

Note the space before WHERE, and quotes around strProduct. To make the
latter more explicitly, here's the end of the statement repeated with extra
spaces added:

" WHERE strProduct_Code = ' " & strProduct & " ' "


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Mattias thanks for that.

I am now using the folloeing and getting a "Too Few Perameters Error
(Expected 2)" message any ideas?

strsql = "UPDATE tblOrder_details SET numQuantity = intTotalQty WHERE
strProduct_Code = strProduct"

CurrentDb.Execute strsql, dbFailOnError

Les,
You have to execute the SQL string to have it be reflected in the database

Add a row that says something like:
currentdb.execute strsql

And add error handling. Hope this helps.

Thanks,
Mattias Jonsson

I am trying to use the following code to update firld "numQuantity" but
although it runs through and I am picking up the correct value in
"intTotalQty" it does not change the table values.

Any advise please?

Les


Dim strsql As String
Dim Totalit As String

If DCount("[strProduct_Code]", "tblOrder_details", "[strProduct_Code]

=

'"

&
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity" = intTotalQty & _
"WHERE strProduct_Code = strProduct"

 

[Les]

Douglas thats done it thanks for that!

I always get confused when to use and not to use " or ' can you recommend
where I can look this up I have tried numerous books and searching the web
without finding any specific guide lines.

Thanks

Les

Assuming intTotalQty and strProduct are variables in your routine (and that
intTotalQty is numeric, while strProduct is text), you need to have them
outside of the quotes when you're creating strsql:

strsql = "UPDATE tblOrder_details SET numQuantity = " & intTotalQty & "
WHERE
strProduct_Code = '" & strProduct & "'"

Note the space before WHERE, and quotes around strProduct. To make the
latter more explicitly, here's the end of the statement repeated with extra
spaces added:

" WHERE strProduct_Code = ' " & strProduct & " ' "


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Mattias thanks for that.

I am now using the folloeing and getting a "Too Few Perameters Error
(Expected 2)" message any ideas?

strsql = "UPDATE tblOrder_details SET numQuantity = intTotalQty WHERE
strProduct_Code = strProduct"

CurrentDb.Execute strsql, dbFailOnError

Les,
You have to execute the SQL string to have it be reflected in the database

Add a row that says something like:
currentdb.execute strsql

And add error handling. Hope this helps.

Thanks,
Mattias Jonsson


I am trying to use the following code to update firld "numQuantity" but
although it runs through and I am picking up the correct value in
"intTotalQty" it does not change the table values.

Any advise please?

Les


Dim strsql As String
Dim Totalit As String

If DCount("[strProduct_Code]", "tblOrder_details",

"[strProduct_Code]

=

'"
&
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity" = intTotalQty & _
"WHERE strProduct_Code = strProduct"

 

[Les]

Douglas

Can you please give me the correct syntax for the following to make it an
Update rather than Insert

Thanks

Les

strsql = "Insert Into tblProduct_List (Product,Description,Gross) Values ('"
& [strProduct_Code] _
& "','" & strProduct_Description _
& "','" & curRetail_Price & "')"

Douglas thats done it thanks for that!

I always get confused when to use and not to use " or ' can you recommend
where I can look this up I have tried numerous books and searching the web
without finding any specific guide lines.

Thanks

Les

Assuming intTotalQty and strProduct are variables in your routine (and that
intTotalQty is numeric, while strProduct is text), you need to have them
outside of the quotes when you're creating strsql:

strsql = "UPDATE tblOrder_details SET numQuantity = " & intTotalQty & "
WHERE
strProduct_Code = '" & strProduct & "'"

Note the space before WHERE, and quotes around strProduct. To make the
latter more explicitly, here's the end of the statement repeated with extra
spaces added:

" WHERE strProduct_Code = ' " & strProduct & " ' "


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Mattias thanks for that.

I am now using the folloeing and getting a "Too Few Perameters Error
(Expected 2)" message any ideas?

strsql = "UPDATE tblOrder_details SET numQuantity = intTotalQty WHERE
strProduct_Code = strProduct"

CurrentDb.Execute strsql, dbFailOnError



Les,
You have to execute the SQL string to have it be reflected in the database

Add a row that says something like:
currentdb.execute strsql

And add error handling. Hope this helps.

Thanks,
Mattias Jonsson


I am trying to use the following code to update firld

"numQuantity"
but

although it runs through and I am picking up the correct value in
"intTotalQty" it does not change the table values.

Any advise please?

Les


Dim strsql As String
Dim Totalit As String

If DCount("[strProduct_Code]", "tblOrder_details",

"[strProduct_Code]

=
'"
&
Forms![frmOrder_Form]![strProductCode] & "' ") > 0 Then
intTotalQty = DLookup("[numQuantity]", "tblOrder_Details",
"[strProduct_Code] = Forms!frmOrder_Form!strProduct") + [numNumber]

strsql = "UPDATE tblOrder_details SET numQuantity" = intTotalQty

&

_

 

[Mattias Jonsson]

Les,
Basic format is like this

Update Table [TableName]
Set Column=[NewValue]
Where SomeID=[IDToUpdate]

The Where part is very important - if you leave it out, you will update all
rows in the table. Access is not very forgiving when it comes to things like
that. Backup your table before you test.

Hope that helps,
Mattias