Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Who Had Item < Unrecognized | Who Had Item 2 >
Back to Who Had Item    Comments List
Pinned    Upload Images   Link   Email  
Transcript
Richard Rost 
          
11 months ago
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.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Who Had Item.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/11/2025 9:11:59 PM. PLT: 1s