[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

[Simply Me]

Im sorry for the change of topic title. I double check my code and UPDATE is actually updating but i cant display the updated value in my txtsupply.

[Static]

im not sure you can do it like that

UPDATE TblStocks SET Quantity = Quantity - " & Val(txtQty.Text) & "

if u already have the quantity in your app do the math and put the new value in.

UPDATE TblStocks SET Quantity = "Supply - Val(txtQty.Text) & "....

get it?

[westconn1]

is missing vbcode tags

pete

[Simply Me]

is missing vbcode tags

pete

I just edited my first post and added the vbcode tag

im not sure you can do it like that

it can be done and its working fine with me. Its just that i cant display the correct value in my textbox but when i checked my DB the value is actually added back.

[Static]

ok, well after the update, requery the field?
or just do the math in your app and change the field...

[Simply Me]

i did something like this and its not working.

sSQL = "UPDATE tblStocks SET Quantity = Quantity + " & txtQty.Text & " WHERE [Code]= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"
    oConn.Execute sSQL
    rsStocks.Requery

[Static]

are your text fields bound to rsStocks?

if not u have to reset then

txtWhatever = rs!whatever

[Simply Me]

in my code, everytime the change event of txtcode is triggered the txtsupply gets its value from my DB.

[Simply Me]

anyone here?

[Simply Me]

bump

[ganeshmoorthy]

I double check my code and UPDATE is actually updating but i cant display the updated value in my txtsupply

in my code, everytime the change event of txtcode is triggered the txtsupply gets its value from my DB.

I hope you want to display the updated qty in the txtSupply after the update query gets displayed...where are you requerying the value again after your update query...post the codes...

[Simply Me]

I hope you want to display the updated qty in the txtSupply after the update query gets displayed...where are you requerying the value again after your update query...post the codes...

I posted the code in post #7. I want to display the updated quantity in txtsupply.

[ganeshmoorthy]

could you post the full code...including the codes where you display the updated value to the text box...just requerying alone will not help you...if you have to code to display your records in txtCode_Change, call it again or write

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

[Simply Me]

the full code is in post #1.

[ganeshmoorthy]

if you are updating the records you must call the function where you are displaying the values...do you understand what i mean..

[westconn1]

in my code, everytime the change event of txtcode is triggered the txtsupply gets its value from my DB.

but when you update txtcode change is not triggered, so you need to trigger it or reload the textboxes

pete

[Simply Me]

i think it will be triggered as because if I double click my listview the textboxes (including txtcode) will be filled up by the records found in my listview and since it is filled up the change event is triggered. Correct me for my analysis if Im wrong.

[westconn1]

check it cos if it was working right you wouldn't have this problem

put a breakpoint in the txtcode change event to see if it is triggered

pete

[Simply Me]

I tried it thrice and yes it is being triggered. I have this code in dblclick event of the listview

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")

this is what is shown in my immediate window
UPDATE tblStocks SET Quantity = Quantity + 5 WHERE [Code]= '4806020497341'
75

at first the txtsupply shows 80 i put 5 in txtqty then i press the enter key so that makes the quantity in my DB =75. I double click now my listview and the records will display in my txtsupply should be 80 not 75 because the code above added back 5 to 75 but its not displaying the correct value.

[Simply Me]

any idea here please?

[Al42]

I tried it thrice and yes it is being triggered.

Put a breakpoint on the If txtCode.Text <> "" Then line. Once it triggers, follow it with F8 and see if txtSupply gets updated and, if it does, to what value. If it doesn't you'll see why.

[Simply Me]

thanks for all the reply! i got it working and thanks to erick widya for the solution.
And for those who would like to know the solution, I am again posting it here. Just update the DB first before displaying anything in the textbox. Relocating the SQL query will do the trick.
[Highlight=VB]Private Sub lstStocks_DblClick()
If lstStocks.ListItems.Count = 0 Then
Exit Sub
Else
edwin = lstStocks.SelectedItem.Index
sSQL = "UPDATE tblStocks SET Quantity = Quantity + " & lstStocks.ListItems.Item(edwin).SubItems(2) & " WHERE [Code]= '" & (lstStocks.ListItems.Item(edwin).Text) & "'"
oConn.Execute sSQL
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))
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