I am building a database system for a video shop and I want to be able to view all the videos I have in the shop (so not the ones that are currently on loan.) To do this, I need a field in the video table that says if it's in the shop or not, which I have called abStatus and made a boolean check box.
Therefore if a video is in the shop the box in the table is ticked, if it's out then it's unchecked. I need this so I can then run a query on all the videos with ticks.
Want I want to know is how do I change the status of the checkbox when i click a button in a form for example? So that when the video is loaned, the checkbox gets unticked so that the video doesnt appear in queries for the remaining videos.
I think I need to use visual basic to do this, but I'm not familiar with the functions for access and I'm not sure how to do it.
That's some pretty bizarre criteria. Not applicable to the real world at all.
How do you pull a rental history for a given patron?
The elegant (and fast) solution is to put a return date on the loan record. Then query for loan records where the return date is null to see everything that's checked out. Or you can outer join the tables together based on video_id where loan record is not null to return current inventory.
Anywho, whatever you do to delete the loan record, you could run an update statement using the DoCmd.RunSQL to execute an update statement.
I assume you have some sort of key or something for the video, so you could use a statement similar to this:
DoCmd.RunSQL "UPDATE videos SET abStatus = 0 WHERE video_id = " & txtBoxWithVideoID.Value
I'm not sure what your process for returning videos is, so I'm not sure exactly how to implement this, but that's the basic idea I would suggest.
every time a video is hired use an update query to remove 1 from the qty in record for that film title.
every time one is returned use an update query to add 1 to the qty in for that title.
this is much easier than using a check box and has the added advantage of allowing the user to see how many of a certain title are "in" at any one time.
(assuming you will have more than 1 copy of some titles.)