|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
ABCD Core Part 4 Access Business & Contact Database Part 4 Welcome to ABCD Part 4: Core. Total running time so far is 4 hours, 6 minutes. Lessons
Contents
Database FilesI'm only posting the 32-bit versions for now. That's what I use. If you are using 64-bit Access, just create a new blank database file and import all of my objects into yours. I'll post a 64-bit version when the Core is finished. If you experience any code that doesn't work under 64-bit let me know. Click here for more information about 32 v 64 bit.
ResourcesLearn MoreI need to start making a proper outline page for the ABCD parts like the Developer lessons. I'm posting this here for the time being: 2. Advanced Filter 1 (32:52) 3. Advanced Filter 2 (31:25) 4. Advanced Filter 3 (32:38) 5. Advanced Filter 4 (39:31) 6. Advanced Filter 5 (26:25) 7. Advanced Filter (24:58) Enroll Today!Enroll now so that you can watch these lessons, learn with us, post questions, and more. Pre-Requisites RequiredIn order to purchase any level of the ABCD, you MUST have purchased all previous levels. For example, you cannot buy only Level 5. You must first have Levels 1 through 4, then you can buy Level 5. Sorry, but this is a hard pre-requisite. VideosI am recording videos showing how the database is built, but mostly so that you can understand the construction and be better equipped to make any modifications or additions you may need on your own. This will be marketed as a finished database without the need for any enhancements, but, of course, one of the main reasons to use Access is so that you can make changes yourself. The videos will be a quick run-through of how each feature is built, as I'm building it. I will be using advanced features like SQL and VBA from the start, so you may want some SQL or VBA Developer Lessons under your belt if you're going to try to follow along. This is NOT a teaching database. Free Technical Support will NOT be offered. Tech SupportThere is NO free Technical Support provided on the ABCD if you have not taken ALL of my previous Developer lessons. There are a lot of very difficult, advanced programming concepts used in the construction of this database. I do not have the time to provide free support if you get stuck trying to make modifications. All I will do is point you to the appropriate Developer lesson(s) that cover the issue. There is a reason why (a) I say this is NOT a teaching database, and (b) there are pre-requisites. Feel free to ask questions, but I can't spend half an hour explaining concepts that are already covered in my other lessons. LicensingUnlike my other courses and seminars which provide you with royalty-free databases that you can modify and distribute yourself, the ABCD does NOT come with a royalty-free license. You may only customize the database for your business needs and for use within your organization. You may not resell or distribute it to others without express written permission. In addition, if you are using it in an organization with more than 10 users, you must purchase additional licenses. Contact me for additional information. I will post additional details when the Core database is completed. OrderingThere are two purchase options available for the ABCD.
Your FeedbackI will be releasing new Core features and modules on a regular basis. You can see the list of features that are scheduled for development on the Coming Soon page. I would very much like your feedback on what features you think should be available. If you have a specific interest for your business type, let me know.
IntroIn this lesson, you will learn how to add powerful filtering, sorting, and searching features to the Entity List form in the Microsoft Access ABCD database. We will cover building filter and sort controls for different fields, implementing advanced search options for various field types including dates and related tables, handling phone number formatting, grouping results to remove duplicates, and finishing with a printable report that reflects your selected filters and sorts. This is part 4.TranscriptWelcome to part four of the Access, Business, and Contact database brought to you by accesslearningzone.com. I am your instructor, Richard Rost.If there's not a whole lot to look at on this title screen, that's because there really isn't. This is about four hours of programming and coding, and it doesn't make for a very good screenshot. So yeah, we're working on the Entity List form. We're adding in filtering, sorting, searching, all that stuff. Then when we're done, we'll make a printable report based on the results of that form. Let's take a quick look and see what's coming up today. One of the most important things in any database is being able to find the information that you're looking for quickly. In lesson one, we're going to build basic filtering and sorting capabilities directly into our Entity List form. First, we're going to build filter boxes across the top of each column. That way, we can filter based on first name, last name, organization, or the person code. Then we're going to make sort labels so we can click on them to sort by last name, by first name, by organization, or whatever we want to sort on. If it's already sorted based on that, it'll flip it and sort descending. Instead of 8 or 0, it'll go to 8. Then we'll make a button up top, and you can see the little x there to clear all the filters. In lesson two, we're going to start advanced filtering. We'll add a search fields section to our Entity List form so we can pick a field to search on, including the fields that aren't on the search form to begin with, like middle name and notes. You'll pick which field you want to search on from that list. You'll pick the operation, less than, greater than, like, for example. Then type in the value that you want to search for. In lesson three, we're going to deal with handling date searches. If you want to search for date of birth, for example, or date created, we're going to take care of searching in the helper data from the Entity table, such as gender, prefix, suffix, etc. We'll have to make a combo box to list the appropriate search items based on which field you pick, then search that box accordingly. Lesson four is where it starts to get interesting. We're going to add the other tables to our search criteria, so we're going to add email address, phone numbers, and the street address fields so we can search them with the same box. Therefore, we have to join our tables together in the SQL. In addition, we're going to strip out anything in the phone number field except for the digits. If someone types in something like 716-222, for example, we'll get rid of that too. Lesson five continues to get even stranger. Now we're going to make it so we can search through all of the miscellaneous data values in the system too. So if you want to search for someone's start fleet rank, shoe size, or favorite baseball team, now you can pull up that list of people as well. We'll switch it so that the list of fields goes from the Entity type field to the miscellaneous data fields. Then, on the bottom, that combo box will show the available options for that miscellaneous data type. I don't like seeing duplicate values for entities that might have multiple valid records in related tables. For example, if I search for state New York, I show up three times - Richard Rost - because I've got three different addresses. So in this lesson, we're going to add a group by to get rid of duplicate values from showing up. And we'll fix the Entity list column so that when you pick miscellaneous data and you pick a value like start fleet rank, Admiral will actually show up in that column. It wasn't showing up before. That's in lesson six. In lesson seven, we're finishing up with the advanced filter. We're never really quite finishing up with it because I have a feeling we're going to be adding features to this form for the rest of the development days of this database, but we're finishing up with it for now. There are a couple of bug fixes to go over and some changes that I made. Then we're going to add an all and none option to our helper combo box option so we can say show me all, show me none. Then we'll do a printable report, which will get its data source from the form so all of these sort options and the filtering and everything else that we selected goes right into that report without having to design all of that separately. That's coming up in lesson seven. As a reminder, there are three prerequisites for this class. ABCD parts one, two, and three are required. Unlike my regular courses, where you can jump around and skip around and buy whatever you want, these you have to get in order. If you don't have part one, you can't buy part two, and so on. So for part four, or this one, you've got to have one, two, and three to go with it. Now, the recommended prerequisites are all of these: beginner, expert, advanced, and developer. You don't need them, but I strongly recommend it if you want to understand everything that I'm doing. I will be using Microsoft 365, which is roughly equivalent to Access 2019. I just got 64-bit Access on one of my machines, so starting with the next version, the ABCD version five or part five, I should say, I will be converting this up to 64-bit. I might do this one to 64-bit just to test it. If you have any questions, of course, post them down below on the student page in the forums. Also, make sure to visit the Access forums if you have any questions not related to this class. If you want to get the ABCD customized for your needs, contact me. I can either add it as a module if I think other people will be interested in it, which is coming up next in part five, or I can build something custom for you if your needs are very specific. There's my email address if you need it. Let's get started with ABCD Part Four. QuizQ1. What is the main feature being added to the Entity List form in this video?A. Multi-user access control B. Filtering, sorting, and searching capabilities C. Advanced encryption options D. Automated data backups Q2. How will users be able to filter data on the Entity List form? A. By selecting colors for each row B. By using filter boxes across the top of each column C. By typing directly into the navigation bar D. By exporting data to Excel Q3. What does clicking a sort label in the Entity List form do? A. Deletes the current record B. Sorts the list by the selected column and toggles ascending or descending order C. Sends an email alert D. Changes the field data type Q4. What functionality is added in lesson two related to searching? A. Only allows searching using checkboxes B. Adds a section to pick which field to search on, including fields not on the form C. Removes all search options D. Restricts searching to text fields only Q5. How are date fields handled in the advanced search? A. By disabling date searches B. By converting all dates to text C. By creating a combo box to select the relevant date field and value D. By removing date columns from the table Q6. What additional tables are included in the search in lesson four? A. Only inventory tables B. Email addresses, phone numbers, and street address fields C. Payroll and sales data D. Marketing campaign tables Q7. How does the search handle phone number entries? A. Leaves all punctuation intact B. Strips out anything except digits C. Only accepts four-digit numbers D. Converts numbers to text Q8. What issue does lesson six aim to resolve when searching related data? A. Inability to search at all B. Complexity when users try to print results C. Duplicate entities showing up due to multiple related records D. Missing help documentation Q9. What is added to the combo box options in lesson seven? A. Only the 'All' option B. Only the 'None' option C. Both 'All' and 'None' options D. No changes are made Q10. How is the printable report in this course generated? A. Separately from the form, using a static layout B. Takes its data source from the form with all filters and sorting applied C. Only prints unfiltered data D. Exports data directly to Word for printing Q11. What are the prerequisites for enrolling in this course? A. Just ABCD part one B. Any other course from AccessLearningZone C. ABCD parts one, two, and three D. None, you can start with part four Q12. Which version of Access is primarily being used for this course? A. Access 2007 B. Access 2013 C. Microsoft 365 / Access 2019 D. Access for Mac Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-C; 9-C; 10-B; 11-C; 12-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 covers part four of the Access, Business, and Contact Database (ABCD) series. I am your instructor, Richard Rost.There really is not much to show visually on a title screen for this lesson because most of the work involves about four hours of programming and writing code, which does not always make for an interesting screenshot. The main focus of this installment is working on the Entity List form, adding robust filtering, sorting, and searching features. Once those are complete, we will also create a printable report based on the form's results. Let me outline what you can expect in today's lessons. First, finding information quickly is essential in any database. In our initial lesson, I will be building basic filtering and sorting tools directly into the Entity List form. We will create filter boxes at the top of each column so users can filter by first name, last name, organization, or person code. Sort labels will allow easy sorting by last name, first name, organization, and more. Clicking a sort label will sort by that field, and if it is already sorted, clicking again will reverse the order. There is also a button to clear all filters instantly. In lesson two, we take on advanced filtering. A new search section in the form will let you choose a field to search, including options not present on the form like middle name or notes. You will pick the field, the search operation (such as less than, greater than, or like), and then type in the value you want to search for. Lesson three focuses on searching for dates. For example, if you want to find records by date of birth or creation date, this will cover how to search both specific fields and helper fields from the Entity table such as gender, prefix, and suffix. I will walk through making a combo box that lists the right search items for each field, handling searches accordingly. When we reach lesson four, we extend our search to include related tables. We will add email, phone numbers, and street addresses to the searchable criteria, letting users search all of these using the same search area. To achieve this, we will join tables together in our SQL. Additionally, for phone number searches, we will ensure that only digits are considered, removing any formatting characters that may be present. Lesson five expands our search capabilities even further. You will be able to search through all miscellaneous data values in the database. Whether you are looking for a record based on items like star fleet rank, shoe size, or favorite baseball team, this feature allows you to bring up those lists as well. The field selection combo box will switch from standard fields to the available miscellaneous data fields, and then you can pick from the matching values. A common issue is seeing duplicate entries for entities that have multiple records in related tables; for example, if there are multiple addresses, you might see the same person appear multiple times. In lesson six, I will show you how to use the group by feature to prevent duplicate values from displaying. We will also adjust the Entity list so that, when you select a miscellaneous data field such as star fleet rank, the correct value appears in the list. By lesson seven, the advanced filter is functionally complete, at least for now. While we may continue to add more features over time, for the moment this lesson will address a few bug fixes and small changes that came up. We will then add "all" and "none" options to the helper combo boxes so you can easily display all options or hide them. After finishing the filter features, we will move on to designing a printable report. This report uses the form as its data source, so all of your filtering and sorting selections will be reflected in the printout without having to redo the work separately. Keep in mind there are three required prerequisites for this class: ABCD parts one, two, and three. These must be taken in order, unlike my regular courses where you can jump around. If you want part four, make sure you already have parts one through three. I also strongly recommend my Access Beginner, Expert, Advanced, and Developer lessons. These are not required, but will make it much easier to understand everything I demonstrate in this course. For software, I will be using Microsoft 365, which is essentially the same as Access 2019. I now have 64-bit Access installed and starting with ABCD part five I will be updating to use 64-bit. I may test part four in 64-bit as well. If you have any questions, you can post them on the student page or in the forums. For everything not directly related to this class, the Access forums are the best place to ask questions. If you are interested in having the ABCD customized for your needs, feel free to contact me. If it is a feature that others might benefit from, I may add it in a future module, or I can build a custom solution for you. With that, let's begin with ABCD part four. 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 ListBuilding basic filtering in the Entity List formAdding filter boxes to each column Implementing clickable sort labels for sorting Clearing all filters with a button Creating advanced filtering with search fields Allowing searches on fields not displayed in the form Selecting search operators like less than, greater than, and like Handling searches on date fields Searching helper data such as gender, prefix, and suffix Creating dynamic combo boxes based on selected search field Joining related tables in SQL for expanded search Adding email, phone, and address fields to search criteria Stripping non-digit characters from phone number searches Searching through miscellaneous data values Dynamically updating field and value combo boxes for misc data Eliminating duplicate entities using group by in SQL Displaying correct values in the Entity List for misc data fields Adding all and none options to helper data combo box Creating a printable report based on form filters and sorting |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access ABCD 04 part 04 core PermaLink Access Business & Contact Database Core Part 4 |
