|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Hide Inactive By Richard Rost 
5 years ago
Hide Inactive Customers from Combo Boxes In this video, I will show you how to hide inactive customers from showing up in combo boxes on your various forms, such as your order form. If the customer is dead or has moved out of the area, and is no longer an active customer, you don't want to see them in every combo box, BUT you shouldn't delete their record either. Luis from Santiago, Chile (a Gold Member) asks: I have about 10,000 customers in my database. Around a third of them are no longer active (died, moved out of the area, etc.) however they still show up in my combo boxes. I don't want to delete these customers for accounting purposes, so how can I hide them? MembersMembers will learn how to have that customer's record still show up in the combo box for old orders even though that customer is marked inactive. We will also learn how to switch the customer list box to show just active or ALL customers. Maggie from Williamsport, Pennsylvania (a Gold Member) asks: If a customer is marked inactive, their name no longer shows up on any of the orders they have. How do I keep their name on the order but not have it show in the combo box for new orders?
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!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, hide inactive records, Combo box, select only active records but allow view of inactive records
IntroIn this video, I will show you how to hide inactive customers from combo boxes in your Microsoft Access forms. We will use the IsActive field to filter out customers who are no longer active, making it easier to select from only current customers when creating new records like orders. I will demonstrate how to update your combo box data source using a query that only includes active customers, and explain why you should avoid deleting old customer records from your database.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to hide inactive customers from your combo boxes in your Microsoft Access forms.Today's question comes from Luis in Santiago, Chile, one of my Gold Members. Luis says, I have about 10,000 customers in my database and around a third of them are no longer active. They either died, moved out of the area, etc. However, they still show up in my combo boxes. I do not want to delete these customers for accounting purposes. So how can I hide them? You have the right idea. You do not want to delete customers, especially if they have contacts or orders in your system, because once you delete them, then all the related records become garbage. For those of you who have not watched my video on soft delete, go watch that now, and also watch my video on relational combo boxes if you have not yet. Let us see how we handle this problem. Here I am in my TechHelp free template. This is a free download from my website. If you want to grab a copy and use it, I will put a link down below in the links section. In our customer form, we already built in the solution to this problem. I have a field here called IsActive. If you look at the customer table, you can see IsActive is right there. A couple of customers are marked active, and a couple are marked inactive. We really did not do anything with this. I just put it in there so we had a yes/no field, a checkbox to work with for our sample classes. But if I go to make an order, I do not want to necessarily have to look down this entire big long list. The way we have it set up right now, you would go to the customer record and then you would hit orders or contacts, hit orders, and they would show up right there. Let us say hypothetically you go right to the order screen. You can open up a blank order, go to a new record, drop this down, and you do not want to see everybody. If you have got 10,000 customers in your database and a third of them, over 3,000 customers, are inactive, that is a lot of wasted space in there. All you really have to do is use that IsActive field and just do not show those people in this list. Let us take a look at how this combo box gets its data. We used the wizard to build this, but if you open up the properties and go to Data, you will see a select statement, an SQL statement. It says SELECT. Basically, you can ignore the first part. It says SELECT CustomerID and LF from the CustomerLFQ. What is CustomerLFQ? All this is, is it concatenates together the first name and the last name, so it is LastName, FirstName. If you watch the video where I build the TechHelp free template, you will see how I put this together. There it is, a little string concatenation. Go watch this video if you have not already. All we have to do now with this query is bring IsActive into it and set the criteria to TRUE. Or you can make another separate query if you want to. You might want to make a different query, because some combo boxes you might want to have all the records and some you might want to have just the active customers. Maybe let us leave this guy alone. Let us do this. Save changes? No. Let us copy this and paste it. So, copy, paste, Control-C, Control-V. We will call this one CustomerLFActiveQ. So we have two of them now. This one we will modify in design view. We will add Active in here and we will set the criteria to TRUE. Now when I run this, I see just the active customers. Save that. You can either modify this box if you want to or you can rebuild it with the wizard. I am just going to modify this SQL statement. Learn SQL, folks. If you are going to be working with databases, learn SQL. It is not that hard. It is real simple. I will put a link to my SQL basics video. It is free and will teach you what a SELECT statement is and how to formulate it properly. You have to learn a little SQL. I did not learn SQL until I had been working with Access for probably 10 years, and I wish I would have learned it sooner. We can simplify this. We can get rid of all of this stuff here because we have only one table or query in here. We can make this simpler to read, like this: CustomerID, LF from CustomerLFQ ORDER BY LF. Now we are just going to change this and we are going to pull the data instead from CustomerLFActive. We are just going to change what query we are getting our data from. Save this. Close it. Now, if I just open up the order form and go to add a new record, I drop this down and there you go. Now you will see just the active customers. Notice Deanna Troi is not in there. If we go to the customer table, there is Deanna and she is not an active customer. If I drop this down to add a new order, she does not show up. That, in a nutshell, is how you go about hiding inactive customers. You do not want to delete Deanna. You do not want to delete her record because you might still have contacts in here you want to keep track of, or she might have older orders. With this being said, there is one minor problem that you might run into. Let us say Deanna is active and she has an order in the system, two gallons of BetaZ cream. What is BetaZ cream? I do not know, but it is expensive. She has an order in, it is paid, and you have invoiced her. Later on, she moves. She moves to Rigel V or whatever. You mark her inactive because she is no longer a customer. Now, if you go to her order, look at that. She does not show up in the combo box because the combo box only lists active customers. So how do you add it so that she shows up in this list alongside all the other active customers? That is going to involve a couple of lines of programming, and I will cover that in the extended cut for the members. In the extended cut, I will show you how to put Deanna back in the combo box, even though she is inactive for her order. We will also make little Active, Inactive, and All links down here so you can change the customer list and see who you want to see. Here is my customer list. Right now it shows everybody, but I can go down here and click on Active, Inactive, or All. If I open up Deanna, even though she is inactive and I go to the order form, you can see she still shows up in the combo box along with the other active customers. How do you do all that? That is covered in the extended cut for the members. Silver Members and up get access to all of my extended cut videos. Gold Members can download these databases. How do you become a member? Click the Join button below the video. After you click the Join button, you will 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. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will 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 will 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 would 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 is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar. It is 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. QuizQ1. Why should you avoid deleting inactive customers from your Access database?A. Deleting inactive customers frees up space in your database B. You might lose related records such as orders or contacts C. It improves the performance of combo boxes D. It is required for auditing purposes Q2. What is the purpose of the 'IsActive' field in the customer table? A. To store the address of the customer B. To indicate whether the customer is active or inactive C. To log customer creation dates D. To keep track of customer payments Q3. When creating a combo box for selecting customers in an order form, how can you hide inactive customers? A. Delete all inactive customers from the table B. Filter the combo box source to only include records where 'IsActive' is TRUE C. Use a password-protected field D. Set the combo box to 'Read Only' Q4. What is the advantage of creating a separate query like 'CustomerLFActiveQ' for your combo box source? A. It permanently deletes inactive customers from view B. It allows you to selectively show only active customers in certain forms or combo boxes C. It prevents users from changing customer data D. It automatically adds new customers Q5. If you need to display both active and inactive customers in different combo boxes, what should you do? A. Use the same query for all combo boxes B. Create different queries for each case, one for all customers and one for only active customers C. Delete inactive customers before opening forms D. Configure combo box properties to auto-detect active status Q6. Why is learning SQL recommended when working with Access databases? A. It helps with creating more complex spreadsheets B. It allows you to better format forms and reports C. It makes creating and customizing queries and combo boxes easier D. It is only useful for web programming Q7. What could be a minor issue after filtering the combo box to only show active customers? A. The order table will be deleted B. Inactive customers cannot be seen anywhere in the application C. Existing orders for inactive customers may not display their customer in the combo box D. Only inactive customers are shown in reports Q8. What feature is mentioned as being available in the members-only extended cut of the video? A. How to format customer phone numbers B. How to allow inactive customers to appear in the combo box for their own orders C. How to import customer data from Excel D. How to change form color themes Q9. What benefit do Gold Members receive as part of their AccessLearningZone.com membership? A. Ability to schedule private lessons B. Access to download folders with sample databases and the code vault C. Discounted hardware purchases D. Free certification exams Q10. How can users ensure they get notified when new AccessLearningZone.com videos are published? A. Sign up for text message alerts from YouTube B. Subscribe to the channel and click the bell icon to select All notifications C. Enable automatic app updates in Windows D. Download every video manually Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-C; 7-C; 8-B; 9-B; 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. SummaryToday's video from Access Learning Zone covers how to hide inactive customers from appearing in your combo boxes in Microsoft Access forms. I received a question from a viewer who has around 10,000 customers in his database, but about a third of them are inactive for various reasons. He does not want to delete these records, especially for the sake of maintaining accurate accounting and keeping related records like orders and contacts intact. The question is, how can you keep these inactive customers out of your selection lists without removing their data?First of all, it is important to avoid deleting customers from your tables if they have any related records. Removing them can cause issues with data integrity, especially for accounting and historical reference. If you have not already watched my videos on the soft delete technique and on relational combo boxes, I strongly recommend you check those out for background information. The soft delete method simply means marking records as inactive rather than removing them completely. Let me walk you through the solution in the TechHelp free template, which you can download from my website. This template has an "IsActive" yes/no field in the customer table. This field is designed specifically for situations like this. Some of the customers are marked active, and some are not. Although this field does not change how the data is stored, it provides a simple way to filter the records that appear in a combo box. Currently, if you try to create a new order, you might notice that every customer, whether active or not, shows up in the dropdown list. With thousands of inactive customers, this can make selection needlessly difficult. The key is to adjust the data source for the combo box to only include active customers. Typically, the combo box is built using a query created by the wizard. When you examine its Row Source property, you will see an SQL SELECT statement that pulls in the customer ID and a concatenation of their first and last names from a query we have set up called CustomerLFQ. This query simply combines the names for display purposes. To hide inactive customers, you just need to bring the IsActive field into this query and set its criteria to TRUE. Alternatively, to give yourself more flexibility, you may wish to create a separate query—say, CustomerLFActiveQ—that only contains those marked as active. This way, you can choose which combo boxes display all customers and which show only the active ones. To do this, copy the original query, add the IsActive field, set the criteria to TRUE, and save the new query. After this, update the combo box Row Source to reference the new query instead of the original one. If you are comfortable with SQL, you can adjust the SELECT statement directly in the property sheet. Mastering a little SQL is extremely helpful for any Access developer, and I suggest you check out my free video on SQL basics if you are unfamiliar with how this works. Once you make this change, you will see that when you open the order form and try to select a customer, only those who are active will appear in the list. For example, if a customer is marked inactive in the customer table, they will no longer show up in the combo box for new orders. This approach solves the main problem nicely: inactive customers no longer clog up your lists, but their records remain safe within your database for historical purposes or other references. However, there is a minor issue you might encounter—suppose a customer, Deanna for instance, has historical orders in the system. If Deanna becomes inactive after making a purchase, and you go to view her past orders, her name will not appear in the combo box because the source is now filtered to only show active customers. This can create confusion if you need to reference her previous transactions. To address this scenario, you need a slightly more advanced solution that involves a bit of programming. In the extended cut of this video, I demonstrate how to ensure that customers like Deanna, who have historical records, can still appear in the combo boxes for their orders even after being marked as inactive. Additionally, I show how to add simple links or buttons that let you toggle the list to show only active, inactive, or all customers as needed. If you want access to the extended cut where I cover these extra features, along with other perks such as downloadable sample databases and access to my code vault, consider joining as a Silver or Gold Member. Members gain access to all the extended cut TechHelp videos as well as other training materials for Access and other Microsoft Office applications. My free TechHelp videos will always remain available for everyone. If you appreciate these tutorials, please subscribe to my channel, turn on notifications, and check out the resources and related videos linked on my website and YouTube channel. If you prefer email notifications, you can join my mailing list as well. If you are just getting started with Access, try my Access Level 1 course for free. It covers the basics in detail and is over three hours long. Additional levels are affordable and free to members. If you have questions you would like me to answer in a future video, visit my TechHelp page and submit your inquiry. 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 ListIdentifying and using the IsActive field in customer tablesModifying combo box row source to filter active customers Creating a new query to show only active customers Setting criteria in query to include only active customers Changing the combo box to use the filtered query Testing the filtered combo box in the order entry form Explaining why not to delete inactive customer records Demonstrating how filtered combo box hides inactive customers |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access hide inactive records, Combo box, select only active records but allow view of inactive records PermaLink Hide Inactive in Microsoft Access |
