Favorite Customers
By Richard Rost
5 years ago
Display Favorite Customers on Your Main Menu
In this video, I'll teach you how to create a "Favorite Customers" list box on your Main Menu form. This way you can just double-click on a customer to go right to their account. Quick and easy!
Katrina from Barcelona, Spain (a Gold Member) asks: I have five to ten customers I contact on a regular basis, some daily. Is there a way I can make a list of my favorite customers and have a quick way to open their accounts from my Main Menu?
Members
I'll show you how to track which customers you view the most, and which you have most recently viewed. This way you can change the favorites list to show the most recent customers and the customers you've opened the most. We'll also see how to automatically requery that list box when it gets focus.
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!
Addendum!
After I posted the video, I realized you may want to put the following line in the code at the top of the OnCurrent event in the CustomerF form:
If IsNull(CustomerID) then Exit Sub
Otherwise, every time you open a blank, new record, Access will save the data to the table, creating blank records at the end of your customer table. Sorry.
 Links
List Boxes: https://599cd.com/RelationalCombo
Intro to VBA: https://599cd.com/IntroVBA
On Double Click: https://599cd.com/OnDblClick
Intro
In this video, I will show you how to create a favorite customers list in Microsoft Access that appears as a list box on your main menu, allowing you to quickly access the customer records you work with most often. We will walk through adding a field to mark customers as favorites, building a query to display only those favorites, adding the list box to your main menu, and setting up a double click event to open customer records instantly. I'll also show you how to refresh the list to keep it up to date for easy access.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to make a list box on your main menu showing you your favorite customers. If you have three or four customers, or ten, that you always contact on a regular basis, they're right there on your main menu. Just double click and boom, it will open up the customer record.
Today's question comes from Katrina from Barcelona, Spain, a gold member. Katrina says, I've got five to ten customers I contact on a regular basis, some daily. Is there a way I can make a list of my favorite customers and have a quick way to open their accounts from my main menu?
Well of course, Katrina, all you have to do is indicate which customers you want on your favorites list with a field in the customer table, for example. Then we'll make a list box and put that on your main menu. Then, with a double click event, we can open up the customer form. Let me show you how to do it.
Here I am inside my TechHelp free template. This is a free download off my website. If you want to grab a copy, I'll put a link down below. In this database, I have my customer list and I can double click on any one of these guys and that opens up the customer record.
What I'd like to do is make another field like the is active field that says this is a favorite customer. So let's close this. Let's go back to my customer table design view. Down at the end here, I have is active. Let's make is favorite. I'm going to make that a yes or no. I'll set the default value to no.
Now let's close this up and let's put that field in the customer's form somewhere. Let's just slide notes down a little bit like that and I'll put is active over here. Let's go to my design, add existing fields. Grab is favorite and slide it right there. Maybe a little format. Let's make this guy format. Let's make it, let's go blue and bold. All right, so that's a favorite. Save it. Close it. Open it back up again.
Now I can indicate who my favorites are by just checking that box favorite. Let's go, drawing pick cards. Me and John Link Pickard are favorites. If you want, you can add them to the customer list. What I'm going to do is I'm going to put a list box right here that only shows the favorite customers.
Before I do that, let's make a query that shows the favorite customers. So go to Create, Query Design, bring in customers. Now, what do you want to see in that box? Well, I want to see the ID of course. That's how we know which customer to open. Let's go last name, then first name, and I'm going to sort them last name, first name. Then let's go is favorite and we'll make that criteria true or yes, either one.
Save it: CustomerFavoriteQ. I try to keep everything singular in my database. That way I don't go crazy. Is it customer or customers? Was it favorite or favorites? I just keep everything singular. That way I don't have to think about it.
Okay, CustomerFavoriteQ. There we go. Now I have enough to make my list box. I can come in here, right click, design view. That's fine. List boxes are up here somewhere. Right there is a list box. Drop it down there.
If you've never done any list boxes before, I have videos on list boxes. Go watch those. List boxes are almost identical to combo boxes. The difference is list boxes are always open and combo boxes you can sometimes type values into them. That's the only major difference; aside from that, they function almost exactly the same.
So, I want the list box to get the values from a table of query. Query is my CustomerFavoriteQ. All right, next. Now, bring over the ID first. That's how we know which customer to open. We'll use that in a minute. I'm going to go last name, then first name. We don't need to bring is favorite in. That's just for in the query to do the filtering or limitations.
Now you can re-sort in here if you want. That's not a bad idea. Any filtering or any sorting that you put on in the list box wizard will override the query. So we really didn't need to sort the query, but that's okay. Last name, then first name, and then next.
How do you want it to look? Now, since we based our results on a query, we can't automatically hide that customer ID, but all we have to do is make its width zero. Just do that. Real easy. Make these as wide as you think they need to be. Next.
Now, what's the bound field? What field has the value that we're interested in? I only want to see first name and last name, but I'm interested in that customer ID. That's how I'm going to know which customer to open. What label would you like for it? Doesn't matter, we're going to delete it anyway.
There it is right there. Goodbye. So, here's my list of favorite customers. Let's give it a splash of color. Let's go with that. All right. Save it. Close it. Open it back up again. There you go. There's your favorite customers. Favorite colors, I almost said. Favorite customers.
Click on them. That doesn't do me much good. If you want to, by the way, if you just call people on a regular basis, you could add phone number in here too. If you just want to have this list of handy-dandy phone numbers to call, you can do that. But I want to make it so when I double click on one of these guys, it opens up the customer record.
So let's set up an on double click event on this list box. If you've never done a double click event before, I have videos on it. Go watch that. In fact, go watch my intro to VBA class if you've never done any VBA programming before. It's real simple, nice and easy. I'll put a link to that down below, too. In fact, I just earlier this week made another video on double click events, so go watch that.
Design view. Open this guy up. Properties-wise, go to All. Let's call this customer favorite list. All right. Event, on double click, right here. Dot dot dot. Pick Code Builder if you're prompted. DoCmd.OpenForm "CustomerF", , , "CustomerID = " & CustomerFavoriteList.
Save it. And now the results: double click on me, boom. Double click on Jean-Luc. There you go.
I'll show you one more little bonus thing. If you double click on one of these guys to open this up, let's say I make me not a favorite now. If I come back over here, this guy isn't updating unless you close the form and then reopen it again. And same thing if you add a record.
So you might want a little refresh down here, or simply a refresh when you double click on one of these guys. Either way, I'll put a little refresh button down here. We'll make it a link, like a little label. Take this guy here, put a little link right there that will say requery, and we'll just go format. We'll make it so we can actually see it, maybe like a light gray. There you go. Slide it right there and then go to All.
Then we'll call this guy requery customer favorite list, or a shorter name if you prefer. This guy will have an event on click, and it's going to be CustomerFavoriteList.Requery. To requery is to go out to the table and refresh your information with the new data that might have been entered. So there's a little bonus for you.
Close that. Open it up again. You can hit requery and nothing seems to happen. But let's go to the customer form. I'll add myself back in as a favorite and then I'll come back over here and add Deanna in. Now I can requery that list to update it.
There you go. That's how you set up a little favorites list. Now you can double click, open someone up, and keep the five or six customers that you contact on a regular basis there.
Want to learn more about setting up a favorite customers list? In the extended cut for members, we're going to expand on this. We're going to add most recent and most viewed. So, every time you open up a customer, it will increment a counter, so you can track the most viewed customers, and it will set your most recent view date.
This way, you can refilter this list and say, show me my favorites, or show me the most recently viewed customers, or show me the top 10 most viewed customers. We'll make it top 10, because if you've got thousands of customers, you don't want that list being gigantic, when you really only care about the most recent or the most viewed customers. Then I'll show you how to requery that list box automatically when you come back to it. So, if you open someone up and it opens up the customer form, as soon as you close the customer form and come back to the main menu, it will requery that list box for you.
That's all covered in the extended cut for members. Silver members and up get access to all of my previous extended cut videos as well, and gold members can download a copy of that database from my website.
How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted.
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.
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 to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long, you can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar, and it's also 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.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.
Quiz
Q1. What is the main goal of the video tutorial?
A. To demonstrate how to back up a database
B. To show how to delete customer records automatically
C. To create a list box for favorite customers on the main menu and open their records quickly
D. To merge duplicate customer records
Q2. What new field does the instructor add to the customer table to designate favorite customers?
A. CustomerRanking
B. IsActive
C. IsFavorite
D. FrequentContact
Q3. What data type is used for the "IsFavorite" field?
A. Text
B. Number
C. Yes/No
D. Date/Time
Q4. Where does the list box get its data from?
A. The customer table directly
B. A query that filters customers marked as favorite
C. A form
D. From Excel import
Q5. Which field is essential to be included in the list box for opening the correct customer record?
A. Customer notes
B. Address
C. CustomerID
D. Phone number
Q6. What is the purpose of the double-click event on the list box?
A. To delete a customer
B. To sort the list
C. To open the selected customer's form
D. To export customer data
Q7. What function is used in the double-click event's VBA code to open the customer form?
A. DLookup
B. DoCmd.OpenForm
C. MsgBox
D. DoCmd.TransferText
Q8. What should be set to zero width in order to hide the CustomerID in the list box?
A. First Name's width
B. IsFavorite's width
C. CustomerID's width
D. Last Name's width
Q9. What additional feature does the instructor add to allow the list box to update after changes?
A. An automatic timer
B. A refresh (requery) button
C. A duplicate remover
D. A search bar
Q10. What does the "Requery" method do in this context?
A. Deletes old records
B. Rearranges fields
C. Refreshes the data in the list box from the source
D. Exports the data to Excel
Q11. Which of the following statements is true about list boxes and combo boxes, according to the instructor?
A. List boxes and combo boxes perform completely unrelated functions
B. Combo boxes can display several records at once, list boxes cannot
C. List boxes are always open; combo boxes can allow typing new values
D. List boxes allow you to type in new values by default
Q12. What bonus features are mentioned as part of the extended cut for members?
A. Sending mass emails to all customers
B. Tracking most recent and most viewed customers
C. Exporting the favorite list to Word
D. Scheduling automatic phone calls
Q13. Who can access the downloadable sample databases used in TechHelp videos?
A. Anyone with a YouTube account
B. Only Gold members
C. Only Silver members
D. Only Platinum members
Answers: 1-C; 2-C; 3-C; 4-B; 5-C; 6-C; 7-B; 8-C; 9-B; 10-C; 11-C; 12-B; 13-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 video from Access Learning Zone focuses on creating a favorites list box on your main menu to quickly access your most frequently contacted customers in Microsoft Access. This is particularly handy if you have a handful of customers you reach out to regularly and want immediate access to their records from your main menu.
The inspiration for this lesson comes from a member named Katrina, who asked about displaying a short list of favorite customers right on the main menu for speedy access. My solution is to add a field to the customer table to indicate which customers are favorites. Once you have that field in place, you can put a list box on your main menu to display just your favorites. Then, using a double-click event on the list box, the database will open the customer's record instantly.
Starting with the example database from my TechHelp free template, I already have a customer list and functionality to double-click and open individual customer records. To indicate favorites, I added a new "IsFavorite" yes/no field to the customer table, similar to the existing "IsActive" field. By default, this field is set to no. Then, I added the "IsFavorite" checkbox to the customer form so you can easily mark or unmark customers as favorites.
Now that you can identify who your favorite customers are, the next step is to display them on the main menu. First, I created a query that filters only the favorite customers by checking if the "IsFavorite" field is true. I named this query "CustomerFavoriteQ," and I recommend keeping names singular for consistency and simplicity.
To present this list on the main menu, I designed a list box that pulls its data from the "CustomerFavoriteQ" query. In the setup, I included the customer ID, last name, and first name, sorted them appropriately, and adjusted the widths so the customer ID remains hidden from view but is still available for use when opening records. If you call your contacts regularly, you could also add their phone numbers to this list.
To open a customer's record quickly, I set up an event that triggers on double-clicking an entry in the list box. This uses the customer ID from the list to launch the customer form filtered for the selected individual. This process uses a simple VBA procedure—if you are new to VBA or double-click events, I have separate introductory videos that you can watch for more detailed guidance.
As a bonus, I added a "Requery" button, which can be useful if you change someone's favorite status or add new customers while the menu is open. Clicking this button refreshes the list so it always stays up-to-date without needing to close and reopen the form.
This approach gives you a handy rundown of your preferred contacts, always just a double-click away. You can modify your favorites as needed, and the list will update accordingly.
For those who want to take this concept further, in the extended cut for members, I go beyond simple favorites. I demonstrate how to track and display your most recently viewed and most frequently viewed customers. We'll update the database each time you open a customer record—adding a counter for most viewed and a date stamp for most recently accessed. You can then filter the list to show your favorites, the most recently viewed, or the top ten most interacted-with customers. This is ideal if you handle a large volume of customers but want to keep your quick-access list concise. Additionally, I cover how to automatically refresh the list when you return to the main menu after viewing or editing a record.
All of these features are included in the member's extended cut. Silver members and above gain access to all these extra training videos, while gold members can download the accompanying sample databases. Platinum members receive these perks plus all my full beginner and some expert courses across Access, Word, Excel, Visual Basic, ASP, and more.
Even if you're not a member, you'll continue to get free TechHelp videos regularly. If you enjoyed this lesson, give it a thumbs up and leave your comments—I do read every one. You can also subscribe to my channel for free and turn on notifications to stay updated on new releases. For additional resources, related videos, and bonus material, check the links below the video.
If you want to receive email notifications when I post new content, join my mailing list. And if you have not tried my free Access Level 1 course, it covers the basics of creating databases and lasts over three hours. It's available both on my website and YouTube. The next level is just one dollar or free for any level of channel membership.
If you have questions you'd like answered in a future video, visit my TechHelp page to send them in.
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
Adding an IsFavorite field to the customer table
Setting the default value for the IsFavorite field
Putting the IsFavorite checkbox on the customer form
Formatting the IsFavorite checkbox
Marking customers as favorites using the new field
Creating a query to show favorite customers only
Designing a list box to display favorite customers
Populating the list box with query results
Hiding the customer ID column in the list box
Setting the bound column in the list box wizard
Customizing the appearance of the list box
Implementing a double click event to open the customer record
Writing VBA to open the customer form using the selected ID
Adding a refresh (requery) label to update the list box
Adding a click event to requery the favorite customers list box
Testing and using the favorite customers quick access list
|
