Looking for an idea to manage inventory 1

[mohebk]

Hi,
My boss asked me to create an access database to manage the inventory of some sample products that we receive from vendors. My challenge is adding to the current inventory and deducting from the stock when something is dispensed. I created the tables and the forms and they look good. But when the user enters the number of items dispensed it seems to be deducting more than it should. I think it is because the users press the save button when they change something on the form. The calculation code is placed in the click event of the save button.

Please advise.

Thanks

Mo

 

[missinglinq]

If you want to help in troubleshooting your code, it would help if you actually posted your code!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[djj55]

One thing you could do is clear the screen after a save. This keeps the user from saving as they go. Or maybe change the "Save" to a "Finish"

Just my thought.
djj

 

[JoeAtWork]

With inventory databases, you should store every change as a transaction record. The trouble with having one "Quantity" field is that you have no audit record of how it got to that quantity.

Ideally, the Quantity is calculated by adding up all the positives and negatives in your transactions table. If something looks off, you need only look through your Transactions table to find out where things went off.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[BofRossClan]

One way I have done this, is all changes to inventory are placed in a separate table. I add field to mark the records as processed or unprocessed. My code calculates everything in the table that is marked as unprocessed. After the calculations are done, the field is updated to processed, and the form is closed (it is set to only show records that are unprocessed). This code updates two fields in my Master Inventory:

'Update Master Inventory QOH and Sold amounts
DoCmd.RunSQL "UPDATE Transactions INNER JOIN MasterInventory ON Transactions.ItemNum = " & _
" MasterInventory.InvID SET MasterInventory.QOH = [MasterInventory]![QOH]-[Transactions]![Quantity], " & _
" MasterInventory.Sold = [MasterInventory]![Sold]+[Transactions]![Quantity] WHERE (((Transactions.Control)=1));"

http://www.accessbrenda.com

 

[Pampers]

Why not get a third party product... Can save you a lot of work.

Pampers
Keeping it simple can be complicated