|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 37 Lessons Welcome to Access Developer 37. Total running time is 1 hour, 58 minutes plus 55 minutes of FREE bonus material. Lessons
Database FilesLinks
Resources
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 this lesson, you will learn how to create custom list box columns on forms in Microsoft Access, allowing users to select which fields they want displayed using both check boxes and multi-select list boxes. We will cover techniques for dynamically loading field options and properties from tables, using the TableDefs and Fields collections, and exploring new topics like query defs. You will also see how to generate and position custom labels for each column, resize forms dynamically, and switch between tables on the form. This class is designed as the next step after Developer 36.TranscriptWelcome to Microsoft Access Developer Level 37 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's class, we're going to work with Custom List Box Columns. We're going to build a form that is based on any table you want. We'll start with the customer table. You will be able to specify in the customer table field properties which fields you want to see on this form: first name, last name, email, and so on. Once the user opens up the form, they can pick which fields they want to see and those will be displayed in a list box. There are lots of cool things you can do with this one. We'll learn some new stuff, including working with query defs, and things we have not done yet in the developer classes. This one is going to be a lot of fun. I had a lot of fun recording it. This class, of course, follows Access Developer 36. I strongly recommend you have taken all the previous classes. Especially, you might want to review Developer 15 and 16 where I cover multi-select list boxes and recordsets. These are very important lessons to have before taking this class. Do not skip levels. My lessons are designed to be taken one after the other in order, so each one might build on the topics from the previous classes. I'll be using Access 365. It's currently 2022, so it's roughly equivalent to Access 2021. The topics covered in today's class should work just fine going back to about 2007. If you have any questions about the material covered in today's class, just scroll down and post them right there in the form you see at the bottom of the page. If you have any questions about stuff that's covered in other classes or just general Access questions, you can post them in the Access forum. Be sure to visit my TechHelp page. This is my kind of sort of almost daily video podcast about Access where I answer questions sent in to me by the students. All the cool questions are answered here. Let's take a look at exactly what's covered in this class. In lesson one, we're going to build a form with custom list box columns. In other words, we'll have a list of customers in a list box and a list of fields on the right. Based on what the user selects in those check boxes, that will determine which fields are displayed in the list box. In lesson two, we're continuing to work with the custom list box columns form. We're going to replace those check boxes with a multi-select list box. We have done multi-select list boxes before in Developer 15. We're going to review them a little bit here and we're going to see how this is much easier to maintain in the long run. It also looks better and is a more elegant solution than a whole bunch of check boxes. In lesson three, we're going to start moving away from having all of the fields listed in our VB code. It's a lot harder to maintain and upgrade that. Instead, I want to store that information in the table itself, in the table's properties. We're going to learn how to use the TableDefs collection, the Fields collection, and the Field Properties collection. We're going to loop through all the fields in a table and then we're going to load the field list based on the properties we're going to set in the table. This will determine the fields that we want to have listed, their widths, whether they're selected when the form loads, and more. In lesson four, we're going to replace our query list with generic code. In other words, it's going to ignore the field list completely. As long as you've got that table property set up, it will work with any field in the table. Then we'll see how to resize the form so that if you want to make it wider or taller, it will resize the list of the widths accordingly, and move our little field list over to the right as well. In lesson five, we're going to make some custom labels to go across the top of each of the columns so we can see what the column has in it. We'll make a bunch of blank labels, and then using our code, we'll position them properly on the screen. Let's do some inches to twips conversion. I'll tell you what that is in just a few minutes. We'll set the width of the label and its other properties based on the data in our list box. We know how wide each column is, so that can tell us where to place each label. In lesson six, we're still working with this custom list box form. We're going to see how we can prevent those labels from going too far to the right and we'll deal with a quirk with the horizontal scroll bar that pops up. Then we're going to see how we can change the table that's in this form. We can pick the customer table or the order table and have the data in here change based on which table we pick. In lesson seven, we're going to wrap up by learning something new - query defs. We have not done query defs yet in any of our developer classes. I just checked. We're going to add queries to the form. Query defs are very similar to table defs, which we've done, but you have to handle them a little bit differently. Then we're going to deal with missing that ID field if the user does not put an ID field in their table or query, and we have to be able to process that differently and disable the double click event. QuizQ1. What is the main focus of Access Developer Level 37?A. Building a form with custom list box columns based on any table B. Designing advanced reports in Access C. Creating forms with subforms only D. Building queries exclusively using SQL Q2. Which previous developer lessons are highly recommended to review before taking this class? A. Developer 1 and 2 B. Developer 15 and 16 C. Developer 25 and 26 D. Developer 35 and 36 only Q3. What is the advantage of using a multi-select list box over multiple check boxes for column selection? A. It is more difficult to maintain B. It takes up more space on the form C. It is easier to maintain and more elegant D. It cannot be used for column selection Q4. In lesson three, where is the field selection information stored for easier maintenance? A. Only in VB code B. In a separate text file C. In the table's properties D. On a hidden form Q5. Which Access object collections are mentioned as important for dynamically loading field lists? A. Form Controls, Queries, Reports B. TableDefs, Fields, Field Properties C. Macros, Modules, Classes D. ListBoxes, CheckBoxes, Labels Q6. What is the purpose of resizing the form and column widths in the custom list box columns form? A. To add blank space B. To remove unused columns C. To ensure the display matches the selected fields and looks correct D. To create a print layout Q7. What programming concept will be introduced for creating column header labels? A. Looping through table records B. Inches to twips conversion for positioning C. Filter by form D. Auto-numbering columns Q8. How does lesson six expand the functionality of the custom list box form? A. By supporting image attachments B. By allowing the selection of different tables to display data from C. By importing data from Excel D. By exporting the fields to PDF Q9. What new concept is covered in lesson seven of this class? A. Using Data Macros B. Working with QueryDefs C. Securing Access databases D. Creating switchboards Q10. If a user omits the ID field from their table or query, what does the class show how to handle? A. Ignore all errors B. Add the ID field automatically C. Process the form differently and disable the double click event D. Prevent the form from opening Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-B; 9-B; 10-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 focuses on Developer Level 37, and I am your instructor, Richard Rost.This lesson is all about working with Custom List Box Columns in Microsoft Access. We will build a form that you can base on any table of your choice, starting with the customer table as our example. You will be able to specify, right in the field properties of the customer table, which fields should appear on this form—such as first name, last name, email, and others. When the user opens the form, they can select the fields they want to see, and those will be shown in a list box. This gives us quite a bit of flexibility and opens up many interesting possibilities. We will cover several new concepts in this class. One of the key topics is working with query defs, which we have not explored yet in the developer courses. If you are following along with my lesson series, I recommend that you have completed all previous levels, especially Developer 15 and 16. Those two classes include important discussions on multi-select list boxes and recordsets, which are foundational for what we will be doing here. My courses are designed to be completed in sequence, as each one often builds on knowledge from the previous levels. For this instruction, I am using Access 365, as of 2022, which lines up pretty closely with Access 2021. The subjects we cover today should be relevant for any version going back to Access 2007. If you run into questions while studying this class, you can scroll down on the page and submit them in the form provided there. If your question is about material from another class, or you just have a general Access question, I encourage you to use the Access forum. I also want to mention my TechHelp page, where I address Access-related questions from students almost every day. If there is something on your mind, check there—many great questions are answered. Now, let's go over what you will learn in this class. In lesson one, we will build a form equipped with custom list box columns. There will be a list of customers shown in a list box, with a list of fields on the side. Whatever fields the user selects using the check boxes will appear as columns in the list box. Lesson two will enhance this form by replacing those check boxes with a multi-select list box. We have previously discussed multi-select list boxes in Developer 15, but here we will review them and see how this approach is both easier to maintain and visually cleaner compared to having many separate check boxes. In lesson three, we will further refine the process by taking field information out of our code, making it much easier to manage. Instead, this data will be stored in table properties—specifically using the TableDefs, Fields, and Field Properties collections. We will loop through all the fields in the table and use the properties to control which fields appear in the list, their widths, and whether they are selected by default when the form loads. Lesson four will let us replace our query list with more generic code so the form can ignore the specific field list entirely. Provided you have configured the relevant table property, the form will work with any field from any table. We will also see how to resize the form dynamically, allowing the column widths to adjust automatically, and shifting the field list as needed. In lesson five, we'll focus on creating and positioning custom labels above each column. These labels will show the field names contained in each list box column. We will generate blank labels and then use code to position and size them according to the corresponding list box columns. This will include converting measurements from inches to twips and assigning the correct widths. Lesson six will address how to prevent those labels from overflowing to the right, and work through issues related to the horizontal scroll bar that can show up. Another feature we will explore is allowing users to change the table shown in the form—switching between the customer table, order table, and so on, with the displayed data updating accordingly. Finally, lesson seven will introduce you to query defs, a new concept for our developer classes. You will learn how to add queries to the form and work with them, contrasting them with the table defs you have seen before. Additionally, we will discuss how to handle situations where an ID field might be missing from a user's table or query, and how to process this while disabling actions such as the double click event when appropriate. For those interested in a step-by-step walkthrough, you can find the complete video tutorial covering everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListBuilding a form with custom list box columnsDisplaying selected fields in a list box Switching from check boxes to a multi-select list box Reviewing and implementing multi-select list boxes Storing field display information in table properties Using the TableDefs, Fields, and Field Properties collections Looping through table fields to load field lists Defining field properties including widths and selection status Replacing hardcoded field lists with generic code Adapting the form to work with any table Resizing the form and list box columns dynamically Positioning and sizing custom labels above list box columns Converting between inches and twips for label placement Handling label overflow and horizontal scroll bars Switching displayed data between different tables Introducing and working with query defs Handling query defs and differences from table defs Managing missing ID fields in tables or queries Disabling double click events when no ID field is present |
||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 37 lessons PermaLink How To Build Custom List Box Columns With Dynamic Fields and QueryDefs in Microsoft Access |
