Introduction
By Richard Rost

13 days ago

Reusable Popups, Safe Deletes, and Drag-and-Drop Files

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

In Microsoft Access Developer 51, we will cover building a reusable modal pop-up form system that works seamlessly across different forms, handling subforms and continuous forms, and using class modules to encapsulate your pop-up logic. You will also learn how to create handy string helper functions, safely delete or archive records within recordset loops, and start building a drag-and-drop file management system for your Access database. This overview sets the stage for lessons that follow and highlights best practices for taking your Access development skills to the next level.

Navigation

Prev: D51 Lessons
Next: Lesson 01

Keywords

Access Developer, Access Developer Level 51, reusable pop up form system, modal form over active control, return values to calling field, subforms, continuous forms, control coordinates, class modules, string helper functions, recordset loop delete record

Subscribe to Introduction
Get notifications when this page is updated

Intro

In Microsoft Access Developer 51, we will cover building a reusable modal pop-up form system that works seamlessly across different forms, handling subforms and continuous forms, and using class modules to encapsulate your pop-up logic. You will also learn how to create handy string helper functions, safely delete or archive records within recordset loops, and start building a drag-and-drop file management system for your Access database. This overview sets the stage for lessons that follow and highlights best practices for taking your Access development skills to the next level.

Transcript

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

In today's class, we are going to build a really slick, reusable pop-up form system that can open a modal form directly over the active control, return values to the calling field, and work cleanly across different forms with minimal code changes.

We will take it further by handling tricky stuff like subforms, continuous forms, and calculating control coordinates no matter where the user clicks. You will also get a solid, real-world example of using class modules to encapsulate the pop-up logic, simplify your forms, and make the whole system easier to maintain and expand.

Along the way, we will build a couple of handy string helper functions. I will show you the safe, professional way to delete or archive records inside a recordset loop without running into skipped records or cursor issues.

Finally, we will start building a drag-and-drop file management system where users can drop files right onto a form, and we will wire up the folder structure, safety checks, unique file naming, and database linking to make it reliable and practical in real-world Access applications.

Today's class follows Access Developer Level 50. Do you need the last class? Yes, the first four lessons today are all based on class modules, so you should watch that. Unless, of course, you are not interested in those lessons, then you could skip them. I strongly recommend you finish all my previous classes - beginner, expert, advanced, and developer lessons - before continuing with today's class. My lessons are designed to be followed one after the other, so do not skip levels. See this page for all the reasons why.

This class is recorded with Microsoft Access as part of a Microsoft 365 subscription, which I strongly recommend. It is currently January 2026, so if you are using the retail version, this is roughly equivalent to Access 2024. The lessons today should work with any version of Access going back to 2007. But if you are still using 2007, it is time to upgrade.

If you have any questions regarding the material covered in today's class, just scroll down to the bottom of the page that you are on and post your questions there. Take a minute to read through any other questions that have been posted, as your question may have already been answered. Make sure you click on the subscribe button to get notified if other questions or comments are posted for this class.

If you have any other Access questions not related to today's class, post them in the general Access forum. This way, other users who may not be signed up for today's class can join in the conversation.

Let's take a closer look at what is covered in today's class.

In lesson one, you learn how to open a modal pop-up form directly over the active control in Microsoft Access and position it correctly every time. I will walk you through setting up a system where you can click on any field and have a custom pop-up form appear above it, laying the groundwork for returning values to that control. We will cover how to determine screen coordinates, use VBA to automate the process, and make the solution reusable for multiple controls with minimal code changes.

In lesson two, we continue working with our pop-up over control feature. I will show you how to store calling form and control information using temp vars. Set up your local form to return a value back to the original control and convert the functionality into reusable public functions for use across multiple forms and fields in your database. We will also cover best practices for naming controls and discuss how to make this process more efficient throughout your application.

In lesson three, I will show you how to handle subforms when working with pop-up controls. We will walk through improvements to the code, how to find a control's position on both main forms and subforms, and deal with issues like continuous forms and multiple subforms. You will learn some key techniques for calculating control coordinates and see how the logic changes depending on where the control is located. This lesson builds on previous videos focusing on challenges unique to subforms.

