0

I need to deduct a quantity from a table by matching the ID of the product. If I run the below code, it deducts a quantity but the problem is for instance...

I enter ID number as 1 and have to click deduct button twice to deduct.
Second time I change ID number to 2 and click deduct button... First time it deducts from ID number 1 itself. But now if I click once more, it deducts from ID number 2.

How can I solve this problem?
I'm using MS Access as database (ADODB).

Option Explicit
Dim con As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String

Private Sub Command1_Click()
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

con.Open (adodc1.ConnectionString)
sql = "UPDATE stock SET QTY = QTY - 1 WHERE ID = '" & IDval.Text & "'"
con.Execute (sql)
Adodc1.Recordset.Update
Adodc1.Refresh
con.Close
End Sub

Thank you in advance.

GSerg
  • 73,524
  • 17
  • 153
  • 317
Rodney Mathew
  • 61
  • 10
  • Try closing `con` before updating `adodc`. Also consider [removing the parentheses](http://stackoverflow.com/a/10262247/11683) and [meeting Bobby Tables](http://stackoverflow.com/q/332365/11683). – GSerg Oct 05 '14 at 15:28
  • Thank you GSerg! It's working! – Rodney Mathew Oct 05 '14 at 17:25

0 Answers0