Welcome

Welcome! Custom List Box With User Fields

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Developer Level 37. In this course we will cover building a form with custom list box columns that allow users to choose which fields to display from any table, starting with the customer table. We will discuss storing field selections in table properties rather than in VBA code, work with collections such as TableDefs and Fields, and use query defs for the first time in this series. Additional topics include switching between tables, resizing forms, dynamically creating column labels, handling label placement, and ensuring the form responds to missing ID fields in tables or queries.

Navigation

Prev: D37
Next: Lesson 01

Keywords

Access Developer, custom list box columns, list box multi-select, form field selection, TableDefs collection, Fields collection, Field Properties, query defs, dynamic column labels, resize form columns, multi-select list box, inches to twips conversion

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 37, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to work with Custom List Box Columns. We are going to build a form where it is going to be based on any table you want. We will 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, such as first name, last name, email, and so on.

The user, once they open up the form, 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. We will learn some new concepts, including working with query defs, which we have not done yet in the developer classes.

This one is going to be a lot of fun. I know I had a lot of fun recording it.

This class, of course, follows Access Developer 36. I strongly recommend that 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 another in order, so each one might build on the topics from the previous classes.

I will be using Access 365. It is currently 2022, so it is 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, scroll down and post them right in the form you see at the bottom of the page. If you have any questions about material 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 you asked here.

Let's take a look at exactly what is covered in this class.

In lesson one, we are going to build a form with custom list box columns. In other words, we will 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 checkboxes, that will determine which fields are displayed in the list box.

In lesson two, we continue to work with the custom list box columns form. We are going to replace those checkboxes with a multi-select list box. We have done multi-select list boxes before in Developer 15. We are going to review them a little bit here and we are 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 checkboxes.

In lesson three, we are going to start moving away from having all of the fields listed in our VB code. It is 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 are going to learn how to use the TableDefs collection, the Fields collection, and the Field Properties collection. We will loop through all the fields in a table and then load the field list based on the properties we set in the table, which will determine the fields that we want to have listed, their widths, whether they are selected when the form loads, and more.

In lesson four, we are going to replace our query list with generic code. It will ignore the field list completely, and as long as you have that table property set up, it will work with any field in the table.

Then, we will see how to resize the form so that, if you want to make it wider or taller, it will resize the list and the widths accordingly. It will also move our field list over to the right as well.

In lesson five, we are going to make some custom labels to go across the top of each of the columns so we can see what the column contains. We will make a bunch of blank labels and then, using our code, position them properly on the screen. Lets do some inches to twips conversion (I will explain what that is in a few minutes). We will 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 you can tell us where to place each label.

In lesson six, we continue working with this custom list box form. We will see how we can prevent those labels from going too far to the right and deal with a quirk with the horizontal scrollbar that pops up. Then, we will see how we can change the table that is in this form, so 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 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 are going to add queries to the form. Query defs are very similar to table defs, which we have done, but you have to handle them a little bit differently.

Then, we are going to deal with missing the ID field. If the user does not put an ID field in their table or query, then we have to be able to process that differently and disable the double-click event.

Intro

Quiz

Q1. What is the main feature being built in this class?
A. A form that allows users to customize which columns appear in a list box
B. A query that automatically updates data
C. A navigation menu for all forms in the database
D. A report generator for printing invoices

Q2. In lesson one, how does the user choose which customer fields to view in the list box?
A. By selecting checkboxes for each field
B. By typing field names into a text box
C. By modifying the SQL query manually
D. By importing fields from Excel

Q3. What is the advantage of using a multi-select list box over checkboxes for field selection in lesson two?
A. It is easier to maintain and looks better
B. It requires less VBA code
C. It works with reports only
D. It prevents users from selecting invalid fields

Q4. Why are the fields to be displayed eventually stored in the table properties instead of hard-coded in VBA?
A. To make it easier to maintain and upgrade
B. To improve security
C. To enable integration with Excel
D. To avoid using Access macros

Q5. Which collections will you learn to use to control field display properties?
A. TableDefs, Fields, Field Properties
B. Forms, Reports, Queries
C. Macros, Modules, Relationships
D. Users, Groups, Permissions

Q6. What happens when you change the table selected in the custom list box form?
A. The fields and data shown update based on the new table
B. The form closes and reopens
C. The database is compacted
D. The previously selected fields remain unchanged

Q7. What is the purpose of resizing the form in lesson four?
A. To adjust the list and field display widths accordingly
B. To minimize the form on startup
C. To maximize database security
D. To delete unused records automatically