In lesson four, we will review a real-world example of using class modules to simplify working with forms, controls, and pop-up logic. We will see how to set up properties, encapsulation, and methods in a class module, determine if a control is in a subform, calculate pop-up form coordinates, and use a helper module to streamline your code. This lesson brings together concepts from Access Developer 50 and shows how everything integrates, providing a practical demonstration of building and using class modules for forms in Access.

In lesson five, we are going to create two helper functions, leftmost and rightmost. We will use these to simplify working with strings without needing to manually count characters as with the standard left and right functions. I will show you how to build these functions step by step, handle common issues like nulls and empty strings, and discuss practical use cases, such as checking email addresses, file extensions, and name prefixes in your application.

In lesson six, you will learn the safe and recommended way to delete records from a table inside a recordset loop in Microsoft Access, and why deleting directly within the loop can cause problems such as skipped records and cursor issues, especially when working with SQL Server or ODBC connections. I will show you how to process each record through a separate SQL statement after checking it with custom business logic, how to archive records efficiently, how to pass the recordset to a function for cleaner code, and we will discuss important best practices like transactions, logging, error handling, and multi-user considerations.

In lesson seven, we will start building a drag and drop file system in Microsoft Access. I will show you how to let users drop files directly onto a form. I will set up a listener using Windows APIs to detect dropped files and capture the file path for use in your VBA code. You will learn how to wire the listener to a specific form and confirm file drops are being received, laying the groundwork for creating a more complete file management system in the next couple of lessons.

In lesson eight, we are going to prepare the file drop system in Access for real-world use by organizing the folder structure, setting up subfolders for storing dropped files, and adding safety checks to prevent Access from locking up. You will learn how to ensure the file drop feature only works when it is safe, such as verifying a customer ID is present and making sure the VBA editor is not open. That will lock you up. These steps will help get your database ready to reliably handle file drops before we start moving on to saving and managing the files in the next lesson.

In lesson nine, we will take the Microsoft Access File Drop system we have been building and make it fully functional by copying dropped files into a structured folder system, generating unique file names, ensuring the necessary folders exist, and saving these files as linked records in the database. No, we do not store files in our database. We store links to where they are located. I will show you how to improve the user experience by refreshing the form and setting the focus to the newly added file, making the file drop feature practical and seamless to use inside Access.

That is what is covered in Access Developer Level 51. Now sit back, relax, get your snacks, and get ready for lesson one.

Quiz

Q1. What is the main purpose of the pop-up form system taught in Access Developer Level 51?
A. To create a reusable pop-up that opens over the active control and returns values to the calling field
B. To generate reports automatically after data entry
C. To export Access data to Excel with formatting
D. To create navigation menus in Access

Q2. What special scenarios does the pop-up form system handle as discussed in the class?
A. Only main forms
B. Only datasheets
C. Subforms and continuous forms
D. Only macro buttons

Q3. Why are class modules important in the pop-up form system built in this class?
A. To store user interface settings globally
B. To encapsulate pop-up form logic for reusability and maintainability
C. To encrypt the database data
D. To increase the maximum number of forms in the database

Q4. What helper functions are built in lesson five?
A. FormatDate and InArray
B. SumNumbers and AverageNumbers
C. Leftmost and Rightmost
D. GetDataType and SetColor

Q5. What is a safe and recommended way to delete records while looping through a recordset, as taught in the class?
A. Delete directly within the loop
B. Use transactions for each record only
C. Archive or delete using a separate SQL statement after verifying business logic
D. Lock the table before looping

Q6. What is the main reason for not deleting records directly inside a recordset loop?
A. It slows down the database
B. It can cause skipped records and cursor issues
C. It prevents logging from working
D. It is not supported by VBA

Q7. What does the drag-and-drop file management system allow users to do?
A. Import data from Excel only
B. Drop files directly onto a form for Access to capture and process
C. Link Access tables to SQL Server automatically
D. Log user login events

Q8. How does the file drop system maintain data integrity and safety?
A. Ignores all file checks
B. Only accepts images
C. Uses safety checks such as verifying customer IDs and ensuring the VBA editor is closed
D. Only drops files into the root directory

