Introduction

Welcome! Custom List Box With User Fields

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

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 Introduction
Get notifications when this page is updated

Intro

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.

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

Article

Welcome to this Microsoft Access tutorial where we will build a form with custom list box columns. You will learn how to create a flexible list box on your forms that displays fields based on the user's preferences. This means you can let users pick which columns they want to see from any table, such as customer information or orders, and have the list box on your form automatically update to show just those fields.

We will start by creating a form based on the customer table. Suppose you have a customer table with fields like FirstName, LastName, Email, and so on. Instead of always showing all of them, you want to give the user control to pick which fields should appear on the form. To do this, we will set up a form with a list box that shows customers, and next to it a way for the user to choose which fields are displayed.

First, we will set up the form so the list of fields is shown with checkboxes. As the user checks or unchecks a box, the columns in the list box update, showing just the fields they are interested in. For example, if the user wants to see FirstName and Email only, those are the only columns that will show up.

As we refine the form, we will replace the collection of checkboxes with a multi-select list box. This is a smoother and more scalable solution, especially as you add more fields in your table. The multi-select list box allows the user to select multiple fields at once with much less effort than a row of individual checkboxes, and it is easier to maintain in your database design.

We will also improve how the form finds out which fields are available. Instead of writing out every field name in your VBA code, which can quickly become unmanageable, we will learn how to look up fields dynamically from the table itself. You can store information about each field in the table's property settings, including things like the preferred column width or whether a field should be selected by default. We will use VBA and the TableDefs and Fields collections to loop through the fields in a table and load the appropriate fields into our form. Here is an example of how you might loop through the fields in a table:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("Customer")
For Each fld In tdf.Fields
' Check for a custom property
If fld.Properties("ShowOnForm") = True Then
' Add field to list
End If
Next fld

By setting custom properties on your table fields, such as ShowOnForm or ColumnWidth, you can control exactly which fields are made available to the user in your custom list box and how wide each column appears.

As we progress, we will make the code more generic so it works with any table in your database, not just the customer table. That way, you can create a single form that adapts dynamically to display different columns for customers, orders, products, or any table you choose. The form will be able to resize its list box and the widths of its columns to match the fields selected, and it will move any field selection controls over as necessary if the form becomes wider.

To make the list box easier to understand, we will generate column headers for each field. Since Access list boxes do not allow you to set custom headers, we will create a collection of label controls on the form. Using VBA, we will position these labels above each column, matching their widths to the column widths in the list box. For measurement, Access uses a unit called a twip, which is 1/1440 of an inch. If you have field widths in inches, convert them to twips in your code with this simple calculation:

Twips = Inches * 1440

That way, you can precisely place each label over the correct column. For example, if your first column is one inch wide, set the label's width to 1440 twips and its left position to zero. Then, make the next label left position equal to the previous width, and so on.

We will also handle quirks that come up, like making sure labels or controls do not overflow past the right edge of the form and dealing with the horizontal scrollbar that appears if the list box is wider than the form itself. All these adjustments will ensure the interface remains clean and easy to use, no matter which fields or how many are selected.

To make the form even more powerful, we will add the ability to switch the underlying table. The user can select the customer table, order table, or any other table in your database, and the list box will update to show the relevant records and fields for that table.

Finally, we will introduce a new concept called query defs. While table defs allow you to work with tables directly, query defs let you work with saved queries in your database. You can add queries to this form so your list box can display data from a query just as easily as from a table. Query defs are similar to table defs but require a slightly different approach when working with them in your VBA code.

A special case you might encounter is when the user does not include an ID field in their selection. If the form is set up to allow double-clicking a row to open the full record, but there is no ID field available, we will need to catch that case and prevent errors by disabling that event.

By applying these techniques, you will have a robust and flexible Access form where users can select any fields from any table or query, and only those fields will appear in a dynamically updating list box. You will create neat column headers, handle resizing, and even switch between tables or queries as needed. This advanced functionality will greatly enhance what you can do with forms in Access and give your users new options to work with data in ways that suit them best.