How to deduct sold item from quantity in access database. VB6.0

[gbenga]

am developing a project point of sales in VB6.0. I need a code to deduct the sold item from the data base . each time they make sale it will automatically deduct from the quantity in the database and also show stock level is low when the item quantity in the database is less than 10. i have 18 items in the database. my code could only work for one item it doesn't work for the rest .
Thanks.

Private Sub cmdsave_Click()
Dim sql As String
For i = 0 To List1.ListCount
rstRecordSet.Open "INSERT INTO Salesrecord VALUES ('" & List1.List(i) & "', '" & List2.List(i) & "', '" & List3.List(i) & "','" & List4.List(i) & "','" & Text1.Text & "')", conConnection
   Next i
   MsgBox ("Record Added")
sql = "update Stock set productquantity = productquantity - " & TxtQty.Text & "   where id =  'F0001'"""
sql = "UPDATE Stock SET Productquantity =   productquantity  - " & TxtQty.Text & "   WHERE ProductID = 'F0002 '"
 
If productQuantity <= 10 Then
MsgBox ("Stock Level is Low")
 
conConnection.Execute sql
 
End If
End Sub

[Mikkeee]

Instead of using the recordset to insert use your connection.

For i = 0 To List1.ListCount
   conConnection.Execute "INSERT INTO Salesrecord VALUES ('" & List1.List(i) & "', '" & List2.List(i) & "', '" & List3.List(i) & "','" & List4.List(i) & "','" & Text1.Text & "')"
Next i