|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 26 Lessons Welcome to Access Developer 26 Total running time so far is 2 hours, 14 minutes. Lessons
Database FilesLinksResources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn lesson 26 of my Microsoft Access Developer course, we start with a bug fix from Developer 25, then build two advanced customer search forms. First, you'll see how to set up a basic search form with custom sorting and filter boxes. Next, we move on to a more advanced search form using a criteria table and recordsets for dynamic filtering. We'll cover sorting improvements, field-specific filters, AND/OR dropdowns, storing search criteria in tables, looping through fields, dynamic control building, and more advanced form design techniques.TranscriptWelcome to Microsoft Access Developer Level 26, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's class, we are going to start off with a bug fix that one of my students discovered from Developer 25. Then we are going to build two different types of search forms. The first one is a simpler form that has some custom sorting on it and some custom filter boxes. The second search form is going to be that one on steroids. We are actually going to make a criteria table where you can put all kinds of criteria in a separate table and then use that table with a recordset to filter the original list of customers. There is some really cool stuff in this lesson. This class, of course, follows Access Developer Level 25. I strongly recommend you take that class first before this one. It is also recommended that you have taken all my Beginner, Expert, Advanced, and other Developer classes too. I am using Microsoft Access 365, part of a 365 subscription. It is roughly equivalent to Access 2019. Access 2016 will work just fine too. In fact, the lessons covered in this class should go all the way back to about 2007 without any problems, but do not quote me. I only support 2016 and 2019 at this level. If you have questions about the material covered in this lesson, just scroll down to the bottom of the page and post them right there in the questions box. As always, if you have any questions about stuff in Access that is not related to the material in this class, go ahead and post it in the Access Forum. Let's take a closer look at what is covered in today's class. In lesson one, we are going to start off with a bug fix, something that was discovered in Developer 25. We are going to make inventory calculations ignore product groups when we go to ship an order. In lesson two, we are going to begin building an advanced search form to search through our customers. We will start off by building a basic, unbound form with a customer list box on it. The first thing we will do is work on sorting. You will be able to click on any of these labels in the column headers to sort based on a field. We will sort the null values at the bottom, which is different from how Access normally treats them. It normally sorts null values at the top, which I do not like. We will also make it so it colorizes whichever one of these columns is the sort field, and then reverse that from blue to red when we sort descending. Lots of cool stuff in this lesson. In lesson three, we are continuing on with our search form. We are going to put field filter boxes across the top of each column so you can type in a letter or a string and it will filter based on whatever you type in. We will put a clear button at the end so you can clear those filters. We will build dynamic WHERE conditions into our SQL so we can say use this field and/or this field and/or this field. We will use an and/or dropdown box so you can pick an AND or OR condition for your search. Lots of cool stuff in this lesson too. In lesson four, we are continuing on with our search form. We are doing something completely and totally brand new. We are going to make a table, and the table will store the search criteria. We are going to loop through the records in that table to create the WHERE condition for the search form using, guess what, a recordset. You did not think you were getting out of Developer 26 without at least one recordset, did you? There is lots of stuff in lesson four here. Brand new stuff. In lesson five, we are continuing on with the search form. We are going to loop through all the fields in the customer table and fill them into a combo box so we can pick from any field in the list. Then, based on the data type of that field, if it is text we will allow the LIKE condition to be possible. If not, it will just be the equality stuff. Then we will put the criteria in, add the criteria, save it in the table, refresh the main list, and then, of course, we will make a delete button. Lots of cool stuff in this one too. QuizQ1. What is the first topic covered in Access Developer Level 26?A. Creating a new customer table B. Bug fix related to Developer 25 C. Building report templates D. Exporting data to Excel Q2. What main feature distinguishes the second, advanced search form discussed in the lesson? A. It is a bound form to customer data B. It uses a separate criteria table and recordset for filtering C. It allows direct editing of customer records D. It integrates with external APIs Q3. What is specifically different about the custom sorting implemented in the new search form compared to standard Access sorting? A. It sorts dates in descending order only B. It ignores sorting for numeric fields C. It sorts null values to the bottom instead of the top D. It does not allow any custom sorting Q4. In lesson three, what new functionality is added to the search form? A. Export to PDF option B. Field filter boxes above each column for typing search strings C. Automated email sending D. Attachment support for customer records Q5. What does the 'and/or' dropdown in the search form allow users to do? A. Select between exporting and importing data B. Pick between AND or OR conditions for filtering C. Adjust font size of the form D. Switch between light and dark mode Q6. How are the search criteria stored in the advanced search form taught in lesson four? A. In the main customer table B. User properties C. In a separate criteria table D. In a text file Q7. What is used to loop through the criteria and generate the WHERE condition for filtering in lesson four? A. Macro groups B. Recordset C. Query wizard D. Action queries Q8. When adding searchable fields to the combo box in lesson five, what determines if LIKE conditions can be used? A. Field length B. Field name C. Field data type (text) D. Field sort order Q9. What is the purpose of the delete button added in lesson five? A. Remove the customer from the database B. Delete the entire criteria table C. Delete a specific search criterion from the table D. Remove all customers from the list Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-C; 7-B; 8-C; 9-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. SummaryToday's video from Access Learning Zone is Microsoft Access Developer Level 26. My name is Richard Rost, and I will be guiding you through today's lesson.We will begin by addressing a bug one of my students found in the previous course, Developer Level 25. After fixing this issue, I will walk you through how to design two different customer search forms that illustrate some advanced techniques. The first search form you'll learn to build is a straightforward one, but it features custom sorting and filter options. This provides an easy way to search and organize customers with a few clicks. However, the second search form is much more advanced. Here, I will show you how to set up a separate criteria table to collect and manage different filters. Then we will use a recordset so that searching and filtering customers becomes much more powerful and dynamic. There are some impressive techniques included in this class that you will not want to miss. Before tackling this material, make sure you have completed Developer Level 25, as this class builds directly on concepts from that course. I also recommend you finish my Beginner, Expert, Advanced, and earlier Developer classes if you have not done so already. Throughout this series, I will be using Microsoft Access 365 which is part of the Office 365 subscription. This is very similar to Access 2019, and even Access 2016 will work fine for everything we do here. In fact, most of the lessons should be compatible all the way back to Access 2007, but my official support is for Access 2016 and 2019 for these advanced topics. If you have any questions about content from this class, scroll down and post them in the questions box at the bottom of the page. For questions not directly related to today's lesson, feel free to use the Access Forum. Here's a breakdown of what we will cover in each lesson: Lesson one starts with our bug fix from Developer 25. I will show you how to change the way inventory calculations work so that product groups are ignored when you ship an order. Lesson two is where we start building our advanced customer search form. You'll see how to create a basic unbound form with a list box displaying customers. Special attention is given to sorting, allowing you to click any column header to sort by that field. Unlike the default Access behavior, we will send null values to the bottom of the list. The current sort field will be highlighted, and the color will switch from blue to red depending on whether the sort is ascending or descending. In lesson three, we expand on this search form by adding filter boxes above each column. These let you type in a value to filter by that particular field. There will also be a clear button to remove all filters at once. I will show you how to dynamically build SQL WHERE conditions so you can choose which fields to use for filtering. Additionally, you will be able to switch between AND and OR logic, so your searches can be as broad or as narrow as you need. Lesson four introduces an entirely new approach: storing your search criteria in a dedicated table. We will use a recordset to loop through these criteria and construct the WHERE condition for the customer search form. This allows for highly flexible and complex searches, taking the search form to a new level. Yes, working with recordsets is a key skill covered in Developer Level 26, so expect to see some new techniques here. For lesson five, we continue to enhance the search form by automating the process of populating a combo box with the names of all fields from the customer table. You will be able to select any field, and the search options presented will depend on the data type of that field. If the field is text, the LIKE operator is used; otherwise, different conditions will be available. You will see how to add, save, and delete criteria from the table, with the main customer list updating each time. 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 ListFixing inventory calculation to ignore product groups when shippingBuilding a basic unbound customer search form Adding a customer list box to the search form Implementing clickable column header sorting Custom sorting with null values at the bottom Highlighting sorted columns with color Reversing column color for descending sort Adding filter text boxes above each column Filtering customer list based on user input Adding a clear button to reset filters Building dynamic WHERE clauses in search SQL Creating an AND/OR dropdown for filter conditions Storing search criteria in a new table Looping through criteria table records with a recordset Generating SQL WHERE conditions from criteria table Populating a combo box with customer table fields Detecting field data type to adjust search operators Allowing LIKE operator for text fields Adding and saving search criteria into table Refreshing customer list based on saved criteria Implementing delete functionality for search criteria |
||||||||||||||||||||||
|
| |||
| Keywords: access developer 26 lessons PermaLink How To Build Advanced Search Forms With Dynamic Criteria Tables And Filtering In Microsoft Access |
