Trying to calculate inventory in an Orders Details Database

[costean]

I'm somewhat of a beginner this being my 3rd Access database, but I have the table structure good and all relationships tight. I have an orders table, orders details table, products, inventory tables. The inventory table has prod ID, Stock on hand, Reorder level and is joined to the order details table and the products table. I need to know the best way to enter restock and get the stock on hand to increase automatically and get the stock on hand to decrease automatically as orders are entered. I can't find anything helpful in my book. I am doing this for a friend and don't want to give it back to him with everything finished except this important piece.

thanks

 

[lespaul]

You shouldn't store calculated fields like 'Stock on Hand'. You should calculate that information in a query when you need it. The reason for this is everytime you buy, sell or have a return you have to update this calculated value.

Check out 'The Fundamentals of Relational Database Design' for more information.

Leslie

 

[Terpsfan]

There are cases where denormalizing the data is fine, and I think this case my fit the bill.

In something like the Order Details form you could run an update query to handle the Quantity on hand in Inventory.

First create an update query like this:
UPDATE tblInventory
SET [StockOnHand] = [StockOnHand] - [Forms]![frmOrderDetails]![OrderQty]
WHERE [ProdID] = [Forms]![frmOrderDetails]![ProdID];

I would run this on the form's before update event. Perform some sort of validation first and if validation fails, set cancel to true, which nullifies the update.

 

[lespaul]

Then you will also need to make sure that you perform this update anytime an inventory modification takes place.

Sale to Customer
Purchase from Vendor
Return from Customer
Return to Vendor

These are the only transactions that I can think of, but according to your own situation, there may be more.

You will also need to have an inventory adjustment function that will allow the user to correct the In Stock if the physical quantity doesn't match the calculated quantity.

Leslie

 

[costean]

Thanks. I was able to get the units in stock to update with the update query, but am missing the piece of how to get the units to increase when restock occurs. Where should the restock entry occur? Afield in the inventory table or a seperate table. Will I need a different update query for this part?

 

[RoyVidar]

I agree with lespaul on the dangers of this kind of calculating. Here's a link elaborating a bit more on it Inventory Control: Quantity on Hand. At the bottom of the page, there's some things to consider, should you still want to pursue this path.

Roy-Vidar

 

[costean]

Thanks Roy,
Someone on another forum had pointed me to that same article. That's a lot of VB code but I would like to try and use it and go that way, except I have no idea how to add all that code to the database and get it to work. Can you give me a pointer on where to look for help on adding the code to get this thing working?

The other path, the update query that Omega gave did an update to the inventory table for all orders to date, but I can't seem to get it added to the form's before update event. When I run that update query it prompts me for parameters which I bypassed and it updated 29 rows in the inventory table, all orders to date. Does it need to be added as a before update event or can it just be run independently? My concern with that update query is that it will look at all records in the order details forms and update the inventory table based on orders that it has previously updated. I should test that by entering new orders, running it and seeing how many rows it updates.

I know the VB code is the better way to go but it seems to hard to implement given my lack of experience with Access And VB. Any other assistance would be appreciated. Is this hopeless for me and beyond my limitations??I wonder.

Thanks

 

[Terpsfan]

Docmd runsql allows you to run an inline sql statement. Since the parameters area in the form you should not get prompted for parameters. This worked for me.

Form_BeforeUpdate(Cancel As Integer)

On Error Goto ErrorHandler

Docmd.RunSQL "UPDATE tblInventory
SET [StockOnHand] = [StockOnHand] - [Forms]![frmOrderDetails]![OrderQty]" & _
" WHERE [ProdID] = [Forms]![frmOrderDetails]![ProdID];"

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "There was an error updating"
Cancel = True
Resume ErrorHandlerExit:
End Sub