Q8. What are query defs and why are they important in this class?
A. They allow dynamic query generation in forms
B. They are used to secure record-level access
C. They enable table-level validation rules
D. They import data from external sources

Q9. When labels for the column headers are created, how are they positioned and sized?
A. Their width and position are set programmatically based on the column data
B. They are set manually on the form design
C. They are determined by the font size only
D. They are fixed and cannot be adjusted

Q10. What problem is addressed if the ID field is not included in the table or query selected?
A. The form must process this differently and disable certain events
B. The form will automatically add the ID field
C. The form prevents access to any data
D. The ID field is ignored and no changes are needed

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 is all about working with custom list box columns in Microsoft Access. I'm Richard Rost, and in this class I'm going to show you how to design a form that can work with any table you choose. We'll use the customer table to start, but you'll be able to select other tables as well. You'll learn how to specify in the table's field properties which pieces of information you want to display in the form, such as first name, last name, and email, among others.

Once the user opens the form, they will be able to pick which fields they'd like to view, and those will show up in a list box. There's a lot of flexibility and power in this approach, and along the way, I'll introduce you to some new techniques, such as working with query definitions (query defs), which we haven't covered yet in any of the earlier developer classes.

This class fits right after Developer 36. If you haven't worked through the previous lessons, I highly suggest you do, especially Developer 15 and 16. Those classes cover the fundamentals of multi-select list boxes and recordsets, which are crucial for understanding today's lessons. My classes are sequential, each building on the last, so please follow them in order.

For this class, I'm using Access 365, but the material applies to versions as far back as Access 2007. If you have questions about what we discuss today, you can post them at the bottom of the page on my website. For general Access inquiries or questions from other lessons, use the Access forum. I also encourage you to check out my TechHelp page, where I answer various questions sent in by students, often in a podcast format.

Now, let's break down what we're covering in this course.

In the first lesson, we'll create a form with custom list box columns. We'll have a list of customers in a list box and a selectable list of fields on the right. As the user checks which fields they want to see, those will appear in the list box, giving them control over the displayed columns.

In the second lesson, we'll improve this by swapping the checkboxes for a multi-select list box, revisiting techniques from Developer 15. This approach is tidier and much easier to maintain, plus it offers a better user experience overall.

Moving into the third lesson, we'll eliminate the need to hard-code the list of fields in VB. Instead, that data will be stored in the table itself, within the field properties. You'll learn to use the TableDefs collection, the Fields collection, and the Field Properties collection. We'll loop through all the fields in a table and decide which to include, their column widths, default selected status, and more, based on the properties you set in the table.

Lesson four makes the process even more generic, moving away from static field lists entirely. As long as the required table property is defined, the code will dynamically handle any field in your table. We'll also add resizing features so that if you adjust the form's size, the lists and columns will resize and reposition themselves as needed.

In lesson five, we'll add labels for each column at the top. We'll generate these labels and position them using calculated widths and positions, converting between inches and twips (I'll explain that concept as we go). The code will control the label size and placement according to the field layout.

The sixth lesson focuses on fine-tuning. We'll make sure the column headers do not run off the form when there are too many fields, and address quirks like the appearance of unwanted horizontal scrollbars. We'll also make the form versatile so that you can switch between different tables (like customer or order tables) and the data will update automatically.

Finally, in lesson seven, we'll introduce something we have not yet covered: query defs. These work similarly to table defs but have some important differences. We'll add query support to our form, and then handle the scenario where a table or query might be missing an ID field. If that's the case, we need to treat things differently, including disabling certain features like the double-click event.

If you'd like to see the complete video tutorial with detailed, step-by-step instructions on everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends.

Topic List

Building a form with custom list box columns
Displaying selectable fields in a list box
Switching from checkboxes to multi-select list box
Reviewing and implementing multi-select list boxes
Storing field display information in table properties
Using TableDefs, Fields, and Field Properties collections
Dynamically building field lists from table properties
Making form column selections work generically with any table
Resizing the form and moving field lists dynamically
Creating and positioning custom column header labels
Converting inches to twips for control placement
Setting column and label widths programmatically
Preventing header labels from overflowing form width
Handling horizontal scroll bar quirks in list box
Switching data source tables dynamically in the form
Introducing and working with QueryDefs
Differentiating QueryDefs from TableDefs
Handling missing ID fields in queries or tables
Disabling double-click events when ID field is absent

Primary Topics

custom list box columns, dynamic field selection, multi-select list boxes, TableDefs, Fields collection, Field Properties, resizing forms, QueryDefs

Secondary Topics

custom column labels, widths and positioning, handling missing ID fields