Who Had Item
By Richard Rost
2 years ago
Track Item Ownership by Date in Microsoft Access
In this Microsoft Access tutorial, I will show you how to track rental item possession on specific dates using VBA and DLookup functions. Discover methods to streamline your rental business reporting, and learn to construct a relational combo box while mastering if-then statements and the IsNull function.
Audrey from Maple Grove, Minnesota (a Platinum Member) asks: I run a rental business, and occasionally I have to determine who had a particular item on a specific date for reporting purposes. Right now, I have to go through the various rental receipts to try to figure out who had the specific item on that date. Is there a way I can enter the date, select the item, and it will tell me who had that item on that particular date?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, track rental item possession, VBA in Access, DLookup function, relational combo box, IsNull function, rental business database, specific item lookup, date-based item tracking, Access reporting tutorial, customer rental record, relational database design, product rental system, record rental transactions, item possession query, rental item database, VBA code for rentals
Subscribe to Who Had Item
Get notifications when this page is updated
Intro In this video, we will learn how to set up a system in Microsoft Access to determine which customer had a specific rental item on a specific date. I'll show you how to create the necessary product and rental tables, input example data, build a lookup form with a date picker and combo box, and discuss best practices like using reserved words and default values. This tutorial focuses on the setup needed before adding the VBA logic to perform the actual lookup, which will be covered in part 2.Transcript In today's video, we're going to learn how to determine which customer had a specific rental item on a specific date in Microsoft Access. Today's question comes from Audrey in Maple Grove, Minnesota, one of my Platinum members. Audrey says, "I run a rental business and occasionally I have to determine who had a particular item on a specific date for reporting purposes. Right now, I have to go through the various rental receipts to try to figure out who had a specific item on that date. Is there a way I can just enter the date, select the item, and it will tell me who had that item on a particular date?" Yes, absolutely. We can do this with just a little bit of VBA code.
It is possible to do this without VBA, but this is one of those instances where it's so much easier just to use VBA than it is to try to construct a query to do this. So I'm going to show you the VBA method. That means this is a developer-level lesson. If you've never done any VBA before, go watch this video first. It'll teach you everything you need to know to get started in about 20 minutes. It's free. We're also going to use DLookup, the granddaddy of all lookup functions. So, if you've never used this before, definitely go watch this video. This is a powerful one.
Watch my video on relationships. It's very important to have a good grasp on relationships before you can do this. We're going to make a relational combo box. That's a combo box where it gets its data from a table. So make sure you know how to do that. We're going to use an if-then statement. So here's another video to go watch if you've never done that. I'm going to use my status box, which is basically a text box that I have on my menu where I can display information instead of using a message box or anything else like that. So go watch this one optionally.
And finally, go watch my video on null. We're going to use the IsNull function today. Again, these are all free videos. They're on my website. They're on my YouTube channel. Go watch them and then come on back.
OK, here I am in the TechHelp free template. This is a free database. You can grab a copy off my website if you want to. And the first thing we have to do is put in here a product table. So we have something for our customers to rent. It could be an item table. And by the way, you can use this same technique for figuring out what a particular product costs on a particular date because I know some of you store the history pricing of your products.
So you've got a product table, and you've got the product pricing table. You can use the same technique to determine, "Okay, on what date was I selling this particular product for?" It's the same concept. So let's make a table. Create table design. Let's just make a real simple product table. You have ProductID, that's our autonumber, and we've got the Product Name or Description. Don't use just 'name.' Remember, 'name' is a reserved word. You never want to have a field named 'name.' Okay? And there are some other ones too, like 'date,' 'picture.' There's a bunch of reserved words. I'll put a link to my reserved words video down below.
All right. Let's save this as our product table. "No primary key defined. Say yes." Let's put in a phaser rifle, a handheld phaser, what else have we got, a comm badge, and a tricorder. Okay, so we've got four items that my customers can rent. All right, let's close that.
And now we need a table to track what the rentals are. So when a customer rents an item, it'll go in this table. So create, table design. This will be our RentalID. That's our autonumber. We've got a CustomerID. That's a number of type long integer. That's covered in relationships. We're going to have a ProductID in here. A customer rents a particular product on what date? Start Date. Oops, DateTime, okay. And yes, I'm going to leave these as null because we'll put them in manually when the item is taken out. We'll put the start date in there. You could default the start date if you want to because probably you're not going to put a record in this table until they actually rent it. So I can see leaving a default value in here as "=Now()". That's fine. If you care about the date and time, if you just care about the date, put "=Date()". If it's a full day rental, just like that. But I would leave the end date as null, so you don't get a default end date in there until they actually return the item.
And you could put other information about the rental action itself in this table, any notes you have, the price they paid, did they pay for it, that kind of stuff. We'll assume it's returned once an end date is entered into the system. So we're going to save this now. This will be my RentalT. And you can go ahead and make forms for these. I would make a product form, obviously, a continuous form to list them all, a single form. The same thing with the rentals. You could put a rental subform on the customer. I've got tons of other videos that cover all this stuff. Today we're just focusing on the logic for seeing who had what product on what particular date.
If you want all those bells and whistles, take my full course. I go over stuff in a lot more detail, too. All right, so let's put some sample data. It's always easier to work with stuff when you've got sample data in here. Now we're going to need rental information. So I'm going to need to see my customer list. Let's put the customers up here so I can see their CustomerIDs. Again, normally you would do this with forms. And I've got my products over here. Products go right there. So let's say, alright, we'll start with our rental. Let's say me, Richard Rost, Customer 1, I rented the Combadge, which is 3, on let's say January 5th, and I returned it on January 20th. And yes, I'm using ISO dates: year, month, day. If you don't know what those are, I'll put a link to that down below as well.
Alright, another rental, let's say Malcolm Reynolds, Customer 6, rented a handheld phaser. He took it out on January 15th and he returned it on February 10th. All right, we'll do one more. Let's say Wesley Crusher, Customer 7, he got a tricorder, he took it out on January 10th and he has not yet returned it. Okay, see where we're going with this? All right, let's close all this stuff down. Now we've got enough data in the system to test this and to actually make our lookup form here.
So we're just going to do this right on the main menu. Design view. I'm going to take this. Let's make it a little bit smaller. This will be our button to do the lookup. We're going to make this, since this is already a date field, let's make this a date that we can actually change. Because right now, this just says 'today is,' and it has the control source of "=Date()", but I don't want to fix it to a specific date. I want to be able to type that in. But I still want to default today's date there. So if you want to see a particular product today. So we're just going to move this from the control source. We're going to cut that out. And we're going to move it to the default value. So click on here and put default value as "=Date()". What that does is it says you're going to start off as today's date, but the user can change it.
Okay, that's what default value means. However, the control source means it's always fixed at this. It's going to be always fixed at today's date. You can't change it. Okay, that's the difference. Usually a control source, you bind it to a field in the table. But this form is unbound, so there's no data behind it.
All right, while we're at it, let's go to all over here. Let's change the name of this guy to LookupDate, that's the name of the field, and we'll put the date right here, 'Date.' Okay, and that's just the label, you can put whatever you want in there. Now for the product, let's make a combo box. So the user can just pick from a list. So let's come up here and find combo boxes. We'll drop that right there. This is a good wizard, I like this wizard. We're going to look up the values from the table of the query, next. We're getting our data from the product table. Next. Bring over both fields, the ID and the name. Next. Let's sort it by the product name. Next. And then that's what it's going to look like, the columns. The key column is hidden. Next. And then what label would you like? Let's make this just 'Product.' And then finish. And there's our beautiful little combo box. We'll put it right there. We'll resize it just a wee bit. We'll move you over here so you're right under the other one like so. All right.
We'll use the format painter and go click, click, and now they look the same. And that's all good. Let's make this. I have a thing with all the fields have to line up just right. It's my Access OCD.
Now one of my pet peeves is that the wizard doesn't let you actually name the box. It's called Combo16, so let's call this ProductCombo. And now we can slide this button up just a little bit. That's all the space we need. And we'll say 'Look Up Rental' on the button. You can rename the button if you want to. It's called 'Hello World Button' in this database. I don't care for now, but eventually, yeah, you want to put a good name in there.
All right. So now what we're going to do is we're going to write some code in this button that's going to say, "Okay, go to the rental table, look up the record on this date for this particular product, and in the status box tell me what customer had it on that date." And we're going to do that in tomorrow's video. So make sure you tune in tomorrow, same bat-time, same bat-channel, or if you're a member, you can watch it right now because members get to watch stuff as soon as I make it. They don't have to wait until it's actually released on YouTube or on my website.
Alright, so come on back for part two tomorrow. We're going to write the code to figure all this stuff out, and that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time. I'll see you tomorrow for part 2.Quiz Q1. What is the main objective of the tutorial video?
A. To teach how to create a rental receipts database
B. To learn how to manage inventory in Microsoft Access
C. To demonstrate how to determine who rented a specific item on a specific date using VBA code in Microsoft Access
D. To showcase how to write complex SQL queries in Microsoft Access
Q2. Which Access function does the video identify as essential for looking up values?
A. DSum
B. DMax
C. DLookup
D. DMin
Q3. What is the first video recommendation for someone who has never done any VBA before?
A. A video on relationships in Microsoft Access
B. A video on product pricing strategies
C. A video on getting started with VBA in about 20 minutes
D. A video on creating rental receipts
Q4. Which type of combo box will be created according to the tutorial?
A. Unbound combo box
B. Calculated combo box
C. Relational combo box
D. Navigation combo box
Q5. In creating the rental tracking table, what default value is suggested for the Start Date?
A. "=Now()"
B. "=Date()"
C. "Null"
D. "=Time()"
Q6. For which purposes can the demonstrated technique also be used apart from tracking rentals?
A. For determining future product releases
B. For tracking employee attendance
C. For figuring out historical pricing of products on specific dates
D. For inventing new product names
Q7. According to the video, what type of fields should you avoid using due to them being reserved words in Access?
A. 'Customer' and 'inventory'
B. 'ProductID' and 'RentalID'
C. 'data' and 'info'
D. 'name' and 'date'
Q8. How is the control source of the Lookup Date field configured to allow the user to change the date?
A. By simply removing the control source
B. By setting the control source to "=Now()"
C. By setting the control source to "=Date()"
D. By moving the "=Date()" formula from control source to the default value
Q9. What is stated to be assumed when an end date is entered into the rental tracking table?
A. The customer has extended the rental
B. The item is assumed to be lost
C. The item is assumed to be returned
D. The item is due for maintenance
Q10. How can Platinum members access the next part of the tutorial series?
A. By tuning in at the same time the next day
B. By waiting for its release on the YouTube channel
C. By accessing it immediately since they get to watch new content as soon as it's created
D. By signing up for the full course
Answers: 1-C; 2-C; 3-C; 4-C; 5-B; 6-C; 7-D; 8-D; 9-C; 10-C
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.Summary Today's TechHelp tutorial from Access Learning Zone will show you how to figure out which customer had a particular rental item on a specific date using Microsoft Access. This is a common need for anyone running a rental business who occasionally has to track down who rented an item for reporting or inventory purposes. Instead of sifting through endless rental receipts, there is a straightforward way to enter a date, select a product, and instantly find out which customer had that item on that day.
This process is made much easier if you use a bit of VBA code. While it is possible to construct an Access query to do the job, the VBA method is much more direct and efficient, especially for developers or anyone comfortable with writing code. If you have never worked with VBA before, I recommend watching my beginner VBA video first. It offers a solid foundation and will get you up to speed in about 20 minutes. We will also be using the DLookup function, which is one of the most powerful lookup tools in Access. If you are not familiar with it, I strongly suggest you check out my dedicated DLookup tutorial as well.
Understanding relationships in Access is also vital, as our solution will rely on combos and linked tables. A solid grasp of how relational combos work—where combo boxes get their values from other tables—is necessary. We will use an if-then statement in the code and leverage a special status box on our form to display the results, rather than a standard message box. You should review my recommended videos on those topics as needed. Also, familiarity with the IsNull function will be important since we will check for missing or empty values in our logic. All of these foundational videos are available for free on my website or YouTube channel.
Let's move on into the database. I am using the TechHelp free template, available for download from my site if you would like your own copy. First, we need to make sure we have a table for the rental items—this can be a Product or Item table. The same technique you will learn here can also be used to look up product prices for a specific date, which is helpful if you keep a record of historical price changes.
The structure for your Product table should be very simple: include a ProductID field (set as an AutoNumber) and a ProductName or ProductDescription field. Avoid using "Name" by itself, as it is a reserved word in Access and can cause problems. The same goes for other reserved keywords like "Date" or "Picture." If you need more information, I have a full list of reserved words with a link below.
Next, save your Product table and enter a few sample rental items. For demonstration, I used examples like phaser rifle, handheld phaser, comm badge, and tricorder—just a handful of things you might have for rent. With products in place, you need another table to keep track of the rentals themselves. Create a Rentals table with the following fields: RentalID (AutoNumber), CustomerID (Long Integer), ProductID, StartDate (Date/Time), and EndDate (Date/Time). When a rental is created, only the StartDate needs to be entered; leave EndDate blank until the item is returned.
You can optionally add other fields to this table to track notes, prices, payment status, or any other details related to the rental transaction. Typically, a record stays open (with a null EndDate) until the item is checked back in.
Once your tables are set, you can create simple forms for data entry or reporting. While today's lesson focuses on the lookup logic, you can follow my in-depth courses for building more advanced forms and subforms. For demonstration, I entered some example data: a few customers renting different items on various dates, with some items returned and others still outstanding. Using ISO date format (year-month-day) helps avoid confusion, especially with international data.
With the sample data in place, it is time to build the lookup form. I work with the main menu form in design view to add the necessary controls. There is a date field on the form, and I want to allow the user to change the lookup date easily. By setting the default value to Date(), today's date will automatically appear, but the user can still enter any other date if needed.
Next, I add a combo box to list the available products. The combo box pulls its data directly from the Product table, presenting a clear list of items to choose from. It is important to name your controls sensibly, so I renamed the combo box to ProductCombo and the date field to LookupDate for easy reference in the code.
With the controls lined up and labeled, the form is both functional and user-friendly. The last step for today is preparing the button that will initiate the lookup process. When the user clicks it, we will instruct Access to search the rental records for the selected product on the entered date, then display which customer had it at that time in the status box.
The actual VBA code for this lookup will be covered in the next lesson. If you are a member, you can access it right away. Otherwise, check back soon for part two, where I walk through writing the code to perform the lookup and display the results on your form.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a product table for rental items
Avoiding reserved words in table and field names
Creating a rental transactions table
Using default values for date fields
Entering and managing sample data for rentals
Understanding rental item status with start and end dates
Building a main menu form for lookups
Setting up a date text box with default value
Adding and configuring a combo box for product selection
Renaming controls for clarity on forms
Aligning form controls for better layout and usability
Implementing a lookup button for item rental checks |
