Cann't update the value in table

  • Thread starter Thread starter Dou
  • Start date Start date
D

Dou

I use the following SQL to update the QtyOnOrder in table SpareParts, but it
cann't.
the QtyOnOrder is single type.

n=8

tbCSN='8210401002'

MySQL = "UPDATE SpareParts SET QtyOnOrder=" & n & " WHERE CSN='" &
tbCSN & "';"

MyDB.Execute (MySQL)

Don't have any error message, How to solve this problem? Thanks
 
Just to make sure: Is [CSN] of Text type or Numeric data type?

The SQL syntax looks correct for Text data type ...
 
I use the following SQL to update the QtyOnOrder in table SpareParts, but it
cann't.
the QtyOnOrder is single type.

n=8

tbCSN='8210401002'

MySQL = "UPDATE SpareParts SET QtyOnOrder=" & n & " WHERE CSN='" &
tbCSN & "';"

MyDB.Execute (MySQL)

Don't have any error message, How to solve this problem? Thanks
Click to expand...

Are you absolutely certain that there is a record for CSN 8210401002 in the
table? Is CSN a Text datatype (it should be)? Might it contain leading or
trailing blanks or zeroes?

John W. Vinson [MVP]
 
Yes, The CSN is Text type, and there are records for all CSN, The SQL cann't
update the value for every CSN, but I can use query to update it. Why I
cann't use SQL to update QtyOnOrder's value?
 
Are you saying that you are trying to update the value of the QtyOnOrder for
all Records with diiferent CSNs with the SQL String you posted originally?
 
Yes, but it cann't update the value.

Van T. Dinh said:
Are you saying that you are trying to update the value of the QtyOnOrder for
all Records with diiferent CSNs with the SQL String you posted originally?
Click to expand...
 
The posted SQL has a WHERE clause that restricts its ability to update all
records???

Furthermore, one you update records with CSN='8210401002', subsequent
executions of the SQL will update the records (that match the criteria)
except that they had been updated previously so the update (to the same
value) won't be noticeable and there is no error message.

I think we may have communication problem and I might not have the whole
picture. However, you should check Access Help for more information
regarding Update Queries and criteria / WHERE clause.
 
I don't update all record at same time, ervery time the SQL only udpate a
record's QtyOnOrder when enter diffrent CSN.
 
I don't update all record at same time, ervery time the SQL only udpate a
record's QtyOnOrder when enter diffrent CSN.
Click to expand...

That is *exactly* what I would expect to happen given this query. You are
telling Access to update the QtyOnOrder for one specific CSN. That's what it's
doing.

If you want to update the QtyOnOrder for multiple records in the table, then
you need a different query. I do not know what that query would be since I
don't know the structure of your database, and I don't know how you would
determine which item should be updated by which amount - surely you wouldn't
want to subtract 8 units from the inventory of every item in your warehouse!?

John W. Vinson [MVP]
 
This is my code:

dim rs as DAO.Recordset
dim n as Single

Set rs = MyDB.OpenRecordset("SELECT QtyOnOrder FROM SpareParts WHERE
CSN='" & Me!tbCSN & "';")

n = rs!QtyOnOrder - Me!tbRequireQty

MySQL = "UPDATE SpareParts " & _
"SET QtyInStock=" & n & " " & _
"WHERE CSN='" & Trim(tbCSN) & "';"

MyDB.Execute (MySQL)

rs.Close
Set rs = Nothing

SpareParts table's structure:

CSN text 20
PartName text 100
PartNo text 50
Model text 50
QtyInStock Single
QtyOnOrder Single
VendorCode Integer
 
This is my code:

dim rs as DAO.Recordset
dim n as Single

Set rs = MyDB.OpenRecordset("SELECT QtyOnOrder FROM SpareParts WHERE
CSN='" & Me!tbCSN & "';")

n = rs!QtyOnOrder - Me!tbRequireQty

MySQL = "UPDATE SpareParts " & _
"SET QtyInStock=" & n & " " & _
"WHERE CSN='" & Trim(tbCSN) & "';"

MyDB.Execute (MySQL)

rs.Close
Set rs = Nothing
Click to expand...

Assuming that the form is named MyForm, try


Set rs = MyDB.OpenRecordset("SELECT QtyOnOrder FROM SpareParts WHERE
CSN='" & Me!tbCSN & "';")

MySQL = "UPDATE SpareParts " & _
"SET QtyInStock=qtyOnOrder - [Forms]![MyForm]![tbRequireQty] " & _
"WHERE CSN='" & Trim(tbCSN) & "';"

MyDB.Execute (MySQL)

You can simply reference the form control directly; no need to mess with the
recordset or looping.

John W. Vinson [MVP]
 
Back
Top