|
||||||
WelcomeWelcome! Find Orphaned VBA EventsWelcome to Access Developer 61. We will learn how to build an orphan VBA code detector that identifies event procedures left behind after controls are deleted. We will cover inspecting form and report modules, locating procedures with ProcOfLine, ProcStartLine, and ProcCountLines, checking the Controls collection, using a data-driven event table with a Recordset, and creating a reusable utility that scans individual objects or the entire database. NavigationKeywordsAccess Developer, orphan VBA code detector, Access VBA orphaned event procedures, VBA Module object, ProcOfLine, ProcStartLine, ProcCountLines, Access Controls collection, CurrentProject collections, form event procedures, report event procedures, VBA cod
More InformationTranscriptWelcome to Microsoft Access Developer Level 61, brought to you by Access Learning Zone. I'm your instructor, Richard Rost. Today, we're going to build an orphan VBA code detector. What does that mean? Well, if you've worked on a database for years, like I have, you probably got old event procedures hanging around for controls that were deleted long ago. That code might not hurt anything, but it can clutter up your modules and make searching and maintaining your database a pain. So today, we're going to learn how VBA can actually inspect its own modules and procedures, identify code whose control no longer exists, and eventually scan every form and report in the database. Along the way, we'll work with module objects, procedure line information, and the Controls collection, Access object collections, and several other parts of Access that you might not use every day. But by the end, we'll have a reusable utility that can scan one object or the entire database for orphaned VBA event procedures. All right, before taking this class, you should be comfortable with For Each loops, which we covered back in Developer 15, and especially Recordsets from Developer 16. I always say that Developer 16 is one of the most important ones. I'm familiar with class modules, and the object model stuff from Developer 50 will also be helpful, but that's not - I wouldn't call that a prerequisite. That'd be like a helpful. We've worked with the CurrentProject object collections before, but I'll review everything before we need it as we get to it today. But you know what I always say, don't skip levels, because I always pull stuff up from other lessons that we've covered before. It's currently 2026, so I'm using Access 365. The retail version would be closest to 2024. If you've got questions, you know what to do. Scroll down to the page that you're on, at the bottom, and post your questions there. If you've got general questions about Access, you can put them in the forum. All right, let's take a look at exactly what we're doing today. In lesson one, we're going to start by building the orphan code detector by having a form inspect its own VBA module. We'll learn how to work with the module object, skip the declaration section, loop through the module's lines, and use ProcOfLine. Yeah, you'll find out what that is in a minute. ProcOfLine to determine which procedure owns a particular line. In lesson two, we'll finish the first working version of the detector. We'll identify click event procedures, extract the control name, check the Controls collection to see whether that control still exists, and report orphaned procedures. We'll also use ProcStartLine and ProcCountLines, so we can jump from one procedure to the next instead of examining every single line in the module. In lesson three, we'll expand the detector beyond click events. We'll create an event table containing things like After Update, Before Update, Double-Click, focus events, keyboard events, and more, and then use a Recordset to make our scanner data-driven instead of hard-coding every event type. We'll also include form-level and report-level event procedures that aren't orphaned control events. In lesson four, we'll turn everything into a reusable database-wide utility. We'll move the scanner to a global module, make FindOrphans work with either forms or reports, build an object selector, and add routines to scan individual forms and reports, or you can click the Scan All button that loops through every form and report in the database and checks the whole application for orphaned VBA event code. By the end of this level, you'll have a useful orphaned VBA code detector that you can bring into your own databases to help you clean up years of leftover spaghetti code in your event procedures. More importantly, you'll have a better understanding of how Access exposes its VBA modules, procedures, controls, forms, and reports programmatically, which opens the door to building all kinds of database development and maintenance utilities. Now, as always, I recommend you watch each video once completely through, then watch it a second time and follow along with the examples. Now, sit back, relax, and let's get ready to start lesson one. IntroWelcome to <B>Access Developer 61</B>. We will learn how to build an orphan VBA code detector that identifies event procedures left behind after controls are deleted. We will cover inspecting form and report modules, locating procedures with ProcOfLine, ProcStartLine, and ProcCountLines, checking the Controls collection, using a data-driven event table with a Recordset, and creating a reusable utility that scans individual objects or the entire database. QuizQ1. What is the main purpose of an orphan VBA code detector? A. Identify event procedures for controls that no longer exist B. Automatically rebuild all deleted controls C. Convert VBA code into macros D. Prevent users from deleting records Q2. Why can orphaned event procedures be a problem in a long-running database? A. They always cause runtime errors B. They clutter modules and make maintenance more difficult C. They prevent forms from opening D. They permanently lock the VBA editor Q3. What will the first version of the detector have a form inspect? A. Its own VBA module B. Its record source table C. Its navigation pane entries D. Its printer settings Q4. Which VBA feature helps determine which procedure owns a particular line in a module? A. ProcOfLine B. RecordCount C. CurrentDb D. FindFirst Q5. Why is it useful to skip the declaration section when scanning a module? A. Declarations are not event procedures B. Declarations cannot contain text C. Declarations are stored in tables instead of modules D. Declarations always belong to deleted controls Q6. How will the detector determine whether a control referenced by an event procedure still exists? A. By checking the form or report Controls collection B. By checking the Windows Registry C. By reading the VBA Immediate Window D. By checking the database password Q7. What is the purpose of ProcStartLine and ProcCountLines in the detector? A. To jump from one procedure to the next efficiently B. To rename controls automatically C. To count records in the form's record source D. To create a new module for each control Q8. Why will the event scanner use an event table and a Recordset in a later lesson? A. To make the scanner data-driven instead of hard-coding every event type B. To store all VBA code inside a table C. To replace forms with recordsets D. To eliminate the need for event procedures Q9. Which type of event is expected to be included when the detector expands beyond Click events? A. After Update B. Table Relationship C. Query Design D. Database Compact Q10. Why should form-level and report-level event procedures be handled differently from orphaned control events? A. They can be valid even though they are not associated with a specific control B. They are never written in VBA C. They cannot contain more than one line of code D. They only run when a table is deleted Q11. What is the eventual scope of the reusable utility? A. Scan an individual form or report, or scan all forms and reports in the database B. Scan only the currently open table C. Scan only standard modules D. Scan external databases on a network Q12. Where will the final reusable scanner be moved? A. A global module B. A query object C. A table validation rule D. A report footer Q13. Which Access object collections are useful for scanning every form and report in a database? A. CurrentProject object collections B. Recordset Fields collections only C. Printer collections D. CommandBars collections Q14. What broader skill does this project help demonstrate? A. Using Access objects and VBA modules programmatically to build maintenance utilities B. Designing web pages with HTML C. Creating Excel charts from Access tables D. Encrypting every VBA procedure Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-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. SummaryToday's video from Access Learning Zone introduces Access Developer Level 61, where I will show you how to build an orphan VBA code detector for your Microsoft Access databases. If you have maintained a database for several years, you may have old event procedures left behind for controls that no longer exist. For example, you might have deleted a button or text box from a form, but its associated Click, AfterUpdate, or other event procedure is still sitting in the form's module. These unused procedures may not cause immediate problems, but they clutter your code, make searches more difficult, and add unnecessary maintenance work. In this course, I will show you how to create a utility that examines VBA modules, identifies event procedures associated with missing controls, and reports those procedures so you can clean them up. Eventually, we will expand the utility so it can scan every form and report in the database. Along the way, I will cover several useful parts of the Access and VBA object models. We will work with Module objects, procedure line information, Access object collections, form and report Controls collections, and other tools that are especially useful for building database maintenance utilities. Before taking this class, you should be comfortable working with For Each loops, which I covered in Developer 15, and Recordsets, which I covered in Developer 16. Developer 16 is particularly important because Recordsets are used throughout Access development. Familiarity with class modules and the object model material from Developer 50 will be helpful, although they are not strict prerequisites. We will also use the CurrentProject object collections to identify forms and reports in the database. I will review the relevant concepts as we need them, but I always recommend taking the lessons in order because I regularly build upon material covered in earlier classes. I am using Microsoft Access 365 for this course. If you are using a retail version of Access, Access 2024 is the closest equivalent. If you have questions about a specific lesson, post them in the comments section for that lesson. For general Access questions, you can use the forum. In lesson one, we will begin building the orphan code detector by having a form inspect its own VBA module. I will explain how to work with the Module object, skip over the declaration section of a module, loop through its lines, and use ProcOfLine to determine which procedure owns a particular line of code. In lesson two, we will complete the first working version of the detector. We will identify Click event procedures, extract the control name from the procedure name, and check the form's Controls collection to determine whether that control still exists. If the control is missing, the procedure will be identified as orphaned code. We will also use ProcStartLine and ProcCountLines to move directly from one procedure to the next instead of inspecting every line in the module individually. In lesson three, we will expand the detector beyond Click events. We will create an event table containing event names such as AfterUpdate, BeforeUpdate, DoubleClick, focus events, keyboard events, and others. By using a Recordset and an event table, the scanner becomes data-driven instead of requiring us to hard-code each possible event type. We will also account for form-level and report-level event procedures, which are legitimate procedures and should not be treated as orphaned control events. In lesson four, we will convert the project into a reusable database-wide utility. We will move the scanner into a global module and update the FindOrphans routine so it can work with either forms or reports. We will build an object selector that lets you choose an individual form or report to scan. We will also add routines that scan every form and report in the current database, allowing you to check the entire application for old event procedures connected to controls that no longer exist. By the end of this level, you will have a practical orphaned VBA code detector that you can import into your own databases. It will help you locate years of leftover event code and make your modules cleaner and easier to maintain. More importantly, you will gain a stronger understanding of how Access exposes VBA modules, procedures, controls, forms, and reports programmatically. Those skills can be used to create many other useful development, documentation, and maintenance tools. I recommend watching each lesson all the way through once before attempting the exercises. Then watch it again and follow along as you build the examples yourself. 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 ListBuilding an orphan VBA event code detector Inspecting an Access form VBA module Using Module objects and procedure line information Using ProcOfLine to identify event procedures Detecting missing controls for event code Using the Controls collection to find controls Using ProcStartLine and ProcCountLines Creating a data-driven event procedure table Scanning form-level and report-level event procedures Moving the scanner to a global VBA module Scanning individual forms and reports for orphans Scanning all forms and reports in a database Primary Topics
orphaned VBA event procedures, Access form and report modules, Module object inspection, procedure line analysis, Controls collection validation, database-wide form and report scanning Secondary Topics
For Each loops, Recordsets, class modules, Access object model, CurrentProject collections, reusable global utility modules |
||
|
| |||
| Keywords: Access Developer, orphan VBA code detector, Access VBA orphaned event procedures, VBA Module object, ProcOfLine, ProcStartLine, ProcCountLines, Access Controls collection, CurrentProject collections, form event procedures, report event procedures, VBA cod PermaLink How To Find Orphaned VBA Event Procedures From Deleted Control Code in Microsoft Access |
