Welcome

Welcome! Option Explicit and Multi Select Filters

Welcome to Access Developer 62. We will learn how to scan VBA code in forms, reports, and standard modules to find and add missing Option Explicit statements, build multi-select filters with multi-valued fields and AND/OR logic, and standardize Option Compare and Option Explicit declarations while safely modifying module code.

Navigation

Prev: D62
Next: Lesson 1

Keywords

Access Developer, VBA Option Explicit, multi-valued fields, multi-select combo box filters, dynamic SQL criteria, AND OR filter logic, VBA code modules, Option Compare, form and report modules, VBIDE CodeModule, VBA module declarations, backward loop dele

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 62, brought to you by Access Learning Zone. I am your instructor, Richard Rost.

Today, we're going to work with a couple of very different but extremely useful developer-level techniques.

We'll start by learning how to scan the VBA throughout your database, including your forms, your reports, and your standard modules, and we'll automatically find and fix missing Option Explicit statements.

Then we're going to do something that I normally tell you not to do. We're going to find a good use for multi-valued fields.

I know. I know.

We'll use them to build compact, multi-select filter combo boxes where users can select multiple values and combine different criteria with AND and OR logic.

Finally, we'll return to our VBA module stuff and improve the declarations code from lesson one by standardizing Option Compare and Option Explicit while preserving the existing comparison setting.

Along the way, you're going to work with module objects, variant arrays, dynamic filter settings, and learn why it's sometimes better to loop backwards than forwards.

This class follows Developer Level 61. As always, I recommend you don't skip levels. Some of the techniques we're using here build on concepts we've covered previously, particularly looping through objects and working programmatically with VBA modules.

I'll be using Microsoft 365. It's currently October of 2026. But this class is valid for all versions of Access going back to, I'd probably say, Access 2007.

If you've got any questions about the material covered in this class, just scroll down to the bottom of the page that you're watching it on and post your questions down there. If you've got other questions that aren't pertaining to today's class, post them in the forum.

All right, let's see what we're going to learn today.

In lesson one, we're going to scan the VBA modules throughout our database and look for missing Option Explicit statements. We'll work with the modules behind forms and reports, as well as standard code modules. We'll learn how to open and close those modules programmatically and automatically insert Option Explicit wherever it's missing. We'll also make sure the Visual Basic Editor is returned to the state it was in before our code ran.

And if you're wondering why this is important, if you've done programming in VBA for any period of time, you know that if you don't have Option Explicit in all of your modules, the slightest spelling error will just allow it to compile, no problem, but then it won't tell you that there's anything wrong until your code just doesn't work.

Option Explicit goes in every one of your modules.

In lesson two, we're going to build multi-select combo box filters using multi-valued fields. Yes, I know, I know, I normally tell you not to use multi-valued fields because they're horrible, but this time I've actually found a really good use for them.

We'll select multiple values from different fields, build dynamic SQL criteria, combine those filters using AND and OR logic, and then apply the results to our customer list. We'll also see how Access gives us those selected values as variant arrays and use VBA to turn them into working filters.

Then, in lesson three, based on popular demand, we're going to go back into the module declarations from lesson one and make them better. We'll standardize Option Compare and Option Explicit so that they're in the proper order while preserving whether the module uses database, binary, or text comparison. And we'll learn how to prevent a very significant gotcha when you're looping through lines deleting them in your code.

So, by the end of this level, you're going to have some powerful new tools for inspecting and maintaining the VBA in your Access database, a creative technique for building compact multi-select filtering interfaces, and another useful technique for safely modifying code modules programmatically.

Now, as always, I recommend you watch each video once through completely and then watch it a second time and follow along with the examples.

All right, are you ready? Sit back, relax, grab your coffee, and let's get ready to start lesson one.

Intro

Welcome to <B>Access Developer 62</B>. We will learn how to scan VBA code in forms, reports, and standard modules to find and add missing Option Explicit statements, build multi-select filters with multi-valued fields and AND/OR logic, and standardize Option Compare and Option Explicit declarations while safely modifying module code.

Summary

Today's video from Access Learning Zone is Developer Level 62. In this class, I will cover several advanced Microsoft Access and VBA techniques that are useful for maintaining databases and building flexible user interfaces.

This class follows Developer Level 61, and I strongly recommend taking the previous levels in order. Several of the techniques in this lesson build on concepts we have already covered, especially looping through Access objects and working with VBA modules programmatically.

I am using Microsoft 365 as of October 2026, but the material in this class should work with Access 2007 and later.

In the first lesson, I will show you how to inspect the VBA code throughout an Access database and locate modules that are missing an Option Explicit statement. This includes the code modules behind forms and reports, as well as standard VBA modules.

Option Explicit is an important statement that should be included in every VBA module. Without it, VBA will allow misspelled variable names to compile without warning. A small typing mistake can create a new unintended variable, and your code may fail or produce incorrect results without giving you a clear error message.

I will demonstrate how to loop through the forms, reports, and standard modules in the database, open their VBA modules programmatically, inspect the declarations section, and add Option Explicit wherever it is missing. I will also make sure that the Visual Basic Editor is returned to the same state it was in before the process began.

In the second lesson, I will demonstrate a practical use for multi-valued fields. I normally recommend avoiding multi-valued fields in Access because they can create complications in table design, queries, and VBA. However, they can be useful in a specific situation: creating compact multi-select filter controls.

I will use multi-valued fields to allow users to select multiple values from a combo box or similar control. For example, a user may want to filter a customer list by several states, several customer types, or other multiple-choice criteria without needing a large list box or a complicated interface.

We will create dynamic SQL criteria based on the selected values. I will explain how Access returns those selections as variant arrays and how VBA can process those arrays to build a working filter. We will also combine criteria from different fields using AND logic and OR logic, allowing users to create flexible searches based on several selections.

In the third lesson, I will return to the VBA module declarations work from lesson one and improve it further. Rather than only adding Option Explicit, we will standardize both Option Compare and Option Explicit.

Modules can use different Option Compare settings, such as Database, Binary, or Text. We need to preserve the existing comparison setting because changing it could alter the way string comparisons behave in that module. I will show you how to identify the existing setting, retain it, and place the declaration statements in the proper order.

I will also cover an important programming issue that occurs when deleting lines from a VBA module while looping through its contents. When removing lines during a loop, it is often safer to loop backward rather than forward. Looping backward prevents line numbers from shifting in a way that causes code to skip lines or produce unexpected results.

By the end of this level, you will have several useful development tools. You will be able to scan and maintain VBA code throughout your Access database, ensure that important module declarations are present and properly organized, and build compact multi-select filtering interfaces using multi-valued fields in a controlled and practical way.

I recommend watching each lesson once from beginning to end, then watching it again while following along in your own database.

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

Course overview and recommended prerequisites

Primary Topics

scanning VBA modules for missing Option Explicit, opening form/report/standard modules programmatically, inserting Option Explicit automatically, restoring Visual Basic Editor state, multi-valued fields for multi-select filter controls, dynamic SQL criter

Secondary Topics

module objects, variant arrays, looping through Access objects, backward looping when deleting code lines, preserving existing comparison settings