update code vba (sql server 2000 and access 2003)

[ashraf_al_ani via AccessMonster.com]

Dear All
I am using Sql server 2000 with Access 2003
I am tring to run an update by vba but i receive error 170 the code is as
follow:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] =
[forms]![muf]![muf_menu].[form]![item_no];"
DoCmd.Requery
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
---------------------------------------------------------------------
if i run the code as this
Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "UPDATE item SET item.balance = 100;"
DoCmd.Requery
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


it runs without any problem

but i want to update according to a value in the form

pls any idea??

waiting your responses

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and has nothing to do with MDB or ACCDB
database file and ODBC Linked Tables. While it's not forbidden to post
here per see, you could have a more accurate and timely answer by posting to
a more relevant newsgroup.

As to your question, you must build the full sql string before sending it by
concatenating together the various parts:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] =" &
[forms]![muf]![muf_menu].[form]![item_no] & ";"

If the value is a string, you must enclose it between quote; taking care of
the possibility of having embedded quote by doubling them:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ='" &
Replace ([forms]![muf]![muf_menu].[form]![item_no], "'", "''") & "';"

Possibly that you must also take into consideration the possibility of
having an empty string as the value of the control if the empty strings are
stored as null value - as it is often the case - instead of empty strings in
the database.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[ashraf_al_ani via AccessMonster.com]

Many Thanks For your Respons
the code is running ok but i have one problem

that if the final result i want to plus 1 with it or any other number how can
i deal with this problem

i mean the hole code +1 how can i write it

please waiting for your response

many thanks again

First, this newsgroup is about ADP and has nothing to do with MDB or ACCDB
database file and ODBC Linked Tables. While it's not forbidden to post
here per see, you could have a more accurate and timely answer by posting to
a more relevant newsgroup.

As to your question, you must build the full sql string before sending it by
concatenating together the various parts:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] =" &
[forms]![muf]![muf_menu].[form]![item_no] & ";"

If the value is a string, you must enclose it between quote; taking care of
the possibility of having embedded quote by doubling them:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ='" &
Replace ([forms]![muf]![muf_menu].[form]![item_no], "'", "''") & "';"

Possibly that you must also take into consideration the possibility of
having an empty string as the value of the control if the empty strings are
stored as null value - as it is often the case - instead of empty strings in
the database.

Dear All
I am using Sql server 2000 with Access 2003

[quoted text clipped - 24 lines]

waiting your responses

 

[Sylvain Lafontaine]

Sorry but I don't really understand your question. You'll have to come back
with an example of what you wan to do.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Many Thanks For your Respons
the code is running ok but i have one problem

that if the final result i want to plus 1 with it or any other number how
can
i deal with this problem

i mean the hole code +1 how can i write it

please waiting for your response

many thanks again

First, this newsgroup is about ADP and has nothing to do with MDB or ACCDB
database file and ODBC Linked Tables. While it's not forbidden to post
here per see, you could have a more accurate and timely answer by posting
to
a more relevant newsgroup.

As to your question, you must build the full sql string before sending it
by
concatenating together the various parts:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ="
&
[forms]![muf]![muf_menu].[form]![item_no] & ";"

If the value is a string, you must enclose it between quote; taking care
of
the possibility of having embedded quote by doubling them:

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ='"
&
Replace ([forms]![muf]![muf_menu].[form]![item_no], "'", "''") & "';"

Possibly that you must also take into consideration the possibility of
having an empty string as the value of the control if the empty strings
are
stored as null value - as it is often the case - instead of empty strings
in
the database.

Dear All
I am using Sql server 2000 with Access 2003

[quoted text clipped - 24 lines]

waiting your responses

 

[ashraf_al_ani via AccessMonster.com]

Sorry but I don't really understand your question. You'll have to come back
with an example of what you wan to do.

Many Thanks For your Respons
the code is running ok but i have one problem

[quoted text clipped - 43 lines]

Dear Sylvain

I tried to add a value to the result but I didnt get the correct result i
wrote as that:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] =" &
_
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1
DoCmd.Requery
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

every thing went ok except (+1)

please help me in this matter


best regards

 

[Sylvain Lafontaine]

You have put the +1 AFTER the semi-comma ";" so it has no effect on your
query.

It's also not clear what you want to do with this +1: do you want to add it
to the item.balance field or to change the value of ITEM_NO in the WHERE
part of the Update query?

Try one of these two:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1


strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ="
& _
([Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] + 1) & ";"

You might have to do some conversion between Int and String with the second
example, I don't remember exactly.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Sorry but I don't really understand your question. You'll have to come
back
with an example of what you wan to do.

Many Thanks For your Respons
the code is running ok but i have one problem

[quoted text clipped - 43 lines]

waiting your responses

Dear Sylvain

I tried to add a value to the result but I didnt get the correct result i
wrote as that:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "UPDATE item SET item.balance = balance + qty where [item_no]
=" &
_
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1
DoCmd.Requery
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

every thing went ok except (+1)

