Using MS Access/adding stock
I am a very beginner with Access. I am trying to design a database to control stock.
I have a simple problem, but I can't find the answer when I google it.
I have a table with stock on hand. Each product has a unique batch number. I want to create a form in wich I can find the product by batch number with an empty field for quantity. In that field I want to be able to add a number. That number then should then be added to the quantity in my stock table. The next time I use this form, the field for quantity should be empty again for adding a new number etc.
I hope I explain it clearly.
Is there an easy solution for this?
That's not the best way to handle stock control. What you need is a transactions table like:
TransactionID (Pk Autonumber)
You also need a table For Transaction Types
TransTypeID (PK Long Integer)
Direction (either in, out or neutral)
Now you create a form bound to tblTransaction. Every time you move stock you enter a transaction using that form.
From there you CALCULATE stock on hand, using a query that adds the incoming transactions and subtracts the outgoing transactions.