Introduction

Welcome! Multi-Select Lists & Debugging VBA

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

Welcome to Microsoft Access Developer Level 15. In this course we will focus on multi-select list boxes and debugging. We will review what has been covered about list boxes in previous classes, talk about moving active and inactive customers between two list boxes, passing controls as parameter values, using multi-select options, and key properties and methods like ListCount, ItemData, Selected, For Each loops, and DBExecute. We will also discuss debugging in Microsoft Access, including error handling, using the Immediate window, setting watches and breakpoints, and recommendations for prerequisites and where to ask questions.

Navigation

Prev: D15
Next: Lesson 01

Keywords

Access Developer, multi-select list box, list box vba, debugging vba, move values between list boxes, error handling vba, dbexecute, itemdata, selected property, listcount, for each loop, point of sale system, test taker database, watches breakpoints

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

Developer 15 is going to focus primarily on multi-select list boxes and debugging.

Now, we have spent a good amount of time covering list boxes in past classes.

In Beginner Level 8, we built static list boxes, which just have a single list of values in them.

In Expert Level 4, we built relational list boxes, where you can base the data in the box off of a table or query.

In Advanced Level 5, we learned how to move values from one box to another using macros.

In Developer 10, we learned how to use VBA and SQL to populate our list boxes. Then, in Developer 10, we used list boxes in our Test Taker database.

Today, we are going to beef that up a little bit and build a similar example to Advanced 5, but it is going to be on steroids.

We are going to see how to move active and inactive customers between two different list boxes.

This time, I am going to teach you how to pass a control as a parameter value.

We are going to learn multi-select list boxes where you can select multiple options from the box and then do something with it.

You can use code to select options in the box. We will learn about ListCount, ItemData, Selected. We will learn about For Each loops, and I will teach you about DBExecute.

And yes, I know I went backwards when I wanted to do the list box stuff first.

Lesson 2 is going to cover debugging.

We have done some debugging before with On Error Resume Next, but a lot of you have been asking for more lessons on debugging.

Here, I am going to teach you how to set up your own error handling, display error messages, use the Immediate window, set up watches and breakpoints, and lots more.

I am using Access 2019, the Office 365 edition. I should probably change that slide.

They recently renamed Office 365 to Microsoft 365, their subscription option. It is the same thing, different name.

I would recommend for prerequisites that you at least have those top five classes there. The Test Taker Developer 10 is necessary.

I do recommend you take all my classes in order, but if you are skipping around, you should know list boxes before you get to the multi-select stuff.

These four classes on top here would be handy, at least. And, of course, I recommend the class before this, Access Developer 14.

One of the major reasons I decided to go into multi-select list boxes is because we are going to use them a lot for developing our Easy Access POS, the point of sale system.

Even if you are not building the POS with me, there are lessons in Developer 14 that everyone can benefit from. I suggest you take Developer 14 before Developer 15.

If you have questions pertaining to the material covered in today's class, you can post them on the page for this class.

If you scroll down to the bottom of the page under the video, you will see all the other questions and comments that other users have posted.

You should see a form something like this. Yeah, I change it every time. It basically looks like this: type in a subject and your comments, hit submit, and it will go right on the course page.

I try to answer questions online as often as I can, and there is Alex and a bunch of other people who do a great job of helping me out.

That is where I recommend you submit any questions you have.

You can also hit the subscribe button and get notified anytime anyone else posts a question about this class, too. That is great for updates or if I post any addendums or extra files, that kind of stuff.

If you have a general question about Access or any other topic, you can go to the forums and post it there.

I try to keep stuff in the course pages relevant to that class. That is what the forums are for. There is Word, Excel, Outlook, all that stuff.

There is also my TechHelp page. If you need help right now with a project and you are stuck, and you do not want to wait for a forum reply, you can send it to my TechHelp page.

If you need consulting advice or any of that kind of stuff, here is my email address, but again, I prefer you use one of the previous methods.

So, sit back, relax, and click on the link below to start Lesson One.

Quiz

Q1. What is the primary focus of Access Developer Level 15?
A. Multi-select list boxes and debugging
B. Creating reports and forms
C. Database security
D. Custom ribbon design

Q2. In which class did you learn to build relational list boxes based on a table or query?
A. Beginner Level 8
B. Expert Level 4
C. Advanced Level 5
D. Developer 10

Q3. Which of the following is true about multi-select list boxes?
A. They allow you to select multiple options and perform actions with them
B. They only allow a single value selection
C. They can only be used with static values
D. They do not support VBA code interaction

