Introduction

Welcome! Multi-Select List Box Tips

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

Welcome to Microsoft Access Developer Level 16. In this course we will continue working with multi-select list boxes, discuss whether formatting and color can be used in list boxes, and explore a useful workaround if that is not possible. We will also begin working with record sets to see how to access data and tables directly from Visual Basic code. This course is designed for students who have already completed the Beginner, Expert, Advanced, and Developer lessons through Level 16, and is demonstrated using Microsoft Access 2019, though it should work for versions 2007 and newer.

Navigation

Prev: D16
Next: Lesson 01

Keywords

Access Developer, multi-select list box, list box formatting, list box color, list box workaround, recordsets, vba recordsets, access tables via vba, access listbox formatting, vba listbox color, access vba code, access 2019, microsoft 365

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

In Developer 16, we are continuing our work with multi-select list boxes. We are going to learn whether or not it is possible to have formatting and color in list boxes. If it is not, we are going to learn a really cool workaround.

Then we are going to begin working with record sets to learn how to access the data and tables directly from our VB code.

I will be using Microsoft Access 2019 with my Microsoft 365 subscription in this class. These lessons should work for anybody using 2007 and up.

Of course, this is a Developer Level lesson. I strongly recommend that you have taken my Beginner, Expert, Advanced, and Developer lessons levels 1 through 16.

Now it is time to get our learning caps on and get started with lesson 1.

Quiz

Q1. What is the main topic covered in Developer 16 of the Microsoft Access course?
A. Multi-select list boxes and their formatting options
B. How to create reports in Access
C. The basics of SQL queries in Access
D. Managing security in Access databases

Q2. What is discussed regarding formatting and color in list boxes?
A. It is fully supported in Access and easy to implement
B. It is not possible, but a workaround will be shown
C. It is only available in Access 2021 and newer
D. It is managed entirely through table properties

Q3. What programming method will be introduced to work directly with tables and data?
A. Macros
B. Queries
C. Recordsets in VB code
D. Relationships

Q4. Which version of Microsoft Access does the instructor use for this class?
A. Microsoft Access 2003
B. Microsoft Access 2016 Standalone
C. Microsoft Access 2019 with Microsoft 365 subscription
D. Microsoft Access 2010 Starter Edition

Q5. For which audience is this Developer Level lesson recommended?
A. Absolute beginners with no prior Access experience
B. Users who have only completed beginner lessons
C. Those who have completed Beginner, Expert, Advanced, and Developer levels 1-16
D. Anyone interested in learning about databases

Q6. What is suggested before starting with Developer 16?
A. Take a break and come back later
B. Practice SQL joins
C. Complete all previous lessons up to Developer Level 16
D. Buy a new computer

Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-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.

Summary

Today's video from Access Learning Zone focuses on Developer Level 16, where we continue exploring multi-select list boxes in Microsoft Access. I will address whether it is possible to apply formatting and color to list box items, and if it turns out that it can't be done natively, I'll show you an effective workaround to achieve a similar result.

Next, we'll start working with record sets. This part of the lesson will teach you how to interact directly with data and tables using Visual Basic for Applications (VBA) code in Access.

For this session, I'm using Microsoft Access 2019 with my Microsoft 365 subscription, but the material should be applicable if you're using Access 2007 or any newer version.

I want to remind you that this is a Developer Level class, so you should already be familiar with Access at the Beginner, Expert, and Advanced levels, as well as Developer lessons 1 through 15. This background will help you get the most out of these lessons.

Let's get ready to learn. 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 List

Working with multi-select list boxes
Exploring formatting and color options in list boxes
Creating a workaround for list box formatting limitations
Accessing data and tables directly from VBA code using recordsets

Article

In this tutorial, we are going to focus on enhancing your Microsoft Access applications by working with multi-select list boxes and exploring advanced techniques to control formatting and work directly with your data using VBA recordsets.

When you use a list box in Access, especially a multi-select list box, it allows users to select multiple items at once. This is great for situations where you want users to choose several categories, tags, or any collection of options in your forms. However, Access does not allow for rich formatting or the use of colors within list box items by default. All the items in a standard list box must display with the same font and color. This limitation can sometimes make it challenging to highlight certain choices or visually separate groups of data.

If you want to give your users some visual cues inside a list box, you might wonder if there is a way to add colors or different text styles directly to the items displayed in the list. Unfortunately, native Access list boxes do not support changing fonts, colors, or styles for individual rows. Every item shares the same format defined by the properties of the list box control as a whole.

While native formatting is not possible, there is a powerful workaround. Instead of using a list box, you can simulate its behavior using a continuous form or a subform set in continuous view. In this setup, each record displayed in the subform represents a choice the user can select, and you can fully control formatting for each record using conditional formatting or VBA code. For example, you could set the background color of records that meet certain criteria, bold specific text, or even add small icons. This approach gives you far more control over the appearance of a selectable list for your users.

If you still want to work with actual list boxes, you can sometimes convey additional information using symbols, emojis, or special character codes within the text of each item. For example, you might prefix urgent choices with an asterisk, or use Unicode characters to visually differentiate entries. However, this is just a display trick and does not truly adjust the color or style of the list box items.

Beyond formatting, an important developer skill is learning how to process user selections from a multi-select list box in VBA. When a user picks one or more items, you often need to capture those choices and perform actions with the selected data, such as filtering records, generating reports, or updating other tables.

To access the selected items in a multi-select list box using VBA, you need to loop through the ItemsSelected collection of the list box control. Here is how you can do this in code:

Dim varItem As Variant
Dim strSelected As String

strSelected = ""

For Each varItem In Me.MyListbox.ItemsSelected
strSelected = strSelected & Me.MyListbox.ItemData(varItem) & ","
Next varItem

If Len(strSelected) > 0 Then
strSelected = Left(strSelected, Len(strSelected) - 1)
End If

MsgBox "You selected: " & strSelected

In this example, replace MyListbox with the name of your list box control. This script collects the values of all selected items, joins them into a comma-separated string, and then displays them in a message box. You can adapt this code to update a field, filter records, or perform more complex actions as your application requires.

Taking this a step further, another advanced technique is working directly with recordsets in VBA. Recordsets allow you to read from and write to tables and queries in your database using code, providing much greater flexibility and power. For example, you might want to create a custom process that iterates through user selections in your multi-select list box and updates values in your tables accordingly.

To work with a DAO recordset, your code might look like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF
' Process each record here
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
Set db = Nothing

This block of code opens the table named MyTable, loops through each record, and lets you run any process you need inside the loop. You can combine this with your list box selection process to, for example, update only those records that match the user's selections.

To sum up, while you cannot apply different formatting or colors to individual list box items in Access, you can simulate the effect with a continuous form or workaround tricks. More importantly, knowing how to access and handle multi-select list box items and work directly with database recordsets in VBA will greatly enhance the interactivity and power of your Access applications. With these skills, you can build more responsive, dynamic, and user-friendly database solutions.