[RESOLVED] What's missing?

[Simply Me]

I have following textboxes: txtcode,txtsupply, txtdesc,and txtQty. I enter a code in txtcode and automatically the txtdesc will display item description and also the txtsupply will display the supply available (I'm getting the value from DB).

I will type quantity to be sold in the txtQty and then click the additem button to add/display the said item in list of items to be sold using a listview. I have codes in my listview to allow me to double click any item for editing purposes (change the quantity to be sold i.e. from 5 and make it 2 or from 5 make it 10).

Now my concerns is this when I double click an item the txtsupply value is not adding back 5 to the DB which should be reflected in txtsupply.

Example:
Supply = 105
quantity to be sold = 5

so when I click additem automatic 5 will be subracted from number of supply and become 100.

I will double click the item in the listview txtsupply will still be 100 But supposedly it should display 105.

What am i missing with my codes?

Private Sub txtCode_Change()
  txtQty.Text = ""
  txtDiscount.Text = ""
 
  If InStr(1, txtCode.Text, "*") = Len(txtCode.Text) Then
    txtQty.SetFocus
  End If
 
  If rsStocks.State = adStateOpen Then rsStocks.Close
 
On Error Resume Next
  If txtCode.Text <> "" Then
    rsStocks.Open "SELECT * FROM tblStocks WHERE Code = '" & txtCode.Text & "'", oConn, adOpenStatic, adLockOptimistic
    If rsStocks.RecordCount <> 0 Then
      txtSupply.Text = rsStocks.Fields("Quantity")
      txtDesc.Text = rsStocks.Fields("ProductDescription")
      'txtSellingPrice.Text = Format$(rsStocks.Fields("SellingPrice"), "P###,###,##0.00")
    Else
      txtSupply.Text = ""
      txtDesc.Text = ""
      'txtSellingPrice.Text = ""
      txtSubTotal.Text = ""
    End If
 Else
    txtSupply.Text = ""
    txtDesc.Text = ""
    'txtSellingPrice.Text = ""
    txtSubTotal.Text = ""
  End If
End Sub
 
 
Private Sub lstStocks_DblClick()
  If lstStocks.ListItems.Count = 0 Then
    Exit Sub
  Else
    edwin = lstStocks.SelectedItem.Index
    txtCode.Text = lstStocks.ListItems.Item(edwin).Text
    txtDiscount.Text = Val(lstStocks.ListItems.Item(edwin).SubItems(4)) '/ Val(lstStocks.ListItems.Item(edwin).SubItems(2))
    txtQty.Text = lstStocks.ListItems.Item(edwin).SubItems(2)
    txtSellingPrice.Text = Val(lstStocks.ListItems.Item(edwin).SubItems(3))
    'iQtyTemp = txtQty.Text
    
    sSQL = "UPDATE tblStocks SET Quantity = Quantity + " & txtQty.Text & " WHERE [Code]= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"
    oConn.Execute sSQL
    Debug.Print sSQL
    Debug.Print rsStocks.Fields("quantity")
    lstStocks.ListItems.Remove (edwin)
    txtCode.Locked = True
    
    cAmountDueTemp = 0
    For i = 1 To lstStocks.ListItems.Count
      cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(5))
      txtAmountDue.Text = cAmountDueTemp
    Next
    lstStocks.Enabled = False
  End If
End Sub
 
 
Private Sub cmdAddItem_Click()
  Dim lst As ListItem, itemFound As ListItem
  
  If txtCode.Locked = True Then
    txtCode.Locked = False
  End If
  
  If lstStocks.Enabled = False Then
    lstStocks.Enabled = True
  End If
  
  'If rsStocks.State = adStateOpen Then rsStocks.Close
  'rsStocks.Open "SELECT * FROM tblStocks WHERE Code = '" & txtCode.Text & "'", oConn, adOpenStatic, adLockOptimistic
     
  '=====================================================================
  '= The above commented code is the same with the enclosed code below =
  '=====================================================================
  '####################################################################
  sSQL = "SELECT * FROM tblStocks WHERE [Code] = '" & txtCode.Text & "'"
 
  If rsStocks.State = adStateOpen Then rsStocks.Close
  rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  '#####################################################################
  If CheckNullValue = False Then Exit Sub
  If Compare = False Then Exit Sub 'or If Not Compare Then Exit Sub
  
  If Val(txtQty.Text) > Val(txtSupply.Text) Then
    Call msgSupply
    txtQty.SetFocus
    SendKeys "{home}+{end}"
    Exit Sub
  'End If
  
  ElseIf txtQty.Text <> 0 Then
  'Set itemFound = lstStocks.FindItem(txtCode.Text)
  'If itemFound Is Nothing Then
    cAmountDueTemp = 0
    If txtDiscount.Text = "" Then
      txtDiscount.Text = 0