Q4. Which VBA concepts will be covered in relation to list boxes in this course?
A. ListCount, ItemData, Selected, For Each loops, and DBExecute
B. Creating custom menus
C. Upsizing to SQL Server
D. User-level security

Q5. What is a new technique you will learn in this course regarding list box controls?
A. Passing a control as a parameter value
B. Sorting values alphabetically only
C. Exporting list box data to Excel
D. Using macro-only solutions

Q6. What will Lesson 2 of this course primarily address?
A. Debugging techniques in VBA
B. Building navigation forms
C. Formatting reports
D. Compacting and repairing databases

Q7. Why does the instructor recommend completing Developer 10 before taking this course?
A. It includes the essential Test Taker database project
B. It teaches forms and reports
C. It covers Access web apps
D. It focuses on SQL Server migration

Q8. What major upcoming project will frequently use multi-select list boxes?
A. Easy Access POS (point of sale system)
B. Membership mailing system
C. Inventory barcode generator
D. Scheduling calendar

Q9. What is the preferred method for posting questions about material covered in this class?
A. Posting on the course page under the video
B. Emailing Richard directly
C. Commenting on YouTube
D. Using the TechHelp page exclusively

Q10. What tool can you use if you need immediate help with your Access project?
A. TechHelp page
B. Course comment section
C. Forums only
D. Wikipedia

Answers: 1-A; 2-B; 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

Welcome to Microsoft Access Developer Level 15 from AccessLearningZone.com. I am your instructor, Richard Rost.

In this class, our main focus is on working with multi-select list boxes and learning more advanced debugging techniques.

Throughout the previous courses, we have covered list boxes in several different ways. In Beginner Level 8, we set up static list boxes that simply display a set list of values. Then, moving up to Expert Level 4, we worked on relational list boxes, which pull data dynamically from tables or queries. In Advanced Level 5, I showed you how to transfer items between list boxes using macros. Later on, in Developer 10, we used VBA and SQL to populate list boxes, which we then used in our Test Taker database.

Today, we are going to take those skills further. We will build a solution similar to what we created in Advanced Level 5, but this time it will be much more robust. Specifically, I am going to show you how to move active and inactive customers between two list boxes.

One of the new skills you will pick up is passing a control as a parameter value. You will also learn how to work with multi-select list boxes so users can pick multiple items at once and perform batch operations. I will teach you how to work with properties such as ListCount, ItemData, and Selected. We will go over using For Each loops in this context, and I will explain the DBExecute method.

This time, I decided to focus first on these enhanced list box features before addressing debugging.

In Lesson 2, we turn our attention to debugging. We have covered On Error Resume Next before, but many of you have requested a deeper dive into error handling. In this lesson, I will walk you through setting up your own error handling routines, displaying error messages, using the Immediate window, and working with watches and breakpoints, among other techniques.

I am currently demonstrating all of this in Access 2019 using the Office 365 edition, though Microsoft has since rebranded Office 365 as Microsoft 365. Only the name has changed - the product is essentially the same.

I recommend you complete the first five prerequisite classes, especially the Test Taker Developer 10 lesson, before starting with this course. Ideally, you should work through my classes in order, but at the very least, make sure you are comfortable with list boxes before moving into the multi-select features in Developer 15. Of course, I also suggest completing Access Developer 14 just before this one.

Learning about multi-select list boxes is very important for future projects, especially if you are planning to build the Easy Access POS (Point of Sale) system with me. Even for students not interested in the POS system, there are valuable lessons in Developer 14 that I believe everyone can benefit from before moving on to this level.

If you have questions as you go through this material, the best place to ask is on the course page. At the bottom of the page, beneath the video lesson, you will see where you can submit your questions or comments. Your question will appear on the page, and I or another member of our community, like Alex and others who help out, will try to answer as quickly as possible.

There is also an option to subscribe for notifications when new questions or updates are posted for the course. If you have more general questions unrelated to this specific lesson, you can use the main forums, which cover Access, Word, Excel, Outlook, and other topics.

For urgent project help, you may use my TechHelp page to request assistance, and for consulting or other advice, you can reach me by email. However, I prefer that you use the appropriate support channels first.

I encourage you to relax and get ready to learn. When you are ready, proceed to Lesson One.

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

Building multi-select list boxes in Access
Moving items between two list boxes
Passing controls as parameter values
Selecting multiple options in list boxes
Using code to select options in list boxes
ListCount, ItemData, and Selected properties
Using For Each loops with list boxes
Using DBExecute in VBA
Debugging Access VBA code
Setting up custom error handling
Displaying error messages in VBA
Using the Immediate window for debugging
Setting up watches and breakpoints in VBA
List box applications in point of sale systems

Article

Welcome to this comprehensive Microsoft Access Developer Level 15 tutorial. In this lesson, we are going to focus on working with multi-select list boxes and improving your debugging skills in Microsoft Access. If you have already taken my previous courses or worked with list boxes in Access, you will find this lesson a helpful step up.

To start, let us quickly review where list boxes fit in your Access development journey. In earlier lessons, you may have built static list boxes that contain a single, unchanging list of values. Later, you might have created relational list boxes, where the contents come from a table or query and can update dynamically. You might also have learned how to move items from one list box to another, often using macros, and you may have utilized VBA and SQL to populate list boxes dynamically.

In this lesson, we will build on those concepts, focusing on multi-select list boxes. Multi-select list boxes allow users to select more than one item at a time and then perform actions with the selected items. As a practical example, imagine you need an interface where you can move active and inactive customers between two different list boxes. Rather than doing this one item at a time, you can select multiple customers and transfer them all at once.

To create a multi-select list box, begin by adding a list box control to your form. In the properties window, set the "Multi Select" property to either "Simple" or "Extended." "Simple" means you can select multiple items by clicking them, while "Extended" lets you use Shift or Ctrl to make your selection, similar to Windows Explorer.

Within VBA, you can interact with the selected items using properties such as ListCount, ItemData, and Selected. Here is how you might process all selected items in a list box named lstCustomers:

Dim i As Integer
For i = 0 To lstCustomers.ListCount - 1
If lstCustomers.Selected(i) Then
MsgBox lstCustomers.ItemData(i)
End If
Next i

This code loops through each item in the list box and checks if it is selected. If it is, you can perform any desired action, such as adding the customer's ID to another list or using it to build an SQL query.

You might also want to move selected items from one list box to another. To do this efficiently, you should manage the underlying data source of the list, such as a table or query, and update it based on the selection. For example, you could have a Customers table with an Active field. If users select customers to deactivate, you can run an update query in VBA like this:

Dim db As Database
Set db = CurrentDb()
For i = 0 To lstCustomers.ListCount - 1
If lstCustomers.Selected(i) Then
db.Execute "UPDATE Customers SET Active = False WHERE CustomerID = " & lstCustomers.ItemData(i) & ";"
End If
Next i

After running the query, requery the list boxes to refresh them and show the correct set of active and inactive customers. This process ensures your list boxes always display up-to-date information.

Another useful tip is that you can pass a control as a parameter to a function or subroutine in VBA. This allows you to write generic procedures that can work with any list box control, making your code more modular and reusable.

Debugging is an essential part of Access development, especially as your projects become more complex. You might be familiar with simple error handling, such as using On Error Resume Next, but for more robust applications, it is a good idea to implement more advanced error handling.

One way to do this is to write your own error handler. For example:

On Error GoTo ErrorHandler
' your code here
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description

This approach displays a message box with the error number and description if something goes wrong. You can expand on this to log errors to a table, display custom messages, or take other actions as needed.

While working in the VBA editor, the Immediate window is a powerful tool for debugging. You can use Debug.Print to output variable values or check the results of expressions while your code is running. Setting breakpoints allows you to pause execution at specific lines, and you can use watches to track the values of variables as your program runs.

It is also important to make sure you have the necessary prerequisites before tackling multi-select list boxes and debugging in depth. You should be comfortable with static and dynamic list boxes, be familiar with forms and controls in Access, and know some VBA basics.

Multi-select list boxes are particularly valuable when building more advanced systems, such as a point of sale solution. Even if you are not creating a point of sale database, mastering these techniques will help you with any Access application that requires working with multiple records at once or processing selections in bulk.

If you run into questions as you work through these concepts, the best place to ask is in the course page's comments and questions section below the lesson. There, you will also find answers to previous questions and a helpful community, including instructors and other students who can provide feedback. If you have general questions about Access or other Microsoft Office applications, there are dedicated forums for each product.

For quick help or more in-depth consulting, options are available, but the comments section and forums are the best places to start for course-related topics.

Now that you have an overview, you are ready to get hands-on with multi-select list boxes and strengthen your debugging skills. Open your project, try out the examples, and see how these features can streamline your workflow in Microsoft Access. With practice, you will find these tools essential for building robust, user-friendly applications.