Q9. When a file is dropped onto the form, how is it stored according to the lesson?
A. Stored directly in the Access database as a BLOB
B. Copied into a structured folder system, with a database link to the file path
C. Sent to the printer
D. Uploaded to Microsoft Teams

Q10. Why does the instructor recommend watching previous Access classes before this one?
A. The class assumes knowledge from prior lessons and builds on their concepts
B. To increase video views
C. Because this class offers a complete review of all earlier material
D. No reason, it is optional

Q11. Which versions of Microsoft Access are the lessons in this class compatible with?
A. Only Microsoft Access 2026
B. Access 2024 and later only
C. Access 2007 and later, with emphasis on Access 365/2024
D. Only Access 2010

Q12. What is a suggested way to ask questions about this class?
A. Call the instructor directly
B. Email the instructor
C. Post questions on the class page and check previous posts for answers
D. Wait for a monthly Q and A session

Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-A; 11-C; 12-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 Microsoft Access Developer Level 51, where I will show you how to build a flexible and reusable pop-up form system. This system allows you to open a modal form over any active control, return values to the field that called it, and keep everything consistent across different forms with very little code adjustment required.

One of the challenges when building such a system is making sure it works seamlessly with subforms, continuous forms, and when controls are located in different places on a form. In this class, we will tackle those challenges by calculating control coordinates accurately, regardless of where the user triggers the pop-up. You'll also see how to use class modules to keep all the logic for the pop-up form together. This approach makes your forms easier to understand, maintain, and expand in the future.

Additionally, we'll create helpful string functions that make handling characters in your database much simpler. I'll demonstrate the safest way to delete or archive records within a recordset loop, so you do not run into skipped records or issues moving the cursor, which can happen especially with back-end databases like SQL Server.

To round out the class, we will begin developing a drag-and-drop file management system. This lets your users drop files directly onto a form. We'll handle organizing subfolders, running safety checks, assigning unique file names, and storing links to these files in your Access database, ensuring everything works smoothly in actual business scenarios.

This class builds directly on what was covered in Access Developer Level 50, especially the first four lessons, which rely heavily on class modules. If you haven't already gone through the previous lessons in the series—beginner, expert, advanced, and developer levels—I highly recommend doing so. Each lesson builds on previous concepts and skills, so following them in order is key for learning effectively.

The material in this course was recorded using Microsoft Access as part of Microsoft 365, which I recommend using. As of January 2026, if you have the Access 2024 retail version, you'll find everything here is comparable. The lessons should also work for versions as far back as Access 2007, but if you are still using that old version, now is the time to upgrade.

For questions about today's material, you can post them at the bottom of the lesson page. Take a moment to look at answers that others have provided, as your question may already be addressed there. Make sure you subscribe so you'll be notified if anyone else comments or asks questions about this lesson. If your question is more general and not directly related to the current lesson, please use the general Access forum so that the conversation can be shared more broadly.

Here's an overview of what you'll learn in each lesson of this class:

- In lesson one, I teach you how to open a modal pop-up form directly above the active control in Microsoft Access and make sure it is positioned correctly every time. You will see how to configure your database so a custom pop-up appears above any field you click on, laying the foundation for returning values back to that control. We discuss how to determine screen coordinates and automate everything in VBA, making it possible to reuse this solution without much extra code.

- Lesson two continues with the pop-up over control feature. I show how to keep track of which form and control called the pop-up, using temporary variables. You'll see how to send a value from the pop-up back to the original field and convert these actions into reusable public functions, available across your whole application. I'll also review naming conventions for controls and show how to make this workflow more efficient through your database.

- In lesson three, we confront the complexities that come with subforms and continuous forms. You'll learn how to adapt the logic to handle finding the right position for controls inside main forms, subforms, and even multiple subforms. I'll show you important techniques for calculating coordinates and explain how the logic needs to be adjusted based on where the control is located.

- In lesson four, we bring everything together by building a practical example that makes use of class modules. I demonstrate how to set up object properties, encapsulate logic, and create methods inside a class module, including how to check if a control is inside a subform, calculate the correct position for the pop-up, and use a helper module to keep your code organized.