'      cSubtotal = (val(txtQty.Text) * CCur(rsStocks.Fields("SellingPrice")))
      cSubtotal = (Val(txtQty.Text) * Val(txtSellingPrice.Text))
      cMargin = (Val(txtSellingPrice.Text) * Val(txtQty.Text)) - (rsStocks.Fields("PurchasePrice") * Val(txtQty.Text))
    Else
      'iTempSubTotal = (Val(txtQty.Text) * Val(rsStocks.Fields("SellingPrice"))) - (Val(txtQty.Text) * (Val(txtDiscount.Text)))
'      cSubtotal = (val(txtQty.Text) * val(rsStocks.Fields("SellingPrice"))) - (Val(txtQty.Text) * (val(txtDiscount.Text))) 'Val(iTempSubTotal))
      cSubtotal = (Val(txtQty.Text) * Val(txtSellingPrice.Text)) - (Val(txtQty.Text) * (Val(txtDiscount.Text))) 'Val(iTempSubTotal))
      cMargin = (Val(txtSellingPrice.Text) * Val(txtQty.Text)) - Val(txtQty.Text) * Val(txtDiscount.Text) - (rsStocks.Fields("PurchasePrice") * Val(txtQty.Text))
    End If
 
    'rsStocks.Fields("Quantity") = (Val(rsStocks.Fields("Quantity") + Val(iQtyTemp)) - Val(txtQty.Text))
    'rsStocks.Update 'Update the database
    
    'Check if it's time to order the item
    If rsStocks.Fields("Quantity") <= rsStocks.Fields("ReOrder") Then
      MsgBox rsStocks.Fields("ProductDescription") & " Product" & _
      Chr(13) & "has reached its Re-order Point", vbInformation, ProgName
      txtQty.SetFocus
    End If
 
'    sSQL = "UPDATE TblStocks SET Quantity = Quantity + " & iQtyTemp & " - " & Val(txtQty.Text) & " WHERE [Code]='" & txtCode.Text & "'"
    sSQL = "UPDATE TblStocks SET Quantity = Quantity  - " & Val(txtQty.Text) & " WHERE [Code]='" & txtCode.Text & "'"
    oConn.Execute sSQL
    
    Set lst = lstStocks.ListItems.Add(, , txtCode.Text) 'DISPLY IN LISTVIEW
      lst.SubItems(1) = txtDesc.Text
      lst.SubItems(2) = txtQty.Text
'      lst.SubItems(3) = Format$(rsStocks.Fields("SellingPrice"), "###,###,##0.00")
      lst.SubItems(3) = Format$(Val(txtSellingPrice.Text), "###,###,##0.00")
      lst.SubItems(4) = Format$(Val(txtDiscount.Text), "###,###,##0.00")
      lst.SubItems(5) = Format$(CCur(cSubtotal), "###,###,##0.00")
      lst.SubItems(6) = Format$(rsStocks.Fields("PurchasePrice"), "###,###,##0.00")
      lst.SubItems(7) = Format$(CCur(cMargin))
    
    txtCode.Text = ""
    txtDiscount.Text = ""
    txtQty.Text = ""
    txtSellingPrice.Text = ""
    txtCode.SetFocus
  
    For i = 1 To lstStocks.ListItems.Count
      cAmountDueTemp = CCur(cAmountDueTemp) + CCur(lstStocks.ListItems.Item(i).SubItems(5))
      txtAmountDue.Text = Format$(cAmountDueTemp, "###,###,##0.00")
    Next
  Else
    MsgBox "Quantity must not be zero", vbInformation, ProgName
    txtQty.SetFocus
    SendKeys "{home}+{end}"
  End If
  
  Set lst = Nothing 'destroy the list
 
'  Else
'    MsgBox "Product code already exists in the list.", vbInformation
'    txtCode.Text = ""
'    txtDiscount.Text = ""
'    txtQty.Text = ""
'  End If
 
End Sub