please help me in this matter


best regards

 

[ashraf_al_ani via AccessMonster.com]

Dear Sylvain

Many thanks for your helps

the (+1) that i need is to add a value for the balance but still didnt work

how can i add this value (1) to the code

please help

best regards

You have put the +1 AFTER the semi-comma ";" so it has no effect on your
query.

It's also not clear what you want to do with this +1: do you want to add it
to the item.balance field or to change the value of ITEM_NO in the WHERE
part of the Update query?

Try one of these two:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ="
& _
([Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] + 1) & ";"

You might have to do some conversion between Int and String with the second
example, I don't remember exactly.
[quoted text clipped - 25 lines]

best regards

 

[ashraf_al_ani via AccessMonster.com]

Dear Sylvain

Many thanks for your helps

the (+1) that i need is to add a value for the balance but still didnt work

how can i add this value (1) to the code

please help

best regards

You have put the +1 AFTER the semi-comma ";" so it has no effect on your
query.

It's also not clear what you want to do with this +1: do you want to add it
to the item.balance field or to change the value of ITEM_NO in the WHERE
part of the Update query?

Try one of these two:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no] ="
& _
([Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] + 1) & ";"

You might have to do some conversion between Int and String with the second
example, I don't remember exactly.
[quoted text clipped - 25 lines]

best regards

 

[Sylvain Lafontaine]

I'm sorry to say that but I still don't understand what you want to do
exactly with this (+1). Please post back an example of data before and
after the update so that I can understand the meaning of this +1.

Also, there is an error with the first of my two previous examples. This
one should have been written without the +1 at the end:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";"

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Dear Sylvain

Many thanks for your helps

the (+1) that i need is to add a value for the balance but still didnt
work

how can i add this value (1) to the code

please help

best regards

You have put the +1 AFTER the semi-comma ";" so it has no effect on your
query.

It's also not clear what you want to do with this +1: do you want to add
it
to the item.balance field or to change the value of ITEM_NO in the WHERE
part of the Update query?

Try one of these two:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";" +1

strSQL = "UPDATE item SET item.balance = balance + qty where [item_no]
="
& _
([Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] + 1) & ";"

You might have to do some conversion between Int and String with the
second
example, I don't remember exactly.

Sorry but I don't really understand your question. You'll have to come
back

[quoted text clipped - 25 lines]

best regards

 

[ashraf_al_ani via AccessMonster.com]

Dear Sylvain

I will tell you what i want

in the store system

when a user enterd a qty for an item (A) this code will run on after update
event in the form

Let us suppose that the user enterd 10 of item (A) then the balance now is
(10 of item A)

then the user discovered that this number was wrong so he changed the value
10 to 7 of item (A)

so he executed this event again so the balance will changed to 17 of item A

(the event occurs after each update so the value will be changed and thats
wrong)
so how can i deal with this problem so i can keep the balance in the last
value of the qty that the user
entered

is there any way to solve this problem

or how can i send the system and it is made in sql server 2000 and ACCESS
2003 (ADP PROJECT)

I'm sorry to say that but I still don't understand what you want to do
exactly with this (+1). Please post back an example of data before and
after the update so that I can understand the meaning of this +1.

Also, there is an error with the first of my two previous examples. This
one should have been written without the +1 at the end:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";"

Dear Sylvain

[quoted text clipped - 37 lines]

 

[Sylvain Lafontaine]

Sorry for the delay but I still don't understand what you want to do.

However, after reading a couple of times your last post, it looks like that
what you're asking for if much more than the simple help for getting right
the syntax of a single sql statement. If you have trouble with the overall
logic and design of your database, that's a whole new story.

In all cases, converting your application to an ADP project will not change
anything to your problem and may even make things worse because working a
SQL-Server is a little more complicated than working against an Access JET
database. Switching to SQL-Server won't simply solves your problems
magically.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Dear Sylvain

I will tell you what i want

in the store system

when a user enterd a qty for an item (A) this code will run on after
update
event in the form

Let us suppose that the user enterd 10 of item (A) then the balance now is
(10 of item A)

then the user discovered that this number was wrong so he changed the
value
10 to 7 of item (A)

so he executed this event again so the balance will changed to 17 of item
A

(the event occurs after each update so the value will be changed and thats
wrong)
so how can i deal with this problem so i can keep the balance in the last
value of the qty that the user
entered

is there any way to solve this problem

or how can i send the system and it is made in sql server 2000 and ACCESS
2003 (ADP PROJECT)

I'm sorry to say that but I still don't understand what you want to do
exactly with this (+1). Please post back an example of data before and
after the update so that I can understand the meaning of this +1.

Also, there is an error with the first of my two previous examples. This
one should have been written without the +1 at the end:

strSQL = "UPDATE item SET item.balance = (balance + qty + 1) where
[item_no] =" & _
[Forms]![muf]![MUF_MENU].[Form]![ITEM_NO] & ";"

Dear Sylvain

[quoted text clipped - 37 lines]

best regards