- Lesson five is all about two new string helper functions called leftmost and rightmost. These make string manipulation in Access much easier, saving you from needing to count characters as you would with the built-in Left and Right functions. I show how to handle special cases like null or empty strings and suggest useful scenarios where these helpers can be applied, such as splitting email addresses, file extensions, and prefixes.

- In lesson six, I cover the right way to safely delete records inside a recordset loop. You'll learn why you should not delete records directly in the loop, as this can lead to skipped records and pointer issues. I walk you through processing records with a separate SQL statement after you have applied your own business logic, and we discuss archiving, passing the recordset to a function for clarity, and key topics like using transactions, logging, error handling, and handling multi-user databases.

- Lesson seven introduces the basics of building a drag-and-drop file system for Microsoft Access. I demonstrate how to enable your users to drop files straight onto a form, hook in Windows API listeners, and capture the dropped file's path so you can manipulate it in VBA. I explain how to connect the listener to a particular form and confirm that file drops are working properly, getting your system ready for the next step.

- Lesson eight further develops the file drop system. This lesson is about prepping your folder organization and running checks to prevent Access from getting stuck or crashing. I show how to confirm conditions such as whether a customer ID is entered and that the VBA editor is closed before allowing file drops, helping you create a reliable and safe system.

- Finally, in lesson nine, we finish the file drop feature by copying dropped files into a well-organized folder structure, generating unique names to prevent overwrites, creating folders as needed, and saving file paths as linked records in your database. Importantly, you will not be saving the actual files in Access, only references to where they are stored. I'll also show you how to enhance the experience by refreshing your form and shifting the focus to the newly added file, making this feature user-friendly and practical inside Access.

That covers everything included in Access Developer Level 51. For a full video tutorial showing every step in detail, be sure to visit my website at the link below. Live long and prosper, my friends.

Topic List

Opening modal pop-up form over active control
Positioning pop-up forms using screen coordinates
Making pop-up forms reusable for multiple controls
Storing calling form and control info with temp vars
Returning values from pop-up to original control
Creating public functions for pop-up logic reuse
Handling subforms with pop-up controls
Calculating control position on main forms and subforms
Managing pop-ups in continuous forms
Using class modules to encapsulate pop-up logic
Determining if a control is in a subform via code
Calculating pop-up form coordinates with a class module
Using helper modules for streamlined pop-up code
Building leftmost and rightmost string helper functions
Handling null and empty strings in string helpers
Deleting or archiving records safely in recordset loops
Avoiding cursor issues when deleting in loops
Processing records with business logic before deletion
Archiving records using an external SQL statement
Passing recordsets to functions for cleaner code
Implementing transactions and error handling for delete/archive
Setting up drag-and-drop file management in Access
Enabling users to drop files onto a form
Listening for Windows API file drop events in Access forms
Configuring folder structure for dropped files
Adding safety checks before accepting file drops
Verifying user context and database state before file drop
Copying dropped files to organized folders
Generating unique file names for dropped files
Creating linked file records in the database
Refreshing form and setting focus after file drop

Article

In this tutorial, you will learn how to build a versatile and reusable pop-up form system in Microsoft Access that can open a modal form directly above the active control, return values to the calling field, and work smoothly across multiple forms with minimal changes to your code. You will also discover key techniques for handling complex situations like subforms, continuous forms, and dynamically determining control positions no matter where the user clicks. We will use class modules to organize and encapsulate pop-up logic, making the system easier to manage, reuse, and expand as your database grows. Along the way, we will build a couple of custom string helper functions and examine a solid method for safely deleting or archiving records from a recordset within a loop, without the pitfalls of skipped records or cursor issues. Finally, you will start constructing a drag-and-drop file management system that allows users to drop files right onto a form in Access, with reliable folder handling, unique file naming, and database linking.

Let's begin with the pop-up form system. Imagine you want to let users select a value from a pop-up, like picking a date or choosing from a custom list, and have the pop-up appear exactly over the field they are working with—no matter where on the screen that control is located, even if it's inside a subform. This is a challenge in Access because each control's position can change based on the layout, scroll position, subform containers, and screen scaling. You will need to calculate the exact screen coordinates of the active control using VBA. Typically, you start by capturing the control's coordinates relative to its parent form, convert those to screen coordinates, and then position your pop-up form accordingly.

