database (for shoes)
---------
item_size(Article,size,stock,quantity)
item(Article,stock,price,st_q1,st_q2,st_q3,...,st_ q10, code_supplier)
Order(order_num,order_date,pers_code)
Order_detail(order_num,order_date,code_pers,articl e)
--------------
i did a form to enter the quantity to stock
that form contain order.order_num, order.order_date, order.pers_code, and a subform that contains:
order_detail.order_num, order_detail.order_date, order_detail.code_pers, order_detail.article, order_detail.quantity, order_detail.purchase_price, order_detail.q1, order_detail.q2,..., order_detail.q10, order_detail.SaleOrReturn item.stock, item.st_q1, item.st_q2,..., item.st_q10.
----------------
on the after update of the quantity i wrote this :
Expand|Select|Wrap|Line Numbers
- Private Sub quantity_AfterUpdate()
- If Me.SaleOrReturn.Value = 1 Then
- 'that means that i'm buying from the supplier
- Me.stock.Value = (Nz(Me.stock.Value, 0) - Me.quantity.OldValue)
- DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
- Me.stock.Value = (Nz(Me.stock.Value, 0) + Me.quantity.Value)
- Else ' Here i'm returning articles to the supplier
- Me.stock.Value = (Nz(Me.stock.Value, 0) + Me.quantity.OldValue)
- DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
- Me.stock.Value = (Nz(Me.stock.Value, 0) - Me.quantity.Value)
- End If
- End Sub
When the form load i can see the value of the stock for example 49 for the article1, but when i insert the value of the quantity for example (quantity=1) the value of the stock change also to 1 !! (But the true value should be 50 = 49+1)
and when i delete the record the field stock in the table item is updated to 0 !!
Any one can help please!! ??
--------------
I had also tried this code:
Expand|Select|Wrap|Line Numbers
- Me.stock.Value = (Me.stock.Value - Me.quantity.OldValue)
- DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
- Me.stock.Value = (Me.stock.Value + Me.quantity.Value)
- Else
- Me.stock.Value = (Me.stock.Value + Me.quantity.OldValue)
- DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
- Me.stock.Value = (Me.stock.Value - Me.quantity.Value)
-----------------
I now it's a long question but hope someone will help . Thanks! :)