Popup Over Control
By Richard Rost
6 months ago
Popup Form Over Active Control with Return Value
In this Microsoft Access tutorial I will show you how to open a modal pop-up form directly over the active control on your form and position it accurately, making it easy to update fields like Last Name or Country. We will use VBA to capture which control was clicked, calculate its position, and open a custom modal form in that exact spot. You will also see how to pass a value from the clicked control to the pop-up form for editing. This is part 1.
Sandra from Norfork, Arkansas (a Platinum Member) asks: Could you show how to open a modal popup form above a specific control? I'm trying to use your popup calendar date picker and it would be nice if it opened on top of the date field.
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, open modal form over control, pop-up form position active control, return value to calling control, date picker modal form, form MoveSize to x-y coordinates, ActiveControl VBA example, WindowLeft and WindowTop usage, reusable pop-up form function, TempVars demo, On Click event pop-up, MyModalF sample form, developer-level modal forms, form Left and Top properties, transfer form value VBA, pop-up over fields, navigation buttons modal off, max min buttons modal off, set ActiveForm ActiveControl, pop-up for subform field
Transcript
Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. Today, I'm going to show you how to open a pop-up form over the active control and then return a value to that control.
So let's say you've got a field here like Last Name. You want to click on that field, it opens up a little pop-up form, you can type in a value, hit Set Value and Close, then it returns it to that field. This little guy is going to appear right over the top of this one. If you've got another one over here on the other side of the form, it'll open up over there. So we're going to learn how to position this form and then we're going to learn how to return a value - pretty cool stuff.
Today's question comes from Sandra in Norfolk, Arkansas. She's one of my Platinum members, she's also a moderator on the website, and she also works on my handbooks for me as she does a lot.
Sandra says: Can you show how to open a modal pop-up form above a specific control? I'm trying to use your pop-up calendar date picker and it would be nice if it opened on top of the date field.
And here's the example she posted in the forums. If you've got a date field over here, you want to click on it and it pops up the date picker, right? This is a little date picker that I built, and you could pick the date and then it returns it back there. But if you want to click on this guy over here, you kind of want this date picker form to appear like over here, right? You want to position this pop-up form.
That's what we're going to be doing today.
Now before we get started, if you don't know what modal and pop-up forms are, go watch this video first. I personally don't like pop-up forms.
In a nutshell, the difference is a modal form is something that opens that you have to either close or do something with it before you can act on the stuff behind it. So if your little calendar form here pops up and it's modal, you won't be able to click behind it. That's what modal means. You're stuck on this guy until you close it.
Now, pop-up is different. Pop-up is a form that just stays on top of other forms, so you could still work with the stuff behind it. That's good for a little notepad, which is the example that I build in this video. I personally don't like pop-up forms. I rarely use them because in a multi-monitor setup like I have, you have a hard time controlling where that guy goes.
So even though I'm using the word pop-up, I'm not using a pop-up form for this example. It's only modal. I just use the word pop-up in the title of the video, because I know that's what everyone thinks of that form as. Not many people know what the word modal means, so I didn't put it in the title.
But we're going to be using a modal form, which is kind of pop-up. It's going to be pop-up because you're going to open it and it's going to pop up on top of the other form and you can't get behind it until you close it, so it basically is pop-up. But pop-up forms also work outside the boundaries of the Microsoft Access application window. You can move them anywhere you want, which is a pro and a con. The con is that you really can have a hard time controlling where they go because their x-y coordinates are your entire screen, whereas a modal form - if it's just modal - will stay inside the Access application window, and I actually prefer that.
But again, there are applications for both, so whichever one you use, I'm using a modal form - a modal non-pop-up form.
This is a developer level video. What does that mean? That means if you don't know VBA and you want to learn it, go watch this first. I'll teach you everything you need to know to get started in about 20 minutes.
Make sure you watch my DoCmd.OpenForm video so you understand how this command works. Watch my MoveSize video so you understand how to move a form where you want it to the x-y coordinates that you pick. We'll talk about WindowLeft, WindowTop, and stuff like that in this video as well - very important. Or not WindowLeft, it's WindowTop. There's WindowTop, Left, Height, and Width. This is actually wrong right here.
We're going to need to use ActiveControl to determine where to return that value to. In fact, this video does kind of show you how to do the same thing - pick a date, put it back here. But this video doesn't show you how to position this window, and that's what we're going to be doing today. And of course, we have to know what form we're on, so go watch my ActiveForm video.
We'll be using Adam's favorite TempVars command, so go watch this as well. These are all free videos - they're on my website, they're on my YouTube channel. Go watch those and then come on back.
Here I am in my TechHelp free template. This is a free database you can grab a copy of on my website if you want to. Let's say here on my customer form, I have a couple fields that I want to be able to click on. It'll open up that pop-up window, type in the value, do whatever you want, pick a date - whatever, it doesn't matter - and then it returns it here. But you want to position that over this guy. If I click down here in Credit Limit, I want to position it over there.
First, we need a little form to pop up. So let's take my Single form over here, copy and paste that, and we'll call this "MyModalF." It'll be a modal form.
Open "MyModalF" in design view. Let's say I just want to keep one text box in here, and we'll call that guy "ReturnValue." I'll put a default value of "Pick Card" in there. We're going to return "Pick Card" or whatever the user types in. Then we'll add a big button. I'll put a command button right here, cancel the wizard, and we'll say "Close Form and Return Value." We'll get to coding this guy later.
That's just our pop-up form for now - our modal form. Go to the form's properties, go to "Other." I'm going to turn "Modal" on and leave "Pop Up" set to "No." That way you can position it inside the Access window where we want. You could turn off things like the scroll bars, record selectors, and navigation buttons - all that stuff. Save it, close it. If you open it, that's what you're going to get - this little guy and of course, it's modal.
When you use a modal window and you have the max and min buttons turned on, sometimes it makes it hard to grab the title bar. So let's turn the max and min buttons off too, because then you can't get ahold of the title bar. Right-click, design view. Where are those max and min buttons? Right there. We don't need those either.
So sometimes when you open up a modal form, it makes it hard to grab the title bar if you have max and min buttons. This guy's just going to pop up, you're going to type in your value, hit this button, and it's going to close the form and put the value back here. If you open it over here, it's going to open up over on this side.
Now we have to write the code to actually do that.
For the purposes of class, I'm going to delete these guys and I'm going to slide Country way over here. Those will be the two fields we use, just so you can see that it's working on opposite sides of the form.
I'm going to visually change these two guys to a deep blue. Those are the fields we're going to use. That tells the user "hey, you can click on these and something's going to happen." I usually make them blue; you could do whatever you want.
Now, it'd be easy to hard code it to each one of these controls. You could say, "first name, if it's here then open up that form and move it to x-y. If it's over here, open it over this..." But we want to make a reusable function. Something we can use regardless of what control is clicked on. All we have to do is call it from Country, call it from First Name, and it'll just work. You don't have to keep specifying different parameters.
So let's go into one of these guys, this First Name here. Go to Events and go to "On Click" and hit the "..." button. That'll bring up the code builder. Let me resize - we don't need this right now.
In the First Name click, we're going to say: PopUpOverControl. That's it.
Now we're going to write that PopUpOverControl. Let me go to the bottom here. For now, we'll keep it "Private Sub PopUpOverControl."
We're going to need some variables. We're going to need to know what the ActiveForm and the ActiveControl are. I like to get those and throw them in local variables. It just makes things easier because then you don't always have to reference Screen.ActiveControl later, you can just say ActiveControl.
So we're going to "Dim ActiveCtl As Control" and "ActiveFrm As Form." Those are really easy to set. They're object variables, so you've got to set them.
Set ActiveCtl = Screen.ActiveControl
Set ActiveFrm = Screen.ActiveForm
And remember my rule: when you set them, you have to forget them. So later on at the bottom, we have to make sure we set ActiveCtl = Nothing and ActiveFrm = Nothing. That clears that memory.
Now, we're going to need to figure out x and y coordinates for where that control is. The form has a property called WindowLeft and WindowTop - that's the left and top coordinates, basically the x-y coordinates of the upper left corner. To that, we can add the coordinates of the active control's Left and Top properties. Then we know where we have to move the pop-up window to.
So we'll need two more variables:
Dim x As Long and y As Long, and we can set those right here:
x = ActiveFrm.WindowLeft + ActiveCtl.Left
It's just "Left," and yes, it's not in the IntelliSense. I don't know, Access team, but it's real.
Then the same thing:
y = ActiveFrm.WindowTop + ActiveCtl.Top
Again, not in there. You don't want Top Padding. I don't know what's going on with the IntelliSense people.
Let's see what we've got so far. Here we're just going to MessageBox x & " " & y. Let's see what those coordinates look like.
We need to put PopUpOverControl in the other field. So come over here, click on this guy, click on that guy, and do the same thing there: PopUpOverControl. We're just copy and pasting. Now we can put PopUpOverControl in any click event we want, and it's just going to call this - it doesn't matter what field it is.
Save it. Debug > Compile, once in a while. Close it. Open it. Click it. That's 2670 by 1035. So it's 2670 this way by 1035 that way. Hit OK. What's this guy? 70350 by 31, and that seems reasonable.
So now all we have to do is open up the form and MoveSize it to that spot. Let's come back to our code. We don't need the MessageBox anymore.
We're going to say:
DoCmd.OpenForm "MyModalF"
Then
DoCmd.MoveSize x, y
You could change the height and width here. You don't have to.
That, for now, is going to be it. Save that.
And then, watch: ready? Boom, it opens right there. If I close it and open it over here, boom, it opens right there. See? It's opening up and moving it to where that control is. It's not exact - well, actually, this little corner right here.I think that is what it's lining up with, so close enough for government work.
Now we just have to return that value when the user opens it up. Change puts something in here. It's the button and then returns it back here.
Actually, what we could do is, instead of that default value of Picard always being in the box, why don't we send to that form what's currently in the box right after we open it? Let's say:
forms modal F
return
Value equals active control dot value
So now, when we open up that box, it puts whatever's in there in there. Then you can make a change to it if you want.
The next step is just to return whatever they enter. If they change this to Canada, for example, we want to hit close form and return, and it'll bring it back and put it in there. We'll cover that in my next TechHelp video, so tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because I'm going to keep recording and post it.
One of the benefits of being a member is you can watch the videos as soon as I finish them. You don't have to wait for them to go public.
Tomorrow, I'm going to show you how to return that value to the calling control. We will also discuss what happens if you've got a subform.
That's going to do it for today, folks. That's your TechHelp video. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Opening a modal form over a specific control
Creating a modal form in Microsoft Access
Configuring modal form properties
Disabling maximize and minimize buttons on forms
Identifying the active form and active control
Calculating x and y coordinates for form positioning
Using VBA to open a form at a specific position
Assigning code to control click events for pop-up behavior
Passing the control's current value to the modal form
Creating a reusable function for modal pop-up positioning
COMMERCIAL:
In today's video, we're learning about how to open a modal form directly above the control that you click on in your Microsoft Access database, like popping up a custom date picker over a specific field. You'll see how to position your form exactly where you want it using the control's coordinates, and how to make a reusable function that works for any field on your form. I'll show you how to open the form, move it to the active control, and send data from that control over to the form so you can edit it. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main goal demonstrated in the video tutorial?
A. How to create a report that summarizes customer orders
B. How to open a modal form above an active control and return a value to that control
C. How to export data from Access to Excel
D. How to secure a database with a password
Q2. What is the difference between a modal form and a pop-up form as discussed in the video?
A. Modal forms can be accessed behind while open, pop-up forms cannot
B. Modal forms require user interaction before returning to the main form, pop-up forms just stay on top of other forms
C. Modal forms can move outside the application window, pop-up forms cannot
D. There is no difference between modal and pop-up forms
Q3. Why did Richard recommend not setting the "Pop Up" property to Yes for this example?
A. Because pop-up forms cannot be closed easily
B. Because pop-up forms are not supported in newer versions of Access
C. Because pop-up forms may appear outside the Access window and are harder to position accurately
D. Because pop-up forms require advanced VBA skills
Q4. What technique is suggested for determining where to position the modal form?
A. Use the form's DefaultView property
B. Add a fixed offset from the top left corner of the main form
C. Calculate x and y coordinates by adding ActiveForm's WindowLeft/WindowTop to ActiveControl's Left/Top properties
D. Place the modal form at the center of the screen every time
Q5. Why is it preferable to use a reusable function like PopUpOverControl rather than hard-coding coordinates for each control?
A. To ensure compatibility with all database versions
B. To reduce redundant code and make applying the feature to more controls easier
C. To make the code more complex for learning purposes
D. To allow pop-up forms to work in web browsers
Q6. What purpose do the VBA object variables ActiveCtl and ActiveFrm serve in this process?
A. They store the last values entered by the user
B. They hold references to the currently active control and form for use in calculations
C. They contain calculated window coordinates for the modal form
D. They track how many forms are open in the application
Q7. What is the significance of setting ActiveCtl = Nothing and ActiveFrm = Nothing at the end of the process?
A. It resets the form to its original position
B. It closes the modal form automatically
C. It releases object variables from memory to avoid potential issues
D. It triggers the value to return to the main form
Q8. What problem is avoided by turning off the Maximize and Minimize buttons on the modal form?
A. Users accidentally closing the form
B. Users being unable to move the modal form by grabbing the title bar
C. The modal form being resized dynamically
D. The appearance of extra scroll bars
Q9. When the modal form is opened over a control, how is the initial value to be edited set in the ReturnValue text box?
A. By manually typing it in before opening the form
B. By passing the current value from the active control to the modal form after opening it
C. By always displaying a default value such as "Picard"
D. By using a TempVar to retrieve it on form load
Q10. Why is it important to Compile the VBA code after writing or modifying it?
A. To see how many lines of code were added
B. To ensure that errors are caught before running the code in Access
C. To make the modal form visible in the navigation pane
D. To export the code to a text file
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B
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 TechHelp tutorial from Access Learning Zone covers how to open a modal form on top of the currently active control in Microsoft Access, and then return a value from that form into the selected field. This technique is especially helpful when you want a more interactive and dynamic user experience, for example, with a custom pop-up date picker or a similar auxiliary form that relates contextually to the field the user selects.
Imagine you have a form with multiple fields, such as Last Name or Credit Limit. You want a small window to open directly above whichever field the user clicks on. After entering a value or making a selection in the pop-up, that value should then appear in the original field. The tutorial explains exactly how to position the modal form correctly and transfer the data back.
This question actually came from one of my members who wanted to know how to make a modal form, such as a date picker, appear directly above the corresponding date field on a form. The idea is to ensure that wherever the date field is located, whether it's on the left or right side, the date picker form should always open above it for a better user experience.
For those unfamiliar with the terminology, let me briefly explain the difference between modal and pop-up forms. A modal form is one that you must interact with or close before you can work with anything behind it. For example, if a calendar window opens as modal, you cannot click anywhere else in the database until you handle the modal window. A pop-up form, on the other hand, simply remains visible on top of other forms but allows you to work in the background. While both can be useful, for this application I prefer modal forms because they behave more consistently within the Access window, especially if you are using multiple monitors. In multi-monitor setups, pop-up forms can sometimes appear in unpredictable locations because their x and y coordinates reference your entire display space. Modal forms, by contrast, remain within Access's main window, making it easier to control their placement.
Although the word "pop-up" often gets used generically, the form demonstrated here is a modal form that behaves like a pop-up since it appears above your other forms and must be closed before continuing. We are intentionally not using a form set to the pop-up property for this technique.
This is considered a developer-level tutorial, so familiarity with VBA is helpful. If you need to brush up, I recommend looking at my introductory VBA lessons, which will give you the fundamentals in about 20 minutes. You should also be familiar with the OpenForm method, the MoveSize method for dynamic window placement, and properties like Top, Left, Height, and Width for window objects. Having a basic grasp on the ActiveControl and ActiveForm properties in Access will be crucial for tracking which field is triggering your pop-up window. There's also a free video on TempVars, which we will use for passing values between forms. All of these topics are covered in detail on my site and YouTube channel.
In practice, let's assume you have a Customer form within the TechHelp free template from my site. You want to enable fields, for example, First Name and Country, to be clicked and have a small modal form open above each one. The workflow is that the user clicks a field, the modal window opens just above it, they enter or modify a value, and then it returns to that original field.
First, we need a form that will serve as the modal pop-up. You can create this easily by copying an existing form and renaming it, say, "MyModalF." Place a single text box on this form, called ReturnValue, with a default value if you wish (like "Pick Card"). Add a button labeled "Close Form and Return Value." All the form needs at this stage is its modal property enabled and the pop-up property left off. Other cosmetic features like scroll bars, record selectors, and navigation buttons can also be disabled for a cleaner look. Remember to also disable the maximize and minimize buttons, because when working with modal forms, those buttons can make it difficult to move or close the form.
The next step is coding the form so that it always opens above the field the user clicked. While it would be possible to hard-code a position for each field, that would require lots of maintenance. Instead, we want a reusable function that automatically opens the modal window above whatever control is active.
For that to work, you will need to write a reusable VBA procedure, named for example PopUpOverControl, and assign it to the On Click event for each field you want this behavior. Inside the function, you first store references to the active form and active control in local object variables. This keeps things organized and avoids the need to repeatedly call Screen.ActiveControl and Screen.ActiveForm throughout the code. When using object variables, it's important to set them to Nothing at the end of your procedure to release memory.
To determine where the modal form should appear, calculate the x and y coordinates by adding the current window's Left (WindowLeft) and Top (WindowTop) properties to the Left and Top properties of the control. These represent the top-left corner of the control within the application window. Using these calculated coordinates, you can then use commands to open the modeless form and move it to the new position immediately upon opening. This way, no matter what field the user clicks, the modal window will snap to the correct spot.
This method is efficient because you only need to assign the same PopUpOverControl function to each clickable field. There is no need to write separate code for every field.
To further enhance the form's behavior, when opening the modal, you can pre-populate the text box with the value from the original field, making editing easier for your users. This is done by assigning the active control's value to the ReturnValue field of your modal form as soon as it opens.
The next stage, which will be covered in a follow-up video, is to transfer whatever new value the user enters on the modal form back to the original control, including handling if the form is used inside a subform. That portion requires a bit more code but builds on the concepts established here.
If you want to see a step-by-step visual demonstration of these steps, including how to write and assign the VBA code, you can find a complete video tutorial on my website at the link below. Thanks for reading, and live long and prosper, my friends.
Topic List
Opening a modal form over a specific control
Creating a modal form in Microsoft Access
Configuring modal form properties
Disabling maximize and minimize buttons on forms
Identifying the active form and active control
Calculating x and y coordinates for form positioning
Using VBA to open a form at a specific position
Assigning code to control click events for pop-up behavior
Passing the control's current value to the modal form
Creating a reusable function for modal pop-up positioning
|
