|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Manage Rental Inventory By Richard Rost 
5 years ago
Manage Rental Inventory. Check In/Out Items, Books, Videos, Tools. Learn how to check out rental inventory items, and automatically return any items to your rental inventory once the due date has passed. Graham from South Africa (a Gold Member) asks: I have a very simple inventory requirement. Items are checked out, and then once their due date arrives, I just want Access to automatically check them back into inventory. That's it. Can this be done in a daily event? MembersWe'll make an actual check in / check out system for each item. You can mark which customer checked out the item, the due date, see rental history, and more!
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksUpdate Query: https://599cd.com/UpdateQuery
IntroIn this video, we will talk about how to manage rental inventory in Microsoft Access, focusing on a simple scenario where items such as books are checked out and automatically marked back in stock once their due date has passed. You will learn how to set up product tables, use an update query to process returns based on due dates, and automate this process to run when your database starts up. We will also cover using VBA to streamline these tasks and show how to display a status message to indicate inventory has been updated.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to talk about rental inventory. This works for any kind of rental situation, or even a library where you can check things out and back in again. Let's say you're a bookstore and you have a certain rental shelf. You let people take certain books and check them out, check them back in, or whatever kind of products you happen to rent. I was going to use video store for my example for class where you rent videos, but those do not exist anymore, do they? So we'll just go with general rental inventory or bookstore inventory that offers rentals. This will be a simple scenario where you've got a list of items like books. You have whether they're in stock or not, and what their due date back is. Once that due date passes, we'll mark them back in stock. Today's question comes from Graham from South Africa, one of my gold members. Graham says, I have a very simple inventory requirement. Items are checked out, and then once their due date arrives, I just want Access to automatically check them back in the inventory. That's it. Can this be done in a daily event? Now I emailed Graham back and forth and asked him a few questions. Literally all he wants to do is, on a specific date whenever the due date passes, he wants Access to automatically mark those items back in the inventory. No manual check in, check out, any of that stuff. So I'm going to cover that in this video. And then after we handle that problem, I'm going to show you in the extended cut for the members how to handle a check in and check out for each specific item in your rental inventory. But let's take a look at Graham's needs first here and do exactly what he wants to do. Since he's a gold member, he gets to pretty much call the shots. My gold members get away with everything. Let's start with a copy of my blank customer template. You can grab this off my website. There's a link down below. You can download it. It's absolutely free. So let's start by creating a product table. You can call it whatever you want to. I'm going to call it product table. You can call it your book table or whatever you do. I was going to do video store for my first example, but then I thought to myself, there are no video stores anymore. So there are still bookstores. We'll just use product ID. That's my autonumber. And then, of course, the description. Now I'm assuming that each item in this table is a specific unique item, not like a group of items. This isn't like a product category where you've got a whole bunch of copies of The Hobbit. Each product has its own specific record. Now we've got in stock, which indicates whether or not this item is in stock. And then if it's out of stock, we'll set a due date. Save that. That is my product table. Primary key, yes. Let's put some data in it. Let's say we've got a copy of The Hobbit. Actually, let's go Hobbit, The. That's how you like to do it. That's in stock. We've got Clockwork Angels, one of my favorite books. We've got, I don't know, Dungeon Master's Guide. We've got a Star Trek novel. Just pick one. I'm a nerd. So you've got a bunch of books in here. And let's say these guys are in stock. These two are out of stock. So this guy's due date--now today's date is January 5th, I think. Yeah, January 5th, 2021. So let's say this guy is due back on January 7, 2021. And the Star Trek novel was due back 12/30/20. That was due back last week. Actually, let's make two of them due back previously. Let's go 12/27/20. So two books were due back last week, and one isn't due back yet. Now all Graham wants is when he starts up his database, in like a daily event. We'll do it when the main menu starts up. When you open up your database, this main menu is set up as the startup form. If you don't know how to do that, go watch the video where I show you how to build this blank template. It's also free. It's on my website. It's on my YouTube channel. We set this as the startup form for the database. So when this guy starts up, when you come into the office every day and you start up your database, the main menu loads and it will run that event. It will take these guys and put them back into inventory if their due date is less than today's date. How do we do that? We do that with an update query. So let's close this. Let's make an update query. Create, query design, bring in the table you want to mess with. So product T, we're going to update data in this product table. I've got other videos on making update queries. If you've never done an update query before, go watch those videos first and come back to this one. Now, in our update query, we're going to set some criteria first. What's the criteria? In stock has to be false. It's not in stock. It's checked out. I've got videos on query criteria. If you've never done any work with query criteria, try saying that 10 times fast, query criteria. Then go watch those too; I'll put links down below. Or criteria queries, say that 10 times fast. What's the second criteria? The second criteria is that the due date has to be less than, or you can do less than or equal to, whichever you want to, today's date. So you can put in there Date(). If you want it to the minute, put Now(), but I'll do today's date. So less than or equal to Date(). If it was checked out yesterday, put it back in the inventory today. So let's save this. This will be my product return query. Now so far, it's still a select query. If I run this now, you'll just see what's going to be updated. That's great. So let's go back to design view. Now comes the task of turning this into an update query. We've got it. So we're selecting the right records as a select query. Now we change it to an update query. So hit the update button right here on the query type. Now you'll see an Update To field here. What do you want to change these fields to if these criteria are met? I'm going to change in stock (Update To) to true. And then due date, we're going to just make it null. It's no longer due; it's back in stock. Null means blank. Save that. Close it. If I were to just run this here, it would do its job. But I don't want it to run there. I want it to run when this guy starts up the database. So let's right click, design view. Now there are a couple of different ways you could do this. You could make a startup macro that does not involve any programming. I'm not a big fan of macros, honestly. I like VBA. Programming is not scary. And for those of you who have never done any VBA programming, go watch my intro to VBA. Again, it's a free video. I'll put a link to it down below. Go watch that. It's real simple. We can accomplish what we need with just one line of code. All you have to do is learn a little bit of VBA and your databases can become really, really powerful. So where do we put that magical one line of code? Go to where the main menu's properties are. Find Events. Then go to OnLoad right there. That's the function that runs when the form or report is loaded or opened. There's an OnOpen, too. They're roughly the same thing. Hit the ... button right there. That's the builder button. You may get asked what kind of builder you want. You want the code builder. I've got that turned off on my system. But the cursor will be placed right here where it says Private Sub Form_Load. Don't worry about all the rest of that stuff. That's all just stuff that I've got in there in the template. You want to worry about right there. Private Sub Form_Load. Put a couple of extra spaces, tab in. One line of code. I want to run that query when the database starts up. DoCmd.OpenQuery "ProductReturnQuery" Now that right there will make the query do its job. That's all you need. But you'll get a warning message. This query is about to run and change records. If you want to avoid that, just turn the warnings off. DoCmd.SetWarnings False That turns the warnings off. Make sure you turn them back on when you're done. DoCmd.SetWarnings True That's all. That's just to turn those annoying warning messages off. It'll just happen in the background. Save it. Now let's go back over here. Close that. I'm going to simulate the database starting by just opening up main menu. It's set as the startup form for this database. Double click, run it. Nothing appears to happen. But let's check our product table. Look at that. Notice those two books that were due in December are now marked back in stock and their due dates are null, because the product return query ran when the form started up. It set them back in stock and blanked the due dates. Now, of course, you'll have to verify that those books have actually been returned, but it works. Graham, this is, I hope, what you asked for. You may also want to throw up a little warning message. I created this thing in the template. I explained it in the video where I built this template. This is just a little whole Hello World. You can use this for putting messages in here. We can use this to display that the inventory has been processed. It's just basically a little message box without using the actual message box command. We can actually use this button to run that product return. Return inventory. You can do it manually if you want to. Right click, build event, code builder if you want to. We'll put that same instead of status Hello World and here let me scroll down so you can see it all and set it at. We'll just do this stuff. So I'm going to copy this and paste it into here. Now, I don't like having duplicate code in multiple places. So what I'm going to do is turn this into its own subroutine. So Form_Load and the Hello World button click can both call the same thing. Watch this. I'm going to come right down here below these guys. I'm going to go Private Sub. That means only this form can use this subroutine. Private Sub ReturnInventory So I made my own subroutine and I called it ReturnInventory. Now I'm going to take this code, cut that out, paste it in here. Now I can get rid of it out of the Form_Load and watch this. All I have to say in here is ReturnInventory. Then in here I can say ReturnInventory. Now both that button and the Form_Load will call ReturnInventory, which is the same thing. You do not want duplicated code. I created my own little sub called Status where it just puts stuff in that status box. Down here, I'm going to say Status "Inventory marked return." So that you know when you open up your form that your inventory is marked return. Close this and open it up. There we go, inventory marked return. Let's double check it. Let's do one more thing in here. Let's say this Dungeon Master's Guide was actually due on the third, and today's the fifth. Now, if I click Return Inventory, Inventory marked return, let's go check it. Look at that. It's back in stock. So Graham, I hope that answers your question as far as how to automatically have that stuff put back in your stock when your database starts up. And as I said earlier, members, in the extended cut, we're going to have an actual check-in/check-out system for each of your books. You'll be able to pick the book, pick the customer, check it out, it'll mark the date and time, we'll lock that field so you can't change the customer or click check out again. Then when the customer brings the book back, he'll go back to the book, click check in, it'll find the last check out, mark them checked in, and so on. That'll be in the extended cut for members. Here it is. I open up The Hobbit. I've got it checked out right now. Richard has checked it out right now. This is great. You can't change these, but I can certainly check it back in. Click, and now it's checked back in and it's marked in stock. Go to a different book. Superman Returns. I was going to do video store initially, but I decided to do bookstore because there are no more video stores. Let's check it out. Check out. I didn't pick a customer, so it drops down the customer box for me. Let's say Jean-Luc checks it out. Now I can hit check out again. Boom. Marks them checked out. Grays that. Grays that. You can't do anything with it until it's checked back in. Click. See that? We'll cover this in the extended cut video for members. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But do not worry. These TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. If you enjoyed this video, please give me a thumbs up and feel free to share it wherever you think it might help people who are interested in Access. Make sure you subscribe to my channel, which is completely free, and click on the bell icon to select all to be notified every time I post a new video. YouTube no longer sends out email notifications when new videos are posted. So if you'd like to get an email every time I post a new video, click on the link below to join my mailing list. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It's over three hours long, and you can find it on my website or on my YouTube channel. I'll include a link below you can click on. If you like Level 1, Level 2 is just $1, and that is free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page, and you can send me your question there. If you have a specific problem you need help with, or you'd like to discuss having a database built for your needs, I do offer one-on-one consulting. Be sure to follow my blog and find me on Facebook, Twitter, and of course, YouTube. Once again, my name is Richard Rost. Thank you for watching this TechHelp video brought to you by AccessLearningZone.com. I hope you enjoyed this video and you learned something today. I'll see you again soon. QuizQ1. What is the main problem Graham from South Africa wanted to solve with his Access database?A. Automatically mark items back in inventory when their due date passes B. Send email reminders to customers for due items C. Track multiple copies of the same product D. Prevent users from checking out more than one book at a time Q2. What type of Access object is used to update records in the product table when an item's due date has passed? A. Append Query B. Update Query C. Delete Query D. Make-Table Query Q3. In the scenario described, which field is set to TRUE by the update query when an item is returned to inventory? A. DueDate B. InStock C. ProductID D. Description Q4. When updating the returned items, what value is assigned to the DueDate field? A. Current date B. 0 C. Null D. "Returned" Q5. Where is the code placed to automatically run the update query when the database starts? A. In a table event B. In the Query Design C. On the Main Menu form's OnLoad event D. On a report Q6. What is the purpose of DoCmd.SetWarnings False in the VBA code? A. To stop all system warnings forever B. To enable user input during query execution C. To suppress warning messages during query execution D. To ensure only administrators can run queries Q7. Why is it important to use DoCmd.SetWarnings True after running the query? A. To disable VBA script warnings B. To make warnings visible to users again C. It is not necessary to turn warnings back on D. To prevent the form from loading twice Q8. How does the video suggest avoiding duplicated code when running the inventory return process from both the form load and a button click? A. Write different code for each event B. Avoid using a button entirely C. Create a separate subroutine that both events can call D. Ignore duplicated code as it does not matter in Access Q9. What is shown in the template's status box after processing inventory returns? A. The list of all checked-in items B. "Inventory marked return." C. The query name D. A warning message about errors Q10. What additional feature is covered in the extended cut for members? A. Importing data from Excel B. Building a full check-in/check-out system for each book C. Printing labels for inventory D. Sending overdue notices Q11. What type of data structure is assumed for each product in the simple product table in this scenario? A. Each record represents a unique item B. Each record represents a product category C. Each record tracks multiple copies of a single title D. Each record is shared between customers Q12. How does the database know which items should be marked back in stock on start-up? A. All items are checked in every day B. It checks for items where InStock is False and DueDate is less than or equal to today C. It checks for items with the highest ProductID only D. It marks only items returned by customers in person Q13. Which Access form property is used to trigger an event on form load? A. OnClick B. AfterUpdate C. OnLoad D. OnClose Q14. What is the recommended way to get more information or ask questions to Richard Rost, according to the end of the video? A. Leave a comment but do not expect a reply B. Send a letter by mail C. Visit the TechHelp page and send a question D. Wait for a live session once a month Q15. What free resource is suggested for viewers who want to learn more about Microsoft Access basics? A. Free Access Level 1 course B. Microsoft Word tutorial C. Advanced Excel training D. SQL Server migration guide Answers: 1-A; 2-B; 3-B; 4-C; 5-C; 6-C; 7-B; 8-C; 9-B; 10-B; 11-A; 12-B; 13-C; 14-C; 15-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on managing rental inventory using Microsoft Access. This method works not only for rental businesses but also for scenarios like a library system where you need to check items out and back in.Let me set the stage. Suppose you run a bookstore with a section for rentals. You lend out books and later expect customers to return them, similar to how traditional video stores operated before they became obsolete. While I originally thought of a video rental example, it makes more sense to use books, since those are still common. We're going to keep things straightforward. Imagine a table listing all the items you rent out, such as books. For each book, you need to track whether it is currently in stock, and if it's been checked out, its due date for return. When the due date passes, the item should be marked as available again in your inventory. The inspiration for today's lesson comes from a question sent in by Graham, one of our Gold Members from South Africa. Graham's inventory requirements are very simple: when items are checked out and their due date passes, he wants Access to automatically mark them back in stock. He wants this to happen automatically each day, without having to manually check the items in. After talking to Graham, I learned that he is not looking for a typical check-in/check-out system, just an automated process where Access checks due dates each day and updates the inventory accordingly. In the extended cut for members, I will show how to create a more advanced system for tracking each check-in and check-out event, but for now, let's address Graham's specific need. I start by using my blank customer template, which you can download from my website free of charge. In this template, we create a table for products – in this example, books. Each record in the table represents a single, specific item. That means each book gets a unique entry; we're not tracking multiple copies of the same book in a single record. Every record includes an ID (as an autonumber), a description, a field to indicate whether the item is in stock, and a due date reserved for items that have been checked out. The primary key is set up on the product ID. After setting up the table, I enter some sample data. For instance, entries might include "Hobbit, The", "Clockwork Angels", "Dungeon Master's Guide", and a "Star Trek" novel. Some books are in stock while others are currently checked out, with due dates assigned for when they are expected back. For demonstration, a couple of these dates are in the past. Now, Graham wants Access to automatically check items back in as soon as their due date passes, ideally when the database is opened each day. In my example, the main menu form is already set as the startup form for the database, meaning it opens first whenever someone starts the database. The way to handle this is with an update query. The update query will go through the product table and identify all items where "in stock" is set to false, and the due date is less than or equal to today's date. For those records, the query will set the item back to "in stock" and clear the due date field (by setting it to null). If update queries are new to you, I recommend reviewing my other tutorials on that topic first. But the core idea is that we use the criteria "in stock is false" and "due date is less than or equal to today". Once the criteria are set, we save the query as "ProductReturnQuery" and change it to an update query. In the "Update To" fields, we set "in stock" to true and "due date" to null. While the query works on its own, the key is to have it run automatically whenever the database opens. To make this happen, I use VBA instead of macros, as VBA is more flexible and powerful. If you are unfamiliar with VBA, I encourage you to watch my introductory video, which is also available for free. Inside the main menu form, I go to the OnLoad event property and place the code to run the update query when the form loads. It's just a single line of VBA code that opens the "ProductReturnQuery". I also add lines to turn off action query warnings before running the query and turn them back on immediately afterward, so users don't see confirmation prompts. After setting it up, each time the database is started and the main menu form opens, the system runs the query and updates the inventory automatically. Books whose due dates have passed are marked back as in stock, and their due dates are cleared. For user visibility, I have a status box in the template which can display messages like "Inventory marked return." This can be triggered manually with a button or on form load. To avoid duplicated code, I place the update and status message code into a shared subroutine, which both the form's load event and the manual button can call. Now, whenever you open the form or press the button, the system processes the inventory and displays the notification. For instance, if I update the due date for "Dungeon Master's Guide" to a past date and use the feature, it returns the book to stock. So, for Graham and anyone with a similar need, this approach keeps your rental inventory up-to-date automatically, using Access's built-in features and a bit of VBA for automation. As I mentioned earlier, members can explore the extended cut for a complete check-in/check-out tracking system. In that version, you can select a specific book, choose a customer, and properly record the full checkout and return process for your items. The system will set the date and time of each transaction and enforce rules to prevent changes to checked-out records until the item is returned. Membership perks also include access to extended cut videos, live sessions, and more. However, my TechHelp videos will continue to be available to everyone for free as long as you keep watching. If you found this lesson helpful, please like and share the content with others interested in Access. Subscribe to my channel for free, and click the bell to receive notifications about new videos. If you want email notifications, you can join my mailing list through the link provided. Be sure to check the additional resources listed below the video for supporting lessons and extra material. If you have not tried my free Access Level 1 course, take a look. It covers all the Access basics and is available both on my website and my YouTube channel. Level 2 is just one dollar or free for channel members. If you want your question featured in a future video, visit my TechHelp page and submit your question there. I also offer one-on-one consulting for more specific needs or custom database development. For more updates and community discussions, follow my blog and find me on Facebook, Twitter, and YouTube. For a complete video tutorial with step-by-step instructions on everything covered here, visit my website at the link below. Live long and prosper, my friends. Topic ListSetting up a product table for rental inventoryAdding fields for in stock status and due date Populating the product table with sample data Identifying overdue items based on due date Creating an update query to mark overdue items as in stock Setting criteria in the query for items not in stock and past due Configuring the update query to reset due date and in stock status Automating the query to run on database startup Using the Form Load event to trigger the update query Suppressing warning messages during query execution in VBA Encapsulating code in a reusable VBA subroutine Displaying inventory update status on the main menu form Providing a manual button to trigger the inventory return process Testing the automated inventory check-in functionality |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access lending library, rental management, check out, check in, point of rental, rental software, inventory management, track inventory of rented items, rental database template, rental tracker, rental equipment PermaLink Manage Rental Inventory in Microsoft Access |
