Update Query problem (Updates all records not just selected.)

[johntyyk3]

Hey all,

I have a problem with my update query (SQL),

The situation where it is used:

The SQL code is as a command on a button on a form. It is used to take the value in the text box(Name: Restock) and update it to the Quantity field in the table. (tblStock)

The problem is that the Update, updates all the records changing all the values in the Column (Quantity) to the value that is in the text box.

I need it to update the record which is displayed in the form not all the records.

Here is the SQL at the minute.

DoCmd.RunSQL ("UPDATE tblStock SET Quantity = (Restock) WHERE ItemID = [ItemID]")

Any help would be great. Thanks in advance

 

[edtab]

Try:

DoCmd.RunSQL ("UPDATE tblStock SET Quantity = Me.[Restock] WHERE ItemID = Me.[ItemID]")

Assumes that "Restock" and "ItemID" are both textboxes in your current form.

 

[johntyyk3]

No Luck...

I have had no luck with getting this to work.

It is basically to update a specific record in tblStock with a new value that is in a calculated field in a form.

On the form it has the key field ItemID that is unique to every record.
The calculated field is called Restock
and the Column i want to up update is tblStock , Quantity

Any more suggestions

Johnty

 

[RV]

Try

DoCmd.RunSQL ("UPDATE tblStock SET Quantity = " & Me.Restock & " WHERE ItemID = " & Me.ItemID & "")

assuming Restock and ItemID are numeic strings.

RV

 

[johntyyk3]

Thanx..

Yep this has worked, I found the problem the ItemID column was not numeric.

Thanx for all your help, reguarding another issue... is there a piece of vba code to clear a text field after the update query has been done?

Thanx in advance.

Johnty