Need help on my code

[twistercool]

I WAS CREATING AN INVENTORY SYSTEM USING VB 6 AND ADODC. HERE IS MY CODE

Private Sub Command1_Click()
Dim temp2 As Integer
Dim temp As Integer
If Text1.Text = 0 Then error.Show Else If Text1.Text > 0 Then purchase.Show
Form38.Hide

Set rs = New ADODB.Recordset

rs.Open "select *from purchasetable", cn, adOpenKeyset, adLockOptimistic

    temp = 100
    temp2 = Val(rs!Remaining)
    rs.AddNew
    rs!Name = Label6.Caption
    rs!Quantity = Text1.Text
    rs!Total = Label5.Caption
    rs!MAXstock = 100
    rs!Remaining = temp - Val(Text1.Text)
   
    rs.Update
   
End Sub

Private Sub Command2_Click()
Form1.Show
End Sub

Private Sub Text1_Change()
Label5.Caption = Val(Text1.Text) * Val(Label4.Caption)
End Sub

PURCHASETABLE

ID - PRODUCT ID
ITEM - THE ITEM TO BE PURCHASED
QUANTITY - THE QUANTITY OF ITEM TO BE PURCHASED
MAXSTOCK - THE MAXIMUM STOCK OF ITEMS
REMAINING - THE REMAINING STOCK AFTER EACH PURCHASED

ISSUE: AN ATTACHED SCREENSHOT OF THE DATABASE TABLE ASSOCIATED WITH THIS CODE. THE PROBLEM IS EVERY TIME A PURCHASE IS MADE, AN ITEM REGARDLESS OF ANY QUANTITY THE REMAINING ON THE TABLE WOULD ALWAYS BE DEDUCTED TO MAXSTOCK ROW OF 100. WHEN THE REMAINING SHOULD BE DEDUCTED FROM THE THE QUANTITY(THAT IS QUANTITY OF ITEMS PURCHASED). COULD HELP ME OUT WITH MY CODE OR COULD YOU HELP ME REWRITE THEM TO WORK

[Jx_Man]

Don't put Remaining items on purchasing table.
Put it on their own table. (e.g Reebok, Adidas,..)
So you just update Stock Field in related table (adidas or reebok, etc) every transactions recorded in table purchase.

[twistercool]

Don't put Remaining items on purchasing table.
Put it on their own table. (e.g Reebok, Adidas,..)
So you just update Stock Field in related table (adidas or reebok, etc) every transactions recorded in table purchase.

thank you could you please give me an example of how it is done? this system am doing is for my cousin's project and am not actually a vb developer

[twistercool]

Don't put Remaining items on purchasing table.
Put it on their own table. (e.g Reebok, Adidas,..)
So you just update Stock Field in related table (adidas or reebok, etc) every transactions recorded in table purchase.

Can you please give a short example of how the relationship table would be made and what would be the formula so as make the remaining item updating every time a quantity is deducted and the max stock is 100

[Cruize_Invades]

rs.Open "select *from purchasetable", cn, adOpenKeyset, adLockOptimistic

    temp = 100
    temp2 = Val(rs!Remaining)
    rs.AddNew
    rs!Name = Label6.Caption
    rs!Quantity = Text1.Text
    rs!Total = Label5.Caption
    rs!MAXstock = 100
    rs!Remaining = temp - Val(Text1.Text)
   
    rs.Update
   
End Sub

you are subtracting temp(100) to the value of your text1.text
should it be temp2(your remaining items) minus the value in text1.text?

[twistercool]

rs.Open "select *from purchasetable", cn, adOpenKeyset, adLockOptimistic

    temp = 100
    temp2 = Val(rs!Remaining)
    rs.AddNew
    rs!Name = Label6.Caption
    rs!Quantity = Text1.Text
    rs!Total = Label5.Caption
    rs!MAXstock = 100
    rs!Remaining = temp - Val(Text1.Text)
   
    rs.Update
   
End Sub

you are subtracting temp(100) to the value of your text1.text
should it be temp2(your remaining items) minus the value in text1.text?

thanks! i will try that. Hope the remaining would not always subtract from 100 but from the latest transaction deducted from quantity item starting from 100 max stock

[ChrisPadgham]

as jx_man said you need to hold the amount remaining somewhere else, ie a different table. create a stock table that holds the remaining stock for each item. you need to read this for the item concerned, subtract the amount sold and update it.

[twistercool]

could you help me please atleast with a simple diagram or flowchart if possible

[Jx_Man]

what do you mean with simple diagram or flowchart?

[ChrisPadgham]

see the attached ER diagram, you will obviously need to add more fields to make it complete

[twistercool]

Thanks ChrisPadgham