Updating table using a form?

[kanav21]

Hi Everybody, I am hoping somebody will be able to help me with this..

I have a simple table called Product - This table has 5 fields:
1. Design_Code (this is the primary key)
2. Original_Qty
3. Qty_Sold
4. Qty_Available
5. Price


I also have a Form which has a ListBox that gets its values from the Design_Code field.

I also have a CommandButton.

What I am trying to do is...every time the CommandButton is pressed, I want one unit to be deducted from the Qty_Available field for the value which is selected in the ListBox. And one Unit to be added to the Qty_Sold field.

Can someone please tell me the best way to do this?

Thanks in advance for your help.

Cheers

[Scott Price]

Hi Everybody, I am hoping somebody will be able to help me with this..

I have a simple table called Product - This table has 5 fields:
1. Design_Code (this is the primary key)
2. Original_Qty
3. Qty_Sold
4. Qty_Available
5. Price


I also have a Form which has a ListBox that gets its values from the Design_Code field.

I also have a CommandButton.

What I am trying to do is...every time the CommandButton is pressed, I want one unit to be deducted from the Qty_Available field for the value which is selected in the ListBox. And one Unit to be added to the Qty_Sold field.

Can someone please tell me the best way to do this?

Thanks in advance for your help.

Cheers

A fairly simple UPDATE query should do the trick. You will call this with your command button's OnClick event.

Dim MySQL
'Test to make sure something really is selected in the ListBox
If Not IsNull(Me![ListBoxName]) Then 
MySQL = "UPDATE Products SET [Qty_Available] = ([Qty_Available] - 1),  Qty_Sold] = ([Qty_Sold] + 1) WHERE [Design_Code] =" & Me![ListBoxName] & ";"
DoCmd.RunSQL MySQL
End If

You'll likely want to add some error handling, and make sure to change the Names inside []'s to reflect the actual names in your database.

Regards,
Scott