Stock update or maintenance

[arsharma159]

Hello guys,
I'm facing a serious problem now in my vb.net(VS 2005) project.
The problem is that I want to update automatically my stock when I enter the purchase voucher and keep the purchase voucher detail as well.
I can do everything except the automatic stock update.
YOUR VALUABLE HELP WILL APPRECIATED. "THANKS IN ADVANCE".

[adam_k]

We'll probably need more info on your project before we can offer any assistance, like what is your db, what have you done already (either in the purchase voucher details or the stock update) and what your tables look like.

Usually we prefer to keep 2 seperate stocks, the on-hand which will be reduced when the orders actually get shipped and the free or allocatable. I'm guessing that you are going for the allocatable one, which has 2 ways to implement.
The first is to store the quantity in a table along with the on-hand. This would require you to update it when a new order is received. This can be implemented with an update query that would use a join between the order details and the stock keeping table, with criteria the order number or with a trigger -if it's supported by your db - that would update the stock with every new order line.
The second one is to calculate it every time you need it (on-hand - qty in orders pending). This approach would require you to add a status on your orders (preferably in the details part as to be able to handle backorders) and use that to filter out the completed ones.

[arsharma159]

Thank you for such a very quick reply.
Well, I'm using Access as DB containing five tables viz. Vendors, PurOrder, Customers, SalesOrder, Stock.

Vendors: VendorID(PK), Name, Contact, Phone, Address.

PurOrder: POID(PK), Items, Quantity, Rate, Total

Customers: CustID(PK), Name, Phone, Address

SalesOrder: SOID(PK), Items, Quantity, Rate, Total

Stock: SItemID(PK), Items, Quantity, Rate, Total

Well i'm able to enter and save/update purchase and sales vouchers and retrieve the reports Customer/Vendor ID-wise and print that.

The only problem I have is that I don't know what to do in order to automatically update the sock quantity when purchase or sale is made.

Thanks in advance.

[adam_k]

update stock set quantity = s.quantity + po.quantity
from stock s inner join PurOrder po on s.items = po.items 
where po.POID = '2' 

update stock set quantity = s.quantity - so.quantity
from stock s inner join SalesOrder so on s.items = so.items 
where so.SOID = '5' 

Use the appropriate update for each for the case.

[arsharma159]

Thank you very much for the solution, Adam.

But even then I can not solve the problem just because I've done evetything from microsoft video tutorials on the internet. I have started coding on starter level.

I don't exactly know how to use the soution code you have already provided me. So. please bear some patience with me and kindly provide me the step-by-step procedure to solve the problem.

I have a question also regarding the final solution given above that is it a 'stored procedures'?????

Once again I must remind you that i am using "MS-ACCESS" as my DB.

Thanks !!

[adam_k]

In a previous post you say that you are "able to enter and save/update purchase and sales vouchers".
Please share your code for those steps and will modify it to make the updates.

[arsharma159]

Exactly Adam, as I said earlier I'm using the Database Wizard in VB.NET Express. I do not need to code for that.

I click on Data Sourses > add the Database > Drag that to my Purchase and Sales form and that automaticaly creates the ADD, DELETE, & SAVE as well as Navigation as toolstrip. and furthermore I drag the Purchase order table (for example) to the form in Datagrid mode and that's it! Now, I can SAVE, DELETE, & ADD records to my database as Purchases.

And then, the only thing I want is that when I enter the purchase, my stock gets updated for the respective items.

And now, solution to my problem is all upto you! Exactly I' fade-up with this.

Thanks!!

[adam_k]

I have started coding on starter level.

That is in contradiction with

Exactly Adam, as I said earlier I'm using the Database Wizard in VB.NET Express. I do not need to code for that.

I've never used the wizard, so I can't really help you with that.

[arsharma159]

Hello adam,
you provided me the solution.
DID YOU MEAN ?????

UPDATE Stock
SET Quantity = Stock.Quantity + PurOrder.Quantity
FROM Stock INNER JOIN PurOrder ON Stock.Item = PurOrder.Item
WHERE PurOrder.POID = '2'

If yes please let me know what (WHERE PurOrder.POID = '2') exactly mean ??

And where should I put this code ??

Thanks!!