Stock Control Database Prob

[P5X]

I'm setting up a database to keep track of book/video loans. Trouble is that some items have more than 1 copy and it'd be a waste of time and space to enter the data for each item 2-5 times.
I was thinking of having a drop down menu labelled "copies" for each item, so the user selects how many copies they have of each item and when a certain item is loaned out it will show up the number of copies in stock in the info section of each particular item.

Basically it will subtract 1 copy from the original amount when a item is loaned out and then add 1 copy back when that item is brought back...is there an easy way of doing this?

 

[Corticus]

Hi!

Firstly, I think you'll need a field in your items table for Qty-on-hand. Add a textbox to the form where you will process check-outs/in. Make the control source of this textbox the qty field. Lock the textbox so the user can only see the qty-on-hand, not change it. Add an unbound textbox to the form where you will put the qty returned/checked out. Add one button for check out, and one for check in (vary for your needs), and when you click, say the check-in button, code as such will be executed:

TextOnHand bound to Qty-on-hand field
TxtQty unbound

Command1_Click()

TextOnHand.locked = False
TextOnHand= TextOnHand + TxtQty
TextOnHand.locked = True

end sub

You will probably need to put some tests in so they don't try to check out more than you have etc.

You also might need another field for how many total of an item you have, not just on-hand, so you can't check in more than you have total.

edit: Let me know if you have 1 record for each item, and can't use the qty-on-hand method as described above. This will require another approach, something like this for checking out, say 3 books:

txtout is a y/n field for each item

command1_click()

dim i as integer

For i = 1 to TxtQty
txtout = true
docmd.gotorecord,,acnext
next i

end sub

Again, you will need to test Txtqty so you don't try to check in more than are there or vice-versa

HTH,

 

[P5X]

Thanks for your reply, I think i'm going to change my db structure around a little bit before trying that because I am starting to think that another method would bring better results.