Here is a basic example in VBA of how to get a control's position on the screen:

Dim pt As POINTAPI
Dim hwnd As Long
hwnd = Me.ActiveControl.hwnd
Call GetWindowRect(hwnd, pt)
' pt now contains the left and top screen pixel coordinates

From here, you can use the coordinates to set the pop-up form's Top and Left properties before opening it. Make the pop-up modal so users cannot continue without making a choice or cancelling.

Once the pop-up is open, you need a way to return the user's selection back to the original control. You can use TempVars to store references to the calling form and control when opening the pop-up. For example, before opening the form, set:

TempVars!CallingForm = Me.Name
TempVars!CallingControl = Screen.ActiveControl.Name

Then, after the user makes a selection in the pop-up, use these TempVars to push the value back:

Forms(TempVars!CallingForm).Controls(TempVars!CallingControl).Value = YourPopUpValue

Clear the TempVars afterwards for cleanliness. By abstracting this logic into a module with public functions, you make it simple to add this feature to any form or field in your application. Use consistent control naming and design a straightforward interface to minimize setup on each new form.

Handling subforms adds another layer of complexity. Controls on subforms cannot be accessed directly from the main form, and you have to resolve the path to the subform and control accurately. To find a control's coordinates, check if it is within a subform or the main form, walk up the hierarchy, and sum the offsets at each level to compute the absolute screen position. This method also works with continuous forms and nested subforms, as long as you keep track of parent-child relationships.

To keep your code organized, use class modules. Create a class that manages everything related to the pop-up: which control opened it, handling coordinate calculation, managing pop-up display and value return, and error handling. Add properties for the calling form and control, and methods for everything else. Using a helper module can streamline usage so you can invoke the pop-up with a single line of code from any form. Not only does this reduce maintenance as your system grows, it makes it easier to add new features.

Moving on, you will sometimes need custom string manipulation functions that go beyond the basic Left and Right. Suppose you need to extract all characters to the left of a certain string, or everything to the right. Create helper functions like Leftmost and Rightmost. Here is a sample Leftmost function:

Public Function Leftmost(strText As String, strDelimiter As String) As String
Dim i As Long
If IsNull(strText) Or IsNull(strDelimiter) Then
Leftmost = ""
Else
i = InStr(strText, strDelimiter)
If i = 0 Then
Leftmost = strText
Else
Leftmost = Left(strText, i - 1)
End If
End If
End Function

You can write a similar Rightmost that returns the text after a delimiter. These functions are handy for splitting email addresses, file paths, or parsing structured input.

When it comes to deleting or archiving records safely in Access using VBA, avoid directly deleting records from within a loop on a recordset you are traversing. This can cause skipped records or cursor errors, especially when working with external data sources like SQL Server or ODBC connections. The safer approach is to scan through the records, store the primary keys of records that should be deleted, then run a second pass to delete them with separate SQL statements. Alternatively, use a flag field to mark records for deletion and process them in one go at the end. Always use transactions, add error handling, and consider concurrency issues if multiple users are working with the data at the same time.

Next, you will learn how to add a drag-and-drop file system to your Access forms. With this feature, users can drag files from Windows Explorer straight onto a form. This requires setting up a Windows API listener in VBA to detect dropped files and capture their paths. Once you have the file path, you can process it as needed—copy the file into an organized subfolder structure, generate unique file names to avoid conflicts, and create a new linked record in a files table that stores the path, display name, and any related information. Never store the files themselves inside the database; always store a reference link. To ensure reliability, make sure the folder structure exists with MkDir, and check for any issues (like the VBA editor being open) before accepting dropped files, as this may cause Access to hang.

Once your file is saved and linked, enhance user experience by refreshing the form, displaying the newly added file, and setting focus to it so users get instant feedback. This kind of drag-and-drop integration is increasingly expected in modern business applications and makes Access much more user-friendly.

By practicing these techniques, you will have a robust pop-up form system, helpful string functions, safer record updates, and practical file management features. These are advanced but practical skills that make your Access applications more powerful, maintainable, and professional.