Multi-Select Listboxes, Colors, Recordsets

Welcome to Access Developer 16. In this course you will learn how to use multi-select list boxes and explore whether color and formatting can be applied to them in Microsoft Access. We will discuss workarounds for visual enhancements, review solutions for common bugs and glitches, and begin working with recordsets to access table data directly from Visual Basic code. This series is intended for students familiar with previous Developer lessons and covers techniques you can apply in Access versions from 2007 and newer.

Lessons

•  00. Introduction (1:01)
•  01. Homework Review (13:16)
•  02. Bug Fixes (18:05)
•  03. Listbox Color & Format (34:31)
•  04. Recordsets 1 (19:37)
•  05. Recordsets 2 (32:00)
•  06. Review & What's Next (5:30)
• Total Runtime: 2 hours 4 minutes

Database Files

• Database Before Lesson 1
• Database After Lesson 1
• Database After Lesson 2
• Database After Lesson 3
• Database After Lesson 4
• Database After Lesson 5
• Database After Lesson 6

Resources

• Full Course Description Page
• Topic Outline
•  PDF Handbook
• Video Lessons Download ZIP File (229 MB)
• Certificate of Completion
• Post-Class Survey

Lesson Summary

Welcome! Multi-Select List Box Tips - 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.

Lesson 1: Select/Deselect All Listbox - In Lesson 1, we will review the homework from Access Developer 15, focusing on creating "Select All" and "Deselect All" links for two listboxes using a single subroutine to handle all four cases. I will show you how to pass controls and optional parameters, count the number of records moved, and display messages like "one customer marked active." We will also discuss good user interface practices, such as ensuring only one listbox has selected items at a time and deselecting all items after moving records.

Lesson 2: Fix Multi-Select List Box Bugs - In Lesson 2, we address a bug fix related to a visual glitch in Microsoft Access list boxes. When you select multiple records and move them between list boxes, Access sometimes retains old selections incorrectly, causing unexpected behavior. I will walk through the troubleshooting steps that led to the solution, including adding diagnostic buttons and adjusting code to properly deselect items as they are moved. We will also discuss and resolve a visual glitch where selections appear incorrectly highlighted, using a setfocus technique to correct the display. Finally, we will briefly mention upcoming lessons about unbound list boxes.

Lesson 3: Colors, Conditional Formatting List Boxes - In Lesson 3, we will explore whether it is possible to add color and formatting to a list box in Microsoft Access. Although list boxes do not support traditional formatting, I will show you how to use a continuous subform to mimic a list box with colored and formatted rows using conditional formatting. We will walk through creating a sales reps example, setting up conditional formatting based on states, linking the subform to the main form, and adding features like changing the mouse pointer and updating selections visually. We will also discuss the limitations and some possible enhancements.

Lesson 4: Recordsets & Data Handling - In Lesson 4, we will learn the basics of working with recordsets in Access, which provide a more efficient and flexible way to interact with table data compared to using SQL statements in loops. I will explain what recordsets are, why they are valuable, and walk through setting up a simple recordset using DAO, including the key steps of declaring, opening, closing, and destroying database and recordset objects. We will also briefly discuss object library references for older Access versions and touch on the differences between DAO and ADO technologies.

Lesson 5: Loop, Edit, and Filter Lists - In Lesson 5, we continue working with recordsets by learning how to display individual fields from a table, rather than just the recordset or database name. I will show you how to loop through all the records using a While loop, apply criteria to filter records (such as by state), and handle situations when no records are found. We will also set up an unbound list box (value list) on a form, manually add data to it, and then populate it directly from a recordset. Finally, we will walk through deleting items from the list box.

Lesson 6: Course Review & Next Steps - In this course we learned a variety of topics in Access Developer 16, including working with multi-select list boxes, unbound list boxes for displaying recently accessed customers, and recordsets. We discussed plans for the upcoming Developer 17 course, which will include techniques for removing items from a multi-select list box and possibly cover arrays. We also talked about supplemental resources like the Access forum, the TechHelp video podcast, and ways to ask questions or provide feedback through course surveys.

Navigation

• Prev: D15
• Next: D17

Keywords