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.