Who Had Item 2
By Richard Rost
2 years ago
Track Item Ownership by Date in Access, Part 2
In this Microsoft Access tutorial, I will show you how to determine who had an item on a specific date, coding with VBA, error handling, and employing functions like DLookup and NZ. Perfect for tracking rentals or loans, this practical lesson is a must-watch. This is part 2 of 2.
Members
In the extended cut, we will take the code we just wrote and rewrite it as a function, which can then be used in different parts of the database, including within a query to determine which customer had a product out on a specific date.
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!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, VBA coding, DLookup function, tracking rentals, Access database tutorial, rental table, product lookup, VB editor, Null Zero NZ, date range check, Access form, query logic, Access VBA, compile VBA, Access Video Series Part 2, Access TechHelp
Subscribe to Who Had Item 2
Get notifications when this page is updated
Intro In this video, we continue our Microsoft Access TechHelp series by building on our previous setup to write the VBA code that identifies who had a particular item on a specific date. We will review DLookup to find matching records in the rental table, handle different scenarios such as missing dates, items not checked out, and situations where items are still out because there is no end date recorded. I will show you how to validate user input, use the NZ function, and set up logic to display the correct customer information based on your search. This is part 2.Transcript Today is part two of my video series on who had an item on a specific date in your Microsoft Access database. Okay, so yesterday we set up our little product table, so we got a list of products. We set up our rental table, so we know who rented what product on what dates. And then we set up on our form what date we're looking for, pick the product, and then we click the button, and it'll tell us who it is. It won't just say "Hello World." So that's what we're gonna do today: write the code that goes in this button.
So, the logic behind our code is going to be with a DLookup. We're gonna say, okay, here's the product ID and here's the date. Okay, I want to go into the rental table, and I want to say find the rental ID where the start date is less than the date I'm looking up, and the end date is greater than the date I'm looking up or equal to'greater than or equal to, of course, right. So if I'm looking for product 3 on January 7th, it should come back with that record. And of course, there are some options too. If it doesn't come back with a record, that means someone might not have had that product out. Or, we might have a third case where it matches the start date, but there is no end date. So that product, you know, is still out. Okay, so we'll take a look at all these different situations.
So let's go into our VB editor here, right-click, build event. All right, so the first thing I'm going to do is I'm going to look to make sure that they put data in those boxes. I don't want errors. So if IsNull(lookupDate), then status, "Missing date,"; Exit Sub. The colon means I can put another line there without having to do a whole if-then statement. And we'll do the same thing for the product combo. Product combo, "Missing product." So if they don't put anything in both of those boxes, it yells at them. And you can do more stuff. Like, you can move the focus to the product combo box and hit the drop-down action. There's all kinds of stuff you can do. Again, we're keeping it simple, so we can focus on the logic.
All right, so case one is going to be the product has a start date and an end date. Okay, so we'll take a look at that first. Now, what we're going to do is we're going to look up a customer ID from that table. All right, we've got our rental table. We'll just return, we'll just look up the customer ID. And we'll use Null Zero NZ, so that if there is no record, if it doesn't hit something, it's going to return a zero. Yeah, I probably should have listed this in the prerequisites last week, but I forgot. Okay, so if you've never used the NZ function before, all it does is if a function returns a null value because DLookup can't find the record, it just gives you whatever you want, like a zero.
All right, so we're going to need a customer ID variable in here. So Dim customerID as Long. And here we're going to say customerID equals whatever product is in the product combo box. You want to put that outside the quotes, so that it actually gets the value of the product combo box and puts it inside your string, okay? Next line, and some other stuff.
Okay, so and the start date is less than or equal to, now I want to put the lookup date here, but again dates have to be inside of these things, all right? They have to be inside of hashtags, or octothorps, or pound signs, whatever you want to call them, all right? lookupDate, and that will close the date. Okay, and one more thing, the end date has to be greater than or equal to whatever the lookup date is. All right, comma zero for our NZ, and there's your lookup.
I know it's pretty complicated'let's break it down. So we're looking inside rentalT for this particular product, right, productID equals 6, for example, and uh... the start date of the rental has to be less than or equal to the lookup date, and uh... the end date of the rental has to be greater than or equal to the lookup date. So, if I look up January 1st for this product, it should return what I'm looking for. Don't forget your spacing'that's very important, right because you've got the product combo number is going to go right here inside the string. Okay, start date, you've got your dates, right, end date, you've got your dates. And then if this DLOOKUP returns a null value, in other words, there is no customer, then the NZ function will convert that to a zero, and we can use that.
All right, so now the next thing is going to be if customerID equals zero, then status: "No one has it." Okay, otherwise we have a customer, and then you put customerID in here, "Has it," or "Had it." Okay. And yeah, you could do another DLookup in here and DLookup the customer's name if you want to say Joe Smith had it. That's another easy DLookup. You should know how to do that. OK?
All right, let's save it. Debug, compile. Let's give it a test. Let's come back over here. All right, let me just slide this down so we can do some testing here. We want to see our data while we're testing it. I'll move this over this way. Okay, let's say we're searching for, we don't have records for March yet, let's say we're searching for January 7th, and I'm searching for product 3. Oh, what's product 3? Product 3 is that comm badge. Let's pick the comm badge and hit Look Up Rental. "Customer one had that," that's correct, it looked up for that product between those two dates, and it found customer one. Let's see who had the phaser rifle'no one had it, phaser. And let's see who had it on January 7th. No one had it, because, right, it wasn't rented until the 15th. Let's change our date to the 15th. And there we go, "Customer six had it." See? Okay, so that's working so far.
There's one more situation we have to take into consideration, though, and that's what if the product is still out. So let's change this to 1/15. Let's do product 4, which is the tricorder, and it still says "No one had it," even though we can see here that they took it out on the 10th. So we're going to add that code to our VBA here. DLookup still comes back with a zero. Case number two might be that the product has a start date, but no end date. So it's still out. We have to change our DLookup statement just slightly. In fact, I am going to just copy this and paste it here. And we'll tab it in, of course.
So now we're gonna say, look up the same thing, customerID from the rental table, where the productID equals ProductCombo, and the start date is less than or equal to the lookup date, and right here we're gonna change this, and we're gonna say, and If IsNull(the endDate), the endDate, just like that. Okay, what that's going to say is, look up and see if that particular customer had it on that date, they match the start date, but there is no end date'it's null. Okay, and here we'll say, if customerID equals 0, then now we can say "No one had it," and that's technically, I guess, case three. Case three, no one home. Let's make this a capital case right there. Capital "Case," "No one has it." Else, now we can say, the customer had it. "Has it, still out."
That is three different cases we had to check for, "if-then" loop and all that stuff, "structure statement." I'll test it, come back over here'well, always throw in a Debug Compile when you make changes like that. Don't forget your backups. All right, let's test that tricorder now. Click. "Customer 7 has it, still out." Look at that. Perfect.
Same? Let's go back and check, just to make sure we didn't break anything. Always double-check the stuff you already checked when you make changes because you don't want to break your code with something that you previously did. But let's just go back and check product 2, which was the handheld phaser. And that's still working. All right.
Did you like that? You want to learn more? In the extended cut for the members, we're going to take that code that we just wrote and rewrite it as a function. And with it as a function, we can then use it in different places in the database, including in a query. We can put it inside of a query and say, here's a list of all of my products and which customer had that product out on a specific date. We'll send the date and the product into the function. It'll return the customer, just like we just did. That'll be covered in the extended cut. Silver members and up get access to all of my extended cut videos. And gold members get access to my Code Vault with lots of cool stuff in it. And you can download these databases that I build in the TechHelp videos and lots more.
And of course, if you enjoy learning with me, come to my website and check out my developer lessons. I've got lessons for all levels. I've got from the very beginner all the way up to advanced stuff. And by the time you're done, you'll know just as much as me because I baked myself into the lessons. I took pieces of myself, and they're just'they're baked in there with a lot of love. So come and check it out.
But that is going to be your TechHelp video for today. I hope you learned something, folks. Live long and prosper. I'll see you next time.Quiz Q1. In the video tutorial, what are the primary tables mentioned that are involved in determining who had an item on a specific date?
A. Customer table and Sales table
B. Product table and Rental table
C. Inventory table and Checkout table
D. Item table and Reservation table
Q2. What is the purpose of the DLookup function in the context of this video?
A. To delete records from the database
B. To create a new rental record in the database
C. To look up information from a table based on specified criteria
D. To update the status of a rental item in the database
Q3. Which function is used to handle null returns from a DLookup function and return a zero instead?
A. DNull
B. ZeroIfNull
C. NZ
D. IfNull
Q4. What variable type is used to store customerID in the Visual Basic for Applications (VBA) code?
A. Integer
B. String
C. Boolean
D. Long
Q5. What is the method used to avoid errors if the user doesn't input required data into both boxes on the form?
A. Using a Try-Catch block
B. Using an IsNull check with an immediate response
C. Using a complex If-Then-Else statement
D. Using a default value for all form inputs
Q6. How does the VBA code determine if an item is currently checked out with no end date?
A. By finding records where the end date equals the current date
B. By checking if the end date is null for a matching start date
C. By looking for a negative customer ID
D. By assuming that no end date always means the item is available
Q7. In the beginning of the video, what does the presenter state they are going to replace "Hello World" with?
A. The current date and time
B. The results returned from a DLookup function
C. A message stating "Missing data"
D. A popup window with customer details
Q8. What is the purpose of the Debug Compile step mentioned in the video?
A. To translate the VBA code into machine language
B. To upload the code to the Access database server
C. To check for syntax errors and compile the code
D. To reset the database to its initial state
Q9. What is an additional feature offered in the extended cut of the tutorial?
A. Rewriting the VBA code as a recursive function
B. Creating a graphical user interface for the database
C. Rewriting the VBA code as a more general function
D. Adding encryption to the database for improved security
Q10. What incentive is provided for viewers to consider becoming a member on the presenter's website?
A. Free access to the presenter's personal contact information
B. Exclusive video content and downloadable database resources
C. A special certificate for completing the tutorial series
D. A discount on future video tutorials
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-B; 7-B; 8-C; 9-C; 10-B;
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 focus on determining who had a specific item on a given date in your Microsoft Access database. This is part two of the series on tracking which customer rented an item on a particular date.
Previously, we created a simple product table that lists all products and a rental table that records which customer rented which product, along with the start and end dates for each rental. We also set up a form where you can specify the date and choose a product. The objective is to click a button and have the form tell us which customer had the selected product on the chosen date, rather than just displaying a generic message.
The solution involves using a DLookup function. We provide the product ID and the date we're interested in. Then, within the rental table, we want to find a record where the rental's start date is less than or equal to the lookup date, and the end date is greater than or equal to that date. For example, if we're looking up product 3 on January 7, we should find the record where those conditions are met. If there's no matching record, it might mean the item was not checked out at that time. Another scenario to consider is when the start date matches but there is no end date, meaning the item is still checked out.
To implement this, the first step in our VBA code is validation. We check that both the date and product fields contain values. If either is missing, the user receives a message indicating what needs to be entered, and the procedure stops.
Next, case one involves looking for rentals with both a start and end date. We need a variable to store the customer ID. Using DLookup, we search the rental table for the correct product where the start date is on or before the lookup date and the end date is on or after the lookup date. If nothing is found, DLookup returns a null value, which we convert to zero using the NZ function. For those unfamiliar, the NZ function lets us handle nulls by returning a default value such as zero.
If the result is zero, it means no customer had the product on that date. Otherwise, if a customer ID is found, we display that the customer either "has it" or "had it." To get the customer's name instead of just their ID, you can run a second DLookup to pull the customer's name from the customer table.
After saving and compiling the VBA code, be sure to test it against your data. For example, if you look up product 3, which is our comm badge, for January 7, the form should indicate the correct customer. If you choose a product that wasn't rented out during that period, the result will confirm that no one had it.
However, there is another situation to handle: the case when the product is still checked out and does not have an end date. In this case, DLookup might return zero again even though the item was taken out earlier and has not been returned yet. To solve this, we add a second DLookup that looks for records where the start date is on or before the lookup date and the end date is null. If this returns a customer ID, then we know which customer still has the item. The message then updates to indicate the item is still out.
After making these adjustments, always test your code to ensure existing scenarios still work. For every new situation you handle, re-check the older ones to avoid accidentally breaking your solution.
For those interested in expanding this concept, in today's Extended Cut for members, we will take the logic from this code and rewrite it as a function. By doing so, we can use it in various places in the database, even inside queries. This will allow us to generate a list of all products and identify which customer had each item at any given time by sending the date and product into the function and getting the customer in return. Silver members and above can watch all Extended Cut videos, and gold members get access to the Code Vault and database samples from these lessons.
If you enjoy learning from my tutorials, visit my website for more developer lessons, ranging from beginner to advanced topics. My goal is to teach you everything I know, with lessons that reflect my own experience, delivered with plenty of enthusiasm.
You can watch a complete video tutorial with step-by-step instructions on everything mentioned here on my website at the link below. Live long and prosper, my friends.Topic List Validating required fields on a form before processing
Using DLookup to find a record based on multiple criteria
Building criteria strings for dates in DLookup
Using the NZ function to handle null results in DLookup
Returning a customer ID based on product and date selection
Handling cases where no customer has a product on the selected date
Displaying results based on whether a product is out, returned, or still out
DLookup for products with a start date but no end date
Testing different rental scenarios to verify logic
Updating the code to distinguish between returned and still-out